Do Loop until empty row (column "A")

**Jim15** · 08-15-2005, 09:47 AM

I have data in columns A - O and am calculating values for columns P - AA. I get stuck in a do loop in row 2. I want the program to perform the calculations in a do loop until the last empty row in my spreadsheet. Please provide answer with VB code, please like

Loop

(my code)

End

Thanks,

Jim15

**Nigel_hough** · 08-15-2005, 10:14 AM

This is just a simple loop until you reach the last row that isn't blank

Do While (Selection.Offset(1, 0) <> "")

'Code

Selection.Offset(1, 0).Select
Loop

Hope This is what you want

Nigel

**Jim15** · 08-15-2005, 10:46 AM

Looks like the do loop works but it is not filling in columns P - AA. I think I have a problem in my code for each of the calculations. How do I reference the columns? P - AA? All formulas show.

Do While (Selection.Offset(1, 0) <> "")
(P) ActiveCell.FormulaR1C1 = "=IF(RC[-7]=0,"" "",(RC[-6]*1000)/RC[-7])"
ActiveCell.FormulaR1C1 = _
"=IF((RC[-8]+RC[-6])=0,"" "",(RC[-7]*1000)/(RC[-8]+RC[-6]))"
ActiveCell.FormulaR1C1 = "=IF(RC[-9]=0,"" "",RC[-7]/RC[-9])"
ActiveCell.FormulaR1C1 = _
"=IF((RC[-10]+RC[-8])=0,"" "",RC[-8]/(RC[-10]+RC[-8]))"
ActiveCell.FormulaR1C1 = "=IF(RC[-11]=0,"" "",RC[-10]/RC[-11])"
ActiveCell.FormulaR1C1 = "=IF(RC[-11]=0,"" "",RC[-12]/RC[-11])"
ActiveCell.FormulaR1C1 = "=IF(RC[-12]=0,"" "",RC[-11]/RC[-12])"
ActiveCell.FormulaR1C1 = "=SUM(RC[-14]/30)"
ActiveCell.FormulaR1C1 = "=SUM(RC[-14]/30)"
(AA) ActiveCell.FormulaR1C1 = "=SUM(RC[-14]/30)"
Selection.Offset(1, 0).Select

Thanks,

Jim
Loop

Tom Ogilvy · 08-15-2005, 11:05 AM

Dim rng as Range, i as Long
i = 1
set rng = Cells(i,1)
do while Application.countA(rng.Resize(1,15)) <> 0
' process the row
i = i + 1
set rng = rng.offset(1,0)
Loop

--
Regard
"Jim15" <Jim15.1tt6ui_1124114775.4408@excelforum-nospam.com> wrote in
message news:Jim15.1tt6ui_1124114775.4408@excelforum-nospam.com...
>
> I have data in columns A - O and am calculating values for columns P -
> AA. I get stuck in a do loop in row 2. I want the program to perform
> the calculations in a do loop until the last empty row in my
> spreadsheet. Please provide answer with VB code, please like
>
> Loop
>
> (my code)
>
> End
>
> Thanks,
>
> Jim15
>
>
> --
> Jim15
> ------------------------------------------------------------------------
> Jim15's Profile:
http://www.excelforum.com/member.php...o&userid=26300
> View this thread: http://www.excelforum.com/showthread...hreadid=395789
>

**Tom Ogilvy** · 08-15-2005, 11:17 AM

You put all your formulas in the same cell - the activecell. So each one overwrites the last.

Modify your code something like this

Do While (Selection.Offset(1, 0) <> "")
Cells(ActiveCell.row,"P")FormulaR1C1 = "=IF(RC[-7]=0,"" "",(RC[-6]*1000)/RC[-7])"
Cells(ActiveCell.row,"Q").FormulaR1C1 = _
"=IF((RC[-8]+RC[-6])=0,"" "",(RC[-7]*1000)/(RC[-8]+RC[-6]))"
Cells(ActiveCell.Row,"R").FormulaR1C1 = "=IF(RC[-9]=0,"" "",RC[-7]/RC[-9])"

--
Regards,
Tom Ogilvy

**Jim15** · 08-15-2005, 12:12 PM

The last thread code did not work. I am using Range "P2" to start the calculations in P2 and continue through Y2, then go to P3 through Y3, etc. until the last row is reached. It is looping until the last row but not filling out columns P - Y. Once finished, it displays "#VALUE!" in P2 cell with the formulate B2/30 which should be in Y2.

