Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
End of data loop
Hi guys,
I need help with this specific macro i've bene tyring to get right. I've been doing it in my head in pseudo-code but can't seem to figure out the proper syntax in excel i guess. I have multiple rows of data and I want to loop through them and insert specific formules. In my head, its something like this integer x=2 while(A(x) is not null) do C(x) = <formula D(x) = <formula E(x) = <formula x++ end while Can anyone give me a few pointers? Thanks in advance |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
End of data loop
Hi,
Some examples: The first is virtual your pseudo code. The second finds the last non-blank row in column A and loops through all rows; the third is si,ilar but assigns the same formula (or any data) to there consecutive columns e.g C, D and E. HTH Sub DoLoop1() Dim r As Long r = 2 Do While Cells(r, "A") < "" Cells(r, "C") = "Formula 1" Cells(r, "D") = "Formula 2" Cells(r, "E") = "Formula 3" r = r + 1 Loop End Sub Sub DoLoop2() Dim r As Long, lastrow As Long ' Find last non-blank row starting from bootom of column lastrow = Cells(Rows.Count, "A").End(xlUp).Row ' Loop through all rows, starting at 2 For r = 2 To lastrow Cells(r, "C") = "Place Formula here" 'Assign a formula to col C, row r Cells(r, "D") = "Place Formula here" 'Assign a formula to col D, row r Cells(r, "E") = "Place Formula here" 'Assign a formula to col E, row r Next r End Sub Sub DoLoop3() Dim r As Long, lastrow As Long ' Find last non-blank row starting from bootom of column lastrow = Cells(Rows.Count, "A").End(xlUp).Row ' Loop through all rows, starting at 2 For r = 2 To lastrow Cells(r, "C").Resize(1, 3) = "Place Formula here" 'Assign SAME formula to cols C,D & E in row r Next r End Sub "mortals" wrote: Hi guys, I need help with this specific macro i've bene tyring to get right. I've been doing it in my head in pseudo-code but can't seem to figure out the proper syntax in excel i guess. I have multiple rows of data and I want to loop through them and insert specific formules. In my head, its something like this integer x=2 while(A(x) is not null) do C(x) = <formula D(x) = <formula E(x) = <formula x++ end while Can anyone give me a few pointers? Thanks in advance |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
End of data loop
Sub ABCD()
Dim x as Long x = 2 Do while not isemtpy(cells(x,"A")) Cells(x,"C").formula = "=Formula" Cells(x,"D").formula = "=Formula" Cells(x,"E").Formula = "=formula" x = x + 1 Loop end Sub -- Regards, Tom Ogilvy "mortals" wrote in message oups.com... Hi guys, I need help with this specific macro i've bene tyring to get right. I've been doing it in my head in pseudo-code but can't seem to figure out the proper syntax in excel i guess. I have multiple rows of data and I want to loop through them and insert specific formules. In my head, its something like this integer x=2 while(A(x) is not null) do C(x) = <formula D(x) = <formula E(x) = <formula x++ end while Can anyone give me a few pointers? Thanks in advance |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
End of data loop
Awesome, the loops seems to work but I have run into another
cinderblock. This is an example of one of the formulas that I am using based on what you guys passed on to me. Cells(x, "C").Formula = "=(Data!Cells(x, "P")+Data!Cells(x,"R")/24" It keeps giving me a compiled error, unexpected end of statement. It keeps pointing to the P and I can't figure out how to get around it. I've tried single quotation marks, putting them in separate brackets and what not but it doesn't work. =/ I'm stumped, guys. Help would be appreciated. -Simon the newbie |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
End of data loop
Mortals,
You need to use a double-double quote mark - "" to include a quote within a text string. ".....(x,""P..... "mortals" wrote: Awesome, the loops seems to work but I have run into another cinderblock. This is an example of one of the formulas that I am using based on what you guys passed on to me. Cells(x, "C").Formula = "=(Data!Cells(x, "P")+Data!Cells(x,"R")/24" It keeps giving me a compiled error, unexpected end of statement. It keeps pointing to the P and I can't figure out how to get around it. I've tried single quotation marks, putting them in separate brackets and what not but it doesn't work. =/ I'm stumped, guys. Help would be appreciated. -Simon the newbie |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Advancing outer Loop Based on criteria of inner loop | Excel Programming | |||
Loop through Filtered Data | Excel Programming | |||
Need help with loop to import data | Excel Programming | |||
Need help with loop to import data | Excel Programming | |||
Checking data with a loop | Excel Programming |