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

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

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

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

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

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

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