VBA: Select Cell using find function and offset
 

I want a macro to find a value in a range and select the cell that matches
the find criteria, after the cell is selected I need it to offset down 20
rows.

Range("M3:IV3").Select
Selection.Find(What:="Rad", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
********Select Found Cell************
********Offset (Down) 20 Rows**************

Thanks in advance

Select Cell using find function and offset
 

Sub offsetfind()
Range("M3:IV3").Find(What:="Rad", LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False).Offset(20).Activate
End Sub

But selections are not necessary
Sub offsetfindandplacevalue()
Range("M3:IV3").Find(What:="Rad", LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False).Offset(20).value=12
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"CM4@FL" wrote in message
...
I want a macro to find a value in a range and select the cell that matches
the find criteria, after the cell is selected I need it to offset down 20
rows.

Range("M3:IV3").Select
Selection.Find(What:="Rad", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
********Select Found Cell************
********Offset (Down) 20 Rows**************

Thanks in advance

VBA: Select Cell using find function and offset
 

It isn't clear whether you want to select all the cells from the found
cell downward for 20 rows, or whether you want to select only the cell
that is 20 rows down. Try

Sub AAA()
Dim RR As Range
Dim FoundCell As Range
Set RR = Range("M3:IV3")
With RR
Set FoundCell = .Find(what:="red", _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
lookat:=xlPart, _
searchdirection:=xlNext, _
MatchCase:=False)
End With
If FoundCell Is Nothing Then
MsgBox "Not Found"
Else
' select cells from FoundCell down for 20 rows
FoundCell.Resize(20, 1).Select
' select only the cell 20 rows down from FoundCell
FoundCell(20, 1).Select
End If
End Sub

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)



On Fri, 24 Apr 2009 15:50:01 -0700, CM4@FL
wrote:

I want a macro to find a value in a range and select the cell that matches
the find criteria, after the cell is selected I need it to offset down 20
rows.

Range("M3:IV3").Select
Selection.Find(What:="Rad", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
********Select Found Cell************
********Offset (Down) 20 Rows**************

Thanks in advance