Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find String in Cell then Paste Sum in Offset Cell | Excel Programming | |||
Find word then use offset to change value in another cell | Excel Programming | |||
Find, Copy offset to offset on other sheet, Run-time 1004. | Excel Programming | |||
How to Select a relative range with Using "Find" and Offset() | Excel Discussion (Misc queries) | |||
vba help pls - find min based on a condition and return val of an offset cell | Excel Programming |