Macro - Find a value and then move down

[Phil Osman]

I have a Macro which finds a unique value in a sheet, but I then want it to
move down 6 rows from where it finds that value to do something else.
What is the code to 'move' around by x number of cells ?

 

[Dave Peterson]

You can use .offset() from the cell you found:


Option Explicit
Sub testme01()

Dim FoundCell As Range
Dim FindWhat As String

FindWhat = "asdf"

With ActiveSheet.UsedRange
Set FoundCell = .Cells.Find(What:=FindWhat, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

If FoundCell Is Nothing Then
MsgBox "not found"
Else
FoundCell.Offset(6, 0).Value = "hi from 6 cells above!"
End If
End With

End Sub

 

[Jim May]

Dave,
Isn't the OP asking for a way to "move to a cell (making it the active
cell)" versus "referencing the value in the cell" ?
thanks,
Jim

 

[Dave Peterson]

It sounded more like he was describing how he would use the cursor keys to move
down 6 rows after he found his cells.

Phil will have to share his real question.

Dave,
Isn't the OP asking for a way to "move to a cell (making it the active
cell)" versus "referencing the value in the cell" ?
thanks,
Jim

 

[Jim May]

Thanks Dave,
Must go to work, for now.
I need to further de-confuse my thinking on this later tonight;
thanks for your help.
Jim

It sounded more like he was describing how he would use the cursor keys to
move
down 6 rows after he found his cells.

Phil will have to share his real question.