Find/Replace text with formula
 

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!

Find/Replace text with formula
 

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
--
Regards,
Tom Ogilvy



"Jasmine" wrote:

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!

Find/Replace text with formula
 

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

--


Gary


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

Find/Replace text with formula
 

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)

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

Find/Replace text with formula
 

Thank you! That worked.

"Gary Keramidas" wrote:

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

--


Gary


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

Find/Replace text with formula
 

They all worked for me.


--
Regards,
Tom Ogilvy


"Jasmine" wrote:

Thank you! That worked.

"Gary Keramidas" wrote:

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

--


Gary


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