replace cell with formula

[rpick60]

I would like to find all of the "0" in range L8:L500 and replace them
with the following
for each 0 I would like to replace it with a formula of =sum((K
+row#)*(c+row#))
so if 0 is in L12 i would replace it with =sum(K12*C12)
if 0 is in L20 i wouls replace it with =sum(K20*K12)

The problem is i never now what row is going to have a 0
and my sheet is updated quite often
if the range has blanks i want them to remain blank.
when the sheet is updated it changes all the formulas back to 0.

any help would be of great help.

 

[Gary''s Student]

Sub formfix()
For i = 8 To 500
If IsEmpty(Cells(i, "L")) Then
Else
If Cells(i, "L").Value = 0 Then
Cells(i, "L").Formula = "=sum(K" & i & "*C" & i & ")"
End If
End If
Next
End Sub

 

[Per Jessen]

Hi

I am a bit confused by your example!

Asuming that you want to sum the cell values in column C through
column K this should do it.

Sub Replace_0_With_Formula
Dim TargetRange as Range
Set TargetRange =Range("L8:L500")
For Each C in TargetRange
If c.Value=0 Then
MyRow=c.Row
c.Formula="=Sum(K" & MyRow & ":C" & MyRow & ")"
End If
Next
ENd Sub

Regards

Per