Thanks,

Jim

Range("P2").Select
'
' Start of row calculations.
'
Dim rng As Range, i As Long
i = 2
Set rng = Cells(i, 1)
Do While Application.CountA(rng.Resize(1, 15)) <> 0
'
' Calculate columns P - Y (10 columns)
'
ActiveCell.FormulaR1C1 = "=IF(RC[-7]=0,"" "",(RC[-6]*1000)/RC[-7])"
ActiveCell.FormulaR1C1 = _
"=IF((RC[-8]+RC[-6])=0,"" "",(RC[-7]*1000)/(RC[-8]+RC[-6]))"
ActiveCell.FormulaR1C1 = "=IF(RC[-9]=0,"" "",RC[-7]/RC[-9])"
ActiveCell.FormulaR1C1 = _
"=IF((RC[-10]+RC[-8])=0,"" "",RC[-8]/(RC[-10]+RC[-8]))"
ActiveCell.FormulaR1C1 = "=IF(RC[-11]=0,"" "",RC[-10]/RC[-11])"
ActiveCell.FormulaR1C1 = "=IF(RC[-11]=0,"" "",RC[-12]/RC[-11])"
ActiveCell.FormulaR1C1 = "=IF(RC[-12]=0,"" "",RC[-11]/RC[-12])"
ActiveCell.FormulaR1C1 = "=SUM(RC[-14]/30)"
ActiveCell.FormulaR1C1 = "=SUM(RC[-14]/30)"
ActiveCell.FormulaR1C1 = "=SUM(RC[-14]/30)"
'
' Next row.
'
i = i + 1
Set rng = rng.Offset(1, 0)
Loop

**Jim15** · 08-15-2005, 12:39 PM

Rows P - Y still empty with modification below. Any suggestions? It appears the loops is not executing with the following code.

Jim

Range("P2").Select
'
' Start of row calculations.
'
Dim rng As Range, i As Long
i = 2
Set rng = Cells(i, 1)
Do While (Selection.Offset(1, 0) <> "")
'
' Calculate columns P - Y.
'
Cells(ActiveCell.Row, "P").FormulaR1C1 = "=IF(RC[-7]=0,"" "",(RC[-6]*1000)/RC[-7])"
Cells(ActiveCell.Row, "Q").FormulaR1C1 = _
"=IF((RC[-8]+RC[-6])=0,"" "",(RC[-7]*1000)/(RC[-8]+RC[-6]))"
Cells(ActiveCell.Row, "R").FormulaR1C1 = "=IF(RC[-9]=0,"" "",RC[-7]/RC[-9])"
Cells(ActiveCell.Row, "S").FormulaR1C1 = _
"=IF((RC[-10]+RC[-8])=0,"" "",RC[-8]/(RC[-10]+RC[-8]))"
Cells(ActiveCell.Row, "T").FormulaR1C1 = "=IF(RC[-11]=0,"" "",RC[-10]/RC[-11])"
Cells(ActiveCell.Row, "U").FormulaR1C1 = "=IF(RC[-11]=0,"" "",RC[-12]/RC[-11])"
Cells(ActiveCell.Row, "V").FormulaR1C1 = "=IF(RC[-12]=0,"" "",RC[-11]/RC[-12])"
Cells(ActiveCell.Row, "W").FormulaR1C1 = "=SUM(RC[-14]/30)"
Cells(ActiveCell.Row, "X").FormulaR1C1 = "=SUM(RC[-14]/30)"
Cells(ActiveCell.Row, "Y").FormulaR1C1 = "=SUM(RC[-14]/30)"
'
' Next row.
'
i = i + 1
Set rng = rng.Offset(1, 0)
Loop

**Jim15** · 08-15-2005, 03:46 PM

Almost there. The following code will calculate the 1st line results only. How do I get it to calculate all of the lines to the last row?

