relative versus specific cells in macros/vba
 

A macro I'm trying to write has to include a "copy-find-paste-modify" string,
but I don't know how to generalize the active cell choice to be dependant
upon the results of the "find" rather than a specific cell typed in vba.

So, how do I write "find cell, move four cells to the right of found cell,
modify that cell" rather than, "move to r1c1, modify?" I'm looking
essentially for a variable plug within r1c1 rather than a specific grid
placement.

relative versus specific cells in macros/vba
 

Dim fCell as Range
Set fCell = Cells.Find(What:=SearchText, After:=StartCell,
LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=True).Offset(0,4)
fCell = "Hello From Iowa"

HTH

Die_Another_Day
Stutsman wrote:
A macro I'm trying to write has to include a "copy-find-paste-modify" string,
but I don't know how to generalize the active cell choice to be dependant
upon the results of the "find" rather than a specific cell typed in vba.

So, how do I write "find cell, move four cells to the right of found cell,
modify that cell" rather than, "move to r1c1, modify?" I'm looking
essentially for a variable plug within r1c1 rather than a specific grid
placement.

relative versus specific cells in macros/vba
 

One slight modification that makes things a bit more robust:

Dim fCell As Range
Set fCell = Cells.Find(What:=SearchText, _
After:=StartCell, _
LookAt:=xlWhole, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=True)
If fCell Is Nothing Then
Msgbox SearchText & " was not found."
Else
With fCell.Offset(0, 4)
.Value = .Value & " modified"
End With
End If



In article .com,
"Die_Another_Day" wrote:

Dim fCell as Range
Set fCell = Cells.Find(What:=SearchText, After:=StartCell,
LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=True).Offset(0,4)
fCell = "Hello From Iowa"

HTH

Die_Another_Day
Stutsman wrote:
A macro I'm trying to write has to include a "copy-find-paste-modify"
string,
but I don't know how to generalize the active cell choice to be dependant
upon the results of the "find" rather than a specific cell typed in vba.

So, how do I write "find cell, move four cells to the right of found cell,
modify that cell" rather than, "move to r1c1, modify?" I'm looking
essentially for a variable plug within r1c1 rather than a specific grid
placement.