Find/Replace text with formula

[Guest]

I have a spreadsheet with a column that has the value 0 in some cells. I want
to write a macro to do a search for column J and every time it finds 0 it
puts my formula in there. I got it to put the formula in there, but not
adjust for what row it is on.

Columns("J:J").Select
Selection.Replace What:="0", Replacement:="=(E3+F3+G3+H3)-I3", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False
Range("J5").Select

This works fine for row 3, but for row 5 it should put =(E5+F5+G5+H5)-I5. I
know there is an easy way to do this, but it is eluding me right now. Any
help would be greatly appreciated! Thanks!

 

[Guest]

Dim rng as Range, sAddr as String
set rng = Columns("J:J").Find( What:="0")
if not rng is nothing then
sAddr = rng.Address
do
rng.Formula = Replace("=(E3+F3+G3+H3)-I3","3",rng.row)
set rng = columns("J:J").findNext(rng)
if rng is nothing then exit do
Loop while rng.Address <> sAddr
End if

 

[Gary Keramidas]

maybe something like this, just adjust the range in column j

Sub test()
Dim cell As Range
For Each cell In Range("j1:j100")
cell.Replace What:="0", Replacement:="=(E" & cell.Row & "+F" & cell.Row & _
"+G" & cell.Row & "+H" & cell.Row & ")-I" & cell.Row, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False
Next
Range("J5").Select
End Sub

 

[Bob Phillips]

Dim cell As Range
Dim sFirst As String
With Columns("J:J")
Set cell = .Find(What:="0", _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False)
If Not cell Is Nothing Then
sFirst = cell.Address
Do
cell.FormulaR1C1 = "=SUM(RC5:RC8,-RC9)"
Set cell = .FindNext(cell)
Loop While Not cell Is Nothing And cell.Address <> sFirst
End If
End With

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

Thank you! That worked.

maybe something like this, just adjust the range in column j

Sub test()
Dim cell As Range
For Each cell In Range("j1:j100")
cell.Replace What:="0", Replacement:="=(E" & cell.Row & "+F" & cell.Row & _
"+G" & cell.Row & "+H" & cell.Row & ")-I" & cell.Row, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False
Next
Range("J5").Select
End Sub

 

[Guest]

They all worked for me.