Range("P2").Select
'
' Start of row calculations.
'
Dim rng As Range, i As Long
i = 1
Set rng = Cells(i, 1)
Do While Application.CountA(rng.Resize(1, 15)) <> 0
'
' Calculate columns P - Y.
'
Cells(ActiveCell.Row, "P").FormulaR1C1 = "=IF(RC[-7]=0,"" "",(RC[-6]*1000)/RC[-7])"
Cells(ActiveCell.Row, "Q").FormulaR1C1 = _
"=IF((RC[-8]+RC[-6])=0,"" "",(RC[-7]*1000)/(RC[-8]+RC[-6]))"
Cells(ActiveCell.Row, "R").FormulaR1C1 = "=IF(RC[-9]=0,"" "",RC[-7]/RC[-9])"
Cells(ActiveCell.Row, "S").FormulaR1C1 = _
"=IF((RC[-10]+RC[-8])=0,"" "",RC[-8]/(RC[-10]+RC[-8]))"
Cells(ActiveCell.Row, "T").FormulaR1C1 = "=IF(RC[-11]=0,"" "",RC[-10]/RC[-11])"
Cells(ActiveCell.Row, "U").FormulaR1C1 = "=IF(RC[-11]=0,"" "",RC[-12]/RC[-11])"
Cells(ActiveCell.Row, "V").FormulaR1C1 = "=IF(RC[-12]=0,"" "",RC[-11]/RC[-12])"
Cells(ActiveCell.Row, "W").FormulaR1C1 = "=SUM(RC[-14]/30)"
Cells(ActiveCell.Row, "X").FormulaR1C1 = "=SUM(RC[-14]/30)"
Cells(ActiveCell.Row, "Y").FormulaR1C1 = "=SUM(RC[-14]/30)"
'
' Next row.
'
i = i + 1
Set rng = rng.Offset(1, 0)
Loop

Thanks,

Jim

Tom Ogilvy · 08-15-2005, 05:05 PM

This worked fine for me. You don't want to select column P - that should be
empty if your are entering formulas there. Select O1 instead - implemented
in my code: (starts writing in P2)

Sub ABC()
Range("O1").Select
'
' Start of row calculations.
'

