Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|