Log In
Register
Home
New Member
September 18, 2019
Offline
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
October 5, 2010
Offline
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
New Member
September 18, 2019
Offline
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 |
October 5, 2010
Offline
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
1 Guest(s)