Do While (Selection.Offset(1, 0) <> "")
Selection.Offset(1, 0).Select
Debug.Print Selection.Address
'
' Calculate columns P - Y.
'
Cells(ActiveCell.Row, "P").FormulaR1C1 = _
"=IF(RC[-7]=0,"""",(RC[-6]*1000)/RC[-7])"
Cells(ActiveCell.Row, "Q").FormulaR1C1 = _
"=IF((RC[-8]+RC[-6])=0,"" "",(RC[-7]*1000)/" & _
"(RC[-8]+RC[-6]))"
Cells(ActiveCell.Row, "R").FormulaR1C1 = _
"=IF(RC[-9]=0,"""",RC[-7]/RC[-9])"
Cells(ActiveCell.Row, "S").FormulaR1C1 = _
"=IF((RC[-10]+RC[-8])=0,"" "",RC[-8]/(RC[-10]+RC[-8]))"
Cells(ActiveCell.Row, "T").FormulaR1C1 = _
"=IF(RC[-11]=0,"""",RC[-10]/RC[-11])"
Cells(ActiveCell.Row, "U").FormulaR1C1 = _
"=IF(RC[-11]=0,"""",RC[-12]/RC[-11])"
Cells(ActiveCell.Row, "V").FormulaR1C1 = _
"=IF(RC[-12]=0,"""",RC[-11]/RC[-12])"
Cells(ActiveCell.Row, "W").FormulaR1C1 = _
"=SUM(RC[-14]/30)"
Cells(ActiveCell.Row, "X").FormulaR1C1 = _
"=SUM(RC[-14]/30)"
Cells(ActiveCell.Row, "Y").FormulaR1C1 = _
"=SUM(RC[-14]/30)"
'
' Next row.
'
Loop

End Sub

--
Regards,
Tom Ogilvy

"Jim15" <Jim15.1ttf6f_1124125560.6021@excelforum-nospam.com> wrote in
message news:Jim15.1ttf6f_1124125560.6021@excelforum-nospam.com...
>
> Rows P - Y still empty with modification below. Any suggestions? It
> appears the loops is not executing with the following code.
>
> Jim
>
>
> Range("P2").Select
> '
> ' Start of row calculations.
> '
> Dim rng As Range, i As Long
> i = 2
> Set rng = Cells(i, 1)
> Do While (Selection.Offset(1, 0) <> "")
> '
> ' Calculate columns P - Y.
> '
> Cells(ActiveCell.Row, "P").FormulaR1C1 = "=IF(RC[-7]=0,""
> "",(RC[-6]*1000)/RC[-7])"
> Cells(ActiveCell.Row, "Q").FormulaR1C1 = _
> "=IF((RC[-8]+RC[-6])=0,"" "",(RC[-7]*1000)/(RC[-8]+RC[-6]))"
> Cells(ActiveCell.Row, "R").FormulaR1C1 = "=IF(RC[-9]=0,""
> "",RC[-7]/RC[-9])"
> Cells(ActiveCell.Row, "S").FormulaR1C1 = _
> "=IF((RC[-10]+RC[-8])=0,"" "",RC[-8]/(RC[-10]+RC[-8]))"
> Cells(ActiveCell.Row, "T").FormulaR1C1 = "=IF(RC[-11]=0,""
> "",RC[-10]/RC[-11])"
> Cells(ActiveCell.Row, "U").FormulaR1C1 = "=IF(RC[-11]=0,""
> "",RC[-12]/RC[-11])"
> Cells(ActiveCell.Row, "V").FormulaR1C1 = "=IF(RC[-12]=0,""
> "",RC[-11]/RC[-12])"
> Cells(ActiveCell.Row, "W").FormulaR1C1 = "=SUM(RC[-14]/30)"
> Cells(ActiveCell.Row, "X").FormulaR1C1 = "=SUM(RC[-14]/30)"
> Cells(ActiveCell.Row, "Y").FormulaR1C1 = "=SUM(RC[-14]/30)"
> '
> ' Next row.
> '
> i = i + 1
> Set rng = rng.Offset(1, 0)
> Loop
>
>
> --
> Jim15
> ------------------------------------------------------------------------
> Jim15's Profile:
http://www.excelforum.com/member.php...o&userid=26300
> View this thread: http://www.excelforum.com/showthread...hreadid=395789
>

**Jim15** · 08-17-2005, 10:01 AM

Thanks for the reply but the last thread code did not work for me. Maybe I should attach all code. I am skipping the header code as I know it works and showing the last header in row 1, column Y as the 1st line. This calculates the columns P2 - Y2 in row 2 only. I had about 13,000 rows in the last example and you could tell it was going through the iterations but not filling in the data. It took a while and finally stopped so I could tell it was looping through the columns; however, the columns Px - Yx were not filled out.

Must be something simple.

ActiveCell.FormulaR1C1 = "Daily Wtr"
Range("P2").Select
'
' Start of row calculations.
'
Dim rng As Range, i As Long
i = 1
Set rng = Cells(i, 1)
Do While Application.CountA(rng.Resize(1, 15)) <> 0
'
' Calculate columns P - Y.
'
Cells(ActiveCell.Row, "P").FormulaR1C1 = "=IF(RC[-7]=0,"" "",(RC[-6]*1000)/RC[-7])"
Cells(ActiveCell.Row, "Q").FormulaR1C1 = _
"=IF((RC[-8]+RC[-6])=0,"" "",(RC[-7]*1000)/(RC[-8]+RC[-6]))"
Cells(ActiveCell.Row, "R").FormulaR1C1 = "=IF(RC[-9]=0,"" "",RC[-7]/RC[-9])"
Cells(ActiveCell.Row, "S").FormulaR1C1 = _
"=IF((RC[-10]+RC[-8])=0,"" "",RC[-8]/(RC[-10]+RC[-8]))"
Cells(ActiveCell.Row, "T").FormulaR1C1 = "=IF(RC[-11]=0,"" "",RC[-10]/RC[-11])"
Cells(ActiveCell.Row, "U").FormulaR1C1 = "=IF(RC[-11]=0,"" "",RC[-12]/RC[-11])"
Cells(ActiveCell.Row, "V").FormulaR1C1 = "=IF(RC[-12]=0,"" "",RC[-11]/RC[-12])"
Cells(ActiveCell.Row, "W").FormulaR1C1 = "=SUM(RC[-14]/30)"
Cells(ActiveCell.Row, "X").FormulaR1C1 = "=SUM(RC[-14]/30)"
Cells(ActiveCell.Row, "Y").FormulaR1C1 = "=SUM(RC[-14]/30)"
'
' Next row.
'
i = i + 1
Set rng = rng.Offset(1, 0)
Loop
'
' Format columns P - Y.
'
Columns("P:R").Select
Selection.NumberFormat = "#,##0"
Columns("S:S").Select
Selection.NumberFormat = "0%"
Columns("T:V").Select
Selection.NumberFormat = "#,##0.0000"
Columns("W:Y").Select
Selection.NumberFormat = "#,##0"
Cells.Select
Range("H1").Activate
Selection.Columns.AutoFit