I'm not to good a complex macros so If someone could help I'd much
appreciate it.I like to create a macro to change to value of a cell 2
cols left of a search string,same row.I have a sheet with 25 cols and
7500 rows,at various places the string oz is in a cell,I need to change
the value in the cell 2 cols left,how could I do this with a macro
?.The search string is usually but not always the same col,the value to
be changed is always second col left. Thanks
SteveC

One way:

Public Sub ReplaceLeftOfOz()
Const sFIND As String = "oz"
Const vREPLACE As Variant = "<Your Value Here"
Dim rFound As Range
Dim sFoundAddr As String

With ActiveSheet
Set rFound = .Cells.Find( _
What:=sFIND, _
LookIn:=xlValues, _
LookAt:=xlWhole, _
MatchCase:=False)
If Not rFound Is Nothing Then
sFoundAddr = rFound.Address
Do
rFound.Offset(0, -2).Value = vREPLACE
Set rFound = .Cells.FindNext(rFound)
Loop Until rFound.Address = sFoundAddr
End If
End With
End Sub


In article .com,
wrote:

I'm not to good a complex macros so If someone could help I'd much
appreciate it.I like to create a macro to change to value of a cell 2
cols left of a search string,same row.I have a sheet with 25 cols and
7500 rows,at various places the string oz is in a cell,I need to change
the value in the cell 2 cols left,how could I do this with a macro
?.The search string is usually but not always the same col,the value to
be changed is always second col left. Thanks
SteveC

thanks

Look at the sample code for the FindNext method. this should get you
started. If you need further help, post back.

--
Regards,
Tom Ogilvy

wrote in message
oups.com...
I'm not to good a complex macros so If someone could help I'd much
appreciate it.I like to create a macro to change to value of a cell 2
cols left of a search string,same row.I have a sheet with 25 cols and
7500 rows,at various places the string oz is in a cell,I need to change
the value in the cell 2 cols left,how could I do this with a macro
?.The search string is usually but not always the same col,the value to
be changed is always second col left. Thanks
SteveC

" wrote:

I'm not to good a complex macros so If someone could help I'd much
appreciate it.I like to create a macro to change to value of a cell 2
cols left of a search string,same row.I have a sheet with 25 cols and
7500 rows,at various places the string oz is in a cell,I need to change
the value in the cell 2 cols left,how could I do this with a macro
?.The search string is usually but not always the same col,the value to
be changed is always second col left. Thanks
SteveC

Assuming you've search with a macro, your next line could be:-

ActiveCell.Offset(0, -2).Value = whatever