Going Back To Previous Cell Within Loop Macro
Jul 13, 2009
I need to copy a changing source cell, paste its value into another specific cell (always the same), and then return to the source cell for continued action (ie range selection, copy & value paste, which I can code).
This action is then followed in the next cell to the R of the first cell copied, etc to end of data. I can code the move to the R.
How do I return to the source cells as part of a loop?
Specific notes included in attached - I hope I've explained it clearly.
View 12 Replies
ADVERTISEMENT
Sep 25, 2008
With "edit directly in cell" turned off, if you double click through in a cell containing a formula to the cell being refferenced, is there a quick way of getting back to the original cell you clicked on?
View 9 Replies
View Related
Jun 28, 2012
let's say I run a macro from a button on sheet 3, macro process on sheet 10. Is there a way that the macro would automatically go back to sheet 3? Like a "Back" button on IE. I can't code Sheets("sheet 3").Select because I want it to do the same thing on sheet 4,5,6...
View 3 Replies
View Related
Dec 2, 2008
I am trying to take a column of data located on one sheet and move into two different locations on another sheet. My range is from A1:A10. The below code works, but as it loops, it replaces G1 and H1 with the other values from the original range. Once the first pos and neg values are placed in G1 and H1 respectively, I want the next cells to be evaluated and placed in G2 and H2 etc. What am I missing here? How can I get it to stop pasting over itself?
Sub test()
Dim bcell As Range
For Each bcell In Range("a1:a10")
If bcell > 0 Then
bcell.Copy Destination:=Worksheets("Sheet2").Range("G1")
Else: If bcell < 0 Then bcell.Copy Destination:=Worksheets("sheet2").Range("H1")
End If
Next bcell
End Sub
View 9 Replies
View Related
Aug 22, 2014
I'm listing through sheets and have Sheet2 for drop-down lists.
Sheet2 is not visible, but when I click on Cmdbutton It appears so that I can change values in drop-downs. When I hide Sheet2 again, I want to be back on sheet before Sheet2.
My code for open and close sheet2 :
[Code].....
How can I do that ?
View 2 Replies
View Related
Nov 29, 2007
I have a macro that unprotects a worksheet copies data from that worksheet and pastes it into a newly created and saved workbook. However I have two problems
1) I'm attempting to copy the data from the first workbook by selecting all cells and then when switching to the new workbook, select all cells and the paste special (values) then paste special (formats). This works when recording the macro but when I attempt to run it it bombs out.
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
2) How do I get the macro to switch back to the workbook it got its data from. The name of the source workbook varies and there may be from 2 to 15 workbooks open. Is there a previous active workbook command? or do I need to create some kkind of temporary value to hold the file name of the source workbook.
View 9 Replies
View Related
Nov 14, 2008
I am working within a workbook with many worksheets and I want the ability to go back to the previous sheet I was on (example - I am on sheet 23, go to sheet 16, I want to be able to have a button or link that takes me back to sheet 23)
View 9 Replies
View Related
May 8, 2014
how to go back to previous page in internet explorer using vba.
View 2 Replies
View Related
Sep 21, 2009
Col ACol BCol CCol DCol ECol F
row 1811TAX CONSIDERATIONS1TAX CONSIDERATIONS
row 1911TAX CONSIDERATIONS#N/A
row 2011TAX CONSIDERATIONS#N/A
row 2121FLYING FALCON1TAX CONSIDERATIONS
row 2221FLYING FALCON#N/A
row 2321FLYING FALCON#N/A
row 2431IN COMMAND1TAX CONSIDERATIONS
row 2531IN COMMAND#N/A
row 2631IN COMMAND#N/A
I have 5 coulmns of data and in col F I have the following formula in row 18
=VLOOKUP(E18,$C$18:$D$800,2,FALSE)
the results come back " TAX CONSIDERATIONS"
however the same results comes back in row 21 and row 24......how can I modify this lookup to change when column B changes
View 9 Replies
View Related
Aug 31, 2007
macro which can paste the value from A1, to another B1 and when A1 is edited again, pasted it in B2. This will probably go on and on until B30. I have tried the if else then in Macro, and even the if elseif, but both don't go futher than B2. E.G A1=1 B1=1 A1=4 B2=4 A1=3 then nothing happens to B3. If there is a way to make this work, it will be great. The way i am tryin to get it is to have =if(B1="","",1) for each cell from C1:30 and with C31 the formula "=count(C1:C30)+1". Then i formated C31 to a custom and make it so that before numbers, there is an B. This means that if B1,2,3 and 4 and numbers in them, then C31 will read B5. That will be the cell i want my next record to go. I would prefer it if there is just a code that i can enter that will go to the next cell when it knows the current cell has a digit in it but if i have to, the count way will do,
View 9 Replies
View Related
Oct 17, 2006
I think the loop is deleting my previous entries and only putting the last results in.
For assortedrowindex = 3 To 400
targetdate = Date
Do While Month(targetdate) = Month(Date)
Redim Preserve arrTransactions(assortedrowindex - 2)
arrTransactions(assortedrowindex - 2).CUSIP = Cells(assortedrowindex, 12)
arrTransactions(assortedrowindex - 2).OrderDate = Cells(assortedrowindex, 9)
arrTransactions(assortedrowindex - 2).BuyCurncy = Cells(assortedrowindex, 2)
arrTransactions(assortedrowindex - 2).SelCuurncy = Cells(assortedrowindex, 4)
arrTransactions(assortedrowindex - 2).Fund = Cells(assortedrowindex, 7)
arrTransactions(assortedrowindex - 2).SettleDate = Cells(assortedrowindex, 10)
arrTransactions(assortedrowindex - 2).BuyUnits = Cells(assortedrowindex, 15)
arrTransactions(assortedrowindex - 2).FxRate = Cells(assortedrowindex, 16)
If targetdate < arrTransactions(assortedrowindex - 2).SettleDate Then
' Sheets("Sheet2").Activate...............................
View 4 Replies
View Related
Jan 6, 2014
I am new to macro. I am trying to convert the following from column 1 to column 2.
1
1
0
1
3
3
[Code] .....
In the left column, the numbers who are greater than 0 can have the same number of rows of zero.
Is there anyway I can do it to convert Column 1 to column 2.
View 5 Replies
View Related
Jan 24, 2007
see the attached file for an example. I am creating a formula/macro for performing the following: If "Y" exists in column J, then replace in the next cell below (columns G and H) with the values from the cell above in columns G and H and then clear the contents from the above cells.
View 2 Replies
View Related
May 9, 2013
I have amended the code below and have got it working. The problem I have now is that every time it loops it overwrites the data it wrote the previous loop
Offending line being ActiveSheet. Range ("A1: D30") = ValuesArray
I have known that somehow it should remember the last row and copy below this one but I cannot get it to work
Code:
Sub Basic_Example_1()
Dim MyPath As String, FilesInPath As String
Dim MyFiles() As String
Dim SourceRcount As Long, Fnum As Long
Dim mybook As Workbook, BaseWks As Worksheet
Dim sourceRange As Range, destrange As Range
[Code] ..........
View 1 Replies
View Related
Sep 27, 2012
I am about to write a small macro to do a iteration calculation. I would like though for the user to select me a cell so that the macro uses the value in that cell to start calculations. Now, what I think would be the problem is how do I code a "time" or notice an action such as clicking in a cell to continue with the macro.
Would it be by inserting a pause in the code of a certain time so that gives the user time to click and wait for the code to start? If so, how would much time is enough? Is there another thing that could restart the code such as "noticing a click in a cell" command in VBA?
View 1 Replies
View Related
Aug 3, 2009
the post 5 for the actual issue. This being my first post could not update it correctly. I have put my views int he 5th post which will be more clear.
View 9 Replies
View Related
Jul 2, 2009
I am wondering why the loop at the end of this macro loops endlessly.
View 3 Replies
View Related
Mar 27, 2007
I have 2 columns and 10 rows (A1:B10). In column A are certain text values. How do I write a macro to go through the table and preform an operation in the corresponding cell in column B depending on the value in cell A? for example, if A3 = "Complete", overwrite the formula in B3 with the value (paste value)?
View 2 Replies
View Related
Oct 31, 2011
What do I need to put in a macro that would
MacroZ:
Start in D1 then if cell has text "Mx2" use macro MK2.
If cell has text "Mx3" use Macro MK3
If cell has text "Mx4" use macro Mk4
Then go down 17 cells and repeat the macroZ until the active cell is empty then use macro MKX
View 1 Replies
View Related
Aug 11, 2012
I have a layout something like the following:
A1
A2
A3
[Code]....
Where each (i.e., A1) represents a location. I have tried to use a coordinate system but this will not work for the back-to-back locations. (Assuming each location is 2 feet wide, For example A1 to C1 is 4 feet apart, not 2 feet (as Euclidean or rectilinear would calculate it as).
Would there be a way to incorporate an if statement for those locations that are back-to-back? As a rectilinear distance calculation would work as long as the locations are not part of the same "block".
Ultimately I am looking to have a matrix which contains all the distances between each location:
A1
A2
A3
B1
[Code].....
View 4 Replies
View Related
Jan 8, 2013
I have a Random Number macro that will generate a random number (from 1 to 50) and place it into cell C4 of the worksheet. From there, various calculations are performed in the spreadsheet, ending with a value in cell AB5 and cell AB6. Depending on this random number, occasionally the value of cell "AB5" and cell "AB6" do not match. I want the macro to continue looping (generating another random number until a match is made between AB5 and AB6). I've tried various things but nothing is working to make it loop.
Below is the code for the random number macro.
VB:
Sub RandomNo()
Randomize
MyNumber = Int((50 - 1 + 1) * Rnd + 1)
Range("c4").Value = MyNumber
End Sub
View 4 Replies
View Related
Dec 10, 2012
see attached file. Need to find latest non blank value - in attached file it is highlighted in yellow. From there, want to summarise 26 weeks back so, in the attached file:
Row 2 would be finding 750 and summarised back 26 weeks from 30 sep 2012
Row 3 would be finding 2250 and summarised back 26 weeks from 2 dec 2012
Row 4 would be finding 5000 and summarised back 26 weeks from 4 nov 2012
View 7 Replies
View Related
Nov 5, 2013
Let's say sheet - "delivery" - has a row1 that includes all possible delivery days. So A1 is 1.9.2013, B1 is 15.9.2013, C1 is 1.10.2013 and so on.
I want to make a macro, that will gradually go through whole row 3 in sheet "delivery" and look for "A1" date in sheet "Orders". Sheet "orders" have for example in column B the date of delivery, and in column C product of the delivery.
I want the macro to find all deliveries with "A1" date, and paste all products that will be delivered on this day under cell A1 (sheet delivery). then move on to the cell B1 (sheet delivery) - find all orders in sheet orders, that will be delivered on B1 date, and list all products with this delivery date under cell B1 (sheet delivery).
Example:
Sheet order
A
B
C
Date of delivery
Product
1.9.2013
orange
[Code] ........
Unfortunately simple Pivot table is not able to do this simple list.
View 3 Replies
View Related
Oct 7, 2008
I have a macro in one file which uses an input box where the user types in a subcontractor code to retrieve address and contact details from another file.
If the user inputs the incorrect subcontractor code, an error message box pops up. Currently the user then has to select a button in the original file to restart the macro at the input box stage.
What I want to happen is when ok is selected on the error message, to return to the input box so in my macro below where the error message says "An error occurred - you asked for a subcontractor that does not exist. Please try again." and the user selects OK, I would like it to return to the 'Enter the Subcontractor line and the "Please enter the Subcontractor code" input box reappears.
View 8 Replies
View Related
Aug 8, 2008
A little context:
Searching this forum for "sleep", "delay" or "animation" will bring up a host of threads referring to the kernel32 function, sleep. This is a great way of putting small delays into code without the potential "synchronisation" problems of application .ontime calls or the "ugliness" of multiple-thousands looping.
My question:
When I call sleep (with values in the hundreds of milliseconds) several times in a macro, it seems like the computer just "gives up" screen updating after a few (maybe 50) iterations. The macro still takes the time I'd expect, accounting for all the sleeps, but it stops showing the intermediate steps and just shows the end result after the macro ends.
Is this something to do with RAM? Is there something about calling this command too much or too frequently that kernel32 doesn't like? Is my computer a useless bag of nails and spit?
View 3 Replies
View Related
May 22, 2013
I have a macro running in a workbook that gathers some data (a date, a string and a few arrays). Towards the end of this macro, I need it to open another workbook and run a macro that sits in this other workbook, using the data from the first workbook. I then need it to return some results (several integers) back to the first macro to be pasted into the first workbook.
I gather that I can't use 'Call' as the second macro is in another workbook.
I've found that I can use Application.Run but I'm unsure how to carry variables back and forth using this.
How to move the variables between macros / workbooks using the Application.Run option, or maybe another way of doing things?
View 1 Replies
View Related
Mar 26, 2009
I want to have a small button at the top of the sheet that when pressed, will automatically reset all filtered autofilters back to show all.
View 9 Replies
View Related
May 1, 2013
Below is a macro I'm trying to create. The basically it's supposed to be a quick simple macro to save lots of time. I want to select current tab, move/copy (create copy) to the end....then select the tab I've just copied and copy paste special the values (then rename the tab to todays date) FYI the tab I'm copying is called rebuy shipping.
However, I want to then do the same at a later date with the most recent tab, but with the below code it always selects and copies the original tab:
Sub macrorebuy1()
'
' macrorebuy1 Macro
'
' Keyboard Shortcut: Ctrl+k
'
Sheets("Rebuy Shipping").Select
[Code] .....
I assume this is a simple error. I've tried inserting Activesheet.previous.
View 2 Replies
View Related
Jan 29, 2014
I have a spreadsheet with 31 rows and 8 columns that are completed as a checklist.
I have a macro that currently increments where required and also pastes the 31 rows again after
What I am looking for is some code whereby if I run a Macro the previous 31 rows are hidden
View 5 Replies
View Related
Jun 23, 2007
I need a macro to loop through a column and delete any date prior to todays date, as well as all cells in the deleted date's row.
View 2 Replies
View Related
