Do Loop until empty row (column "A")
 

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

--
Jim1
-----------------------------------------------------------------------
Jim15's Profile: http://www.excelforum.com/member.php...fo&userid=2630
View this thread: http://www.excelforum.com/showthread.php?threadid=39578

Do Loop until empty row (column "A")
 

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


--
Nigel_hough
------------------------------------------------------------------------
Nigel_hough's Profile: http://www.excelforum.com/member.php...o&userid=20623
View this thread: http://www.excelforum.com/showthread...hreadid=395789

Do Loop until empty row (column "A")
 

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" wrote in
message ...

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

Do Loop until empty row (column "A")
 

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


--
Jim15
------------------------------------------------------------------------
Jim15's Profile: http://www.excelforum.com/member.php...o&userid=26300
View this thread: http://www.excelforum.com/showthread...hreadid=395789

Do Loop until empty row (column "A")
 

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


--
Tom Ogilvy
------------------------------------------------------------------------
Tom Ogilvy's Profile: http://www.excelforum.com/member.php...fo&userid=1960
View this thread: http://www.excelforum.com/showthread...hreadid=395789

Do Loop until empty row (column "A")
 

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
------------------------------------------------------------------------
Jim15's Profile: http://www.excelforum.com/member.php...o&userid=26300
View this thread: http://www.excelforum.com/showthread...hreadid=395789

Do Loop until empty row (column "A")
 

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

--
Jim1
-----------------------------------------------------------------------
Jim15's Profile: http://www.excelforum.com/member.php...fo&userid=2630
View this thread: http://www.excelforum.com/showthread.php?threadid=39578

Do Loop until empty row (column "A")
 

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

--
Jim1
-----------------------------------------------------------------------
Jim15's Profile: http://www.excelforum.com/member.php...fo&userid=2630
View this thread: http://www.excelforum.com/showthread.php?threadid=39578

Do Loop until empty row (column "A")
 

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" wrote in
message ...

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

Do Loop until empty row (column "A")
 

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


--
Jim15
------------------------------------------------------------------------
Jim15's Profile: http://www.excelforum.com/member.php...o&userid=26300
View this thread: http://www.excelforum.com/showthread...hreadid=395789