Macro - Find a value and then move down
 

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 ?

--
http://www.redbrick.dcu.ie/~pele

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



Phil Osman wrote:

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 ?

--
http://www.redbrick.dcu.ie/~pele

--

Dave Peterson

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" wrote in message
...
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



Phil Osman wrote:

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 ?

--
http://www.redbrick.dcu.ie/~pele

--

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.

Jim May wrote:

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" wrote in message
...
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



Phil Osman wrote:

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 ?

--
http://www.redbrick.dcu.ie/~pele

--

Dave Peterson

--

Dave Peterson

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


"Dave Peterson" wrote in message
...
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.

Jim May wrote:

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" wrote in message
...
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



Phil Osman wrote:

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 ?

--
http://www.redbrick.dcu.ie/~pele

--

Dave Peterson

--

Dave Peterson