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!

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!

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" <[email protected]> wrote in message
news:[email protected]...
>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!

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" <[email protected]> wrote in message
news:[email protected]...
> 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!

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" <[email protected]> wrote in message
> news:[email protected]...
> >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!
>
>
>

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" <[email protected]> wrote in message
> > news:[email protected]...
> > >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!
> >
> >
> >