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
 
Ad

Advertisements

D

Don Guillett

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
 
Ad

Advertisements

C

Chip Pearson

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top