I need help with a loop that will end when the following vba script will reach a blank cell in column A.
whal the loop does is copy the time in cell D1 to D2 and D3 to D4 and so on until a blank Cell in column A is reached
Thank's in advance for any help provided.
Larry
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=R[-1]C"
Range("D4").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=R[-1]C"
Range("D6").Select
Hi Larry,
You can use something like this
Sub copystuff()
Dim i As Long
i = 0
While Range("A1").Offset(i, 0).Value <> ""
INSERT YOUR CODE HERE
i = i + 1
Wend
End Sub
Without seeing your data I can't be more specific. By seeing it I could maybe offer some suggestions as to how to copy the data more efficiently.
Regards
Phil
Hi Phil
Here is some sample data and what I am trying to do has been done in column D2 and D4.
what has been done is I copied D1 to D2 and D3 to D4.
Now I want to copy that formula (all odd number rows in column D to the even number rows in column D) down the entire column and end when I get to the end of the column.
I guess that the macro could end at the first blank cell in column A.
I do all right using the macro recorder but I can't seem to figure out how to start and stop a loop function.
Any help you can give (an old man 84yrs) will be greatly appreciated.
Thanks for the reply.
Larry
311 | 57 | UL Monroe | 12:00 PM |
312 | -19 | Iowa State | 12:00 PM |
313 | 60 | Miami Ohio | 3:30 PM |
314 | -38 | Ohio State | 3:30 PM |
315 | 62 | Charlotte | 7:30 PM |
316 | -41 | Clemson | |
317 | 59.5 | Connecticut | 12:00 PM |
318 | -26.5 | Indiana | |
319 | -10 | Central Florida | 3:30 PM |
320 | 58 | Pittsburgh | |
321 | 65 | UL Lafayette | 2:00 PM |
322 | -3.5 | Ohio | |
323 | -10 | Nebraska | 8:00 PM |
324 | 63 | Illinois |
Hi Larry,
This code will do what you want. It uses FillDown to fill an empty cell with the value above it
Sub FillDown()
Dim i As Long
i = 2
While Range("A" & i).Value <> ""
Range("D" & i).FillDown
i = i + 2
Wend
End Sub
You can do this by hand too by selecting an empty cell e.g. D2 and using the Fill Down shortcut CTRL+D. Here's a list of useful keyboard shortcuts for Excel.
Please see attached file for working code.
Great to hear that at 84 you are still learning 🙂
Regards
Phil