Help in creating formula in a macro
 

The following code is working, now I need to use the value in line 6
and divided into line 7...how to do

1. With ActiveSheet
2. lfR = 1
3. For lcR = 1 To .Cells(.Rows.Count, "B").End(xlUp).Row + 1
4. If Len(Trim(.Cells(lcR, 1))) = 0 Then
5. .Cells(lcR, 2) = .Cells(lcR - 1, 2) '& "- Averages"
6. .Cells(lcR, 8).Formula = "=SUM(H" & lfR & ":H" & lcR - 1 &
")"
7. .Cells(lcR, 9).Formula = "=SUM(I" & lfR & ":I" & lcR - 1 &
")"
------ .Cells(lcR, 7).Formula = <-------need help here, the sum of
H divided into the sum of I.
8. lfR = lcR + 1
9. End If
10. Next
11. End With

Help in creating formula in a macro
 

Wouldn't you just want the two cell addresses divided out?

..Cells(lcR, 7).Formula = "=" & .Cells(lcR, 8).Address(0, 0) / _
.Cells(lcR, 8).Address(0, 0)

The (0, 0) arguments for the Address property just means to use a relative
row and column reference.

--
Rick (MVP - Excel)


"S Himmelrich" wrote in message
...
The following code is working, now I need to use the value in line 6
and divided into line 7...how to do

1. With ActiveSheet
2. lfR = 1
3. For lcR = 1 To .Cells(.Rows.Count, "B").End(xlUp).Row + 1
4. If Len(Trim(.Cells(lcR, 1))) = 0 Then
5. .Cells(lcR, 2) = .Cells(lcR - 1, 2) '& "- Averages"
6. .Cells(lcR, 8).Formula = "=SUM(H" & lfR & ":H" & lcR - 1 &
")"
7. .Cells(lcR, 9).Formula = "=SUM(I" & lfR & ":I" & lcR - 1 &
")"
------ .Cells(lcR, 7).Formula = <-------need help here, the sum of
H divided into the sum of I.
8. lfR = lcR + 1
9. End If
10. Next
11. End With

Help in creating formula in a macro
 

x = .Cells*lcR, 9).Value/.Cells(lcR, 8).Value




"S Himmelrich" wrote in message
...
The following code is working, now I need to use the value in line 6
and divided into line 7...how to do

1. With ActiveSheet
2. lfR = 1
3. For lcR = 1 To .Cells(.Rows.Count, "B").End(xlUp).Row + 1
4. If Len(Trim(.Cells(lcR, 1))) = 0 Then
5. .Cells(lcR, 2) = .Cells(lcR - 1, 2) '& "- Averages"
6. .Cells(lcR, 8).Formula = "=SUM(H" & lfR & ":H" & lcR - 1 &
")"
7. .Cells(lcR, 9).Formula = "=SUM(I" & lfR & ":I" & lcR - 1 &
")"
------ .Cells(lcR, 7).Formula = <-------need help here, the sum of
H divided into the sum of I.
8. lfR = lcR + 1
9. End If
10. Next
11. End With