Macro to change value 2 cols over from search
 

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

Macro to change value 2 cols over from search
 

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

Macro to change value 2 cols over from search
 

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

Macro to change value 2 cols over from search
 

thanks

Macro to change value 2 cols over from search
 

" 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