Macro - update the cell to the left after a find

[Guest]

Hello,
I have a macro that I need to find a certain cell and update the cell to the
right of that one with a certain value. My Macro is below; it currently finds
the record for Gregory T Pattens. I then want it to update the cell directly
to the right of this one with the value "n/a". Gregory T Pattens will not
consistently be in the same location, so I can't have it locked down to
always update a specific cell. I had thought that the RC[1] would move to the
right but instead it's overwriting Gregory T Patten's name with
"RC[1]=""n/a"""

Thank you!!!


Sub Macro4()

' Macro4 Macro
' Macro recorded 4/12/2007 by Jen Frost
'
Columns("B:B").Select
Selection.Find(What:="Pattins,Gregory T", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.FormulaR1C1 = "RC[1]=""n/a"""
End Sub

 

[merjet]

Use this instead:
ActiveCell.Offset(0,1) = "n/a"

Merjet

 

[Guest]

Hi Jennfier,

Try it this way:

ActiveCell.Offset(0, 1).Value = "n/a"

 

[Guest]

Give this a whirl. Your existing code will bomb if Greg is not found. This
code will be just fine...

Sub Macro4()
' Macro4 Macro
' Macro recorded 4/12/2007 by Jen Frost
Dim rngFound As Range
Set rngFound = Columns("B:B").Find(What:="Pattins,Gregory T", _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
MatchCase:=False)
If Not rngFound Is Nothing Then rngFound.Offset(0, 1).Value = "n/a"
End Sub