Do Loop until empty row (column "A")

J

Jim15

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 perfor
the calculations in a do loop until the last empty row in m
spreadsheet. Please provide answer with VB code, please like

Loop

(my code)

End

Thanks,

Jim1
 
N

Nigel_hough

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 :)
 
T

Tom Ogilvy

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
 
J

Jim15

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
 
T

Tom Ogilvy

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])"
 
J

Jim15

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
 
J

Jim15

Rows P - Y still empty with modification below. Any suggestions? I
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)
Loo
 
J

Jim15

Almost there. The following code will calculate the 1st line result
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,

Ji
 
T

Tom Ogilvy

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
 
J

Jim15

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Do Until Loop 2
Loop returns only one value, does not loop & an assignment to columnhow-to problem. 26
Nested Do Until Loop 4
loop help 6
loop until empty row and column problem (format cells until the lastrow and column in a report) 2
do until blank 2
Do Until Loop Help 3
Do Until empty loop 2

Top