macro equivalent to arrow keys

[Jim Robinson]

What are the macro equivalent commands corresponding to the arrow keys (up
down left right) and to the "end" key?
Right now if I do the "record keystrokes" command for end up to take me up
to the last row with data, the recorded macro takes me to a particular cell.
I want it to simply do the equivalent of end up no matter where end up might
take me.

Here is what I am actually trying to do.

Dim rng As Range, tvalue
tvalue = WorksheetFunction.Max(Range("C13:C20"))
Set rng = Cells.Find(tvalue)

I find the max value (tvalue) in a range, then I set rng to be the cell
where that value is located. Now I want to go to that cell and copy tvalue
and the data in the cell to the right of tvalue to another location.

I have no problem determining tvalue or determining the cell location of
tvalue. Beyond that, I am stumped.

 

[Ron de Bruin]

Hi

Try this example that copy it to C22

SubTest()
Dim FindString As String
Dim Rng As Range
FindString = Application.WorksheetFunction.Max(Range("C13:C20"))
Set Rng = Range("C13:C20").Find(What:=FindString, _
After:=Range("C20"), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not Rng Is Nothing Then
Rng.Resize(1, 2).Copy Range("C22")
Else
MsgBox "Nothing found"
End If
End Sub

 

[ben]

--
When you lose your mind, you free your life.

 

[Tom Ogilvy]

set rng1 = Range(rng,rng.End(xltoRight))

rng1.copy Destination:=worksheets(Sheet3").End(xlup).Offset(1,0)

 

[ben]

oops sorry maybe i should put something in here. sorry

are you meaning you're having trouble finding the date to the right of the
cell containg tvalue? like this
nvalue = cells(rng.row,rng.column+1)

then to move the date just decide where to put it and use that variable...
or did i completely miss your point?

also to move to the end of a row of data use the end property eg.....

cells(1,1).end(xlToRight).select

will move you to the last column in the section containing data exactly as
if you had press ctrl + the right arrow key
ben

 

[Gary Keramidas]

this would be something simple to add to your code. i am sure others with a
lot more experience will have shorter answers. but the offfset is what you
use to move like you ask. offset(r,c), where if r is positive, it moves the
cursor down, if it's negative, it moves it up. the same for the c, it moves
right or left


Dim rng As Range, tvalue
tvalue = WorksheetFunction.Max(Range("C13:C20"))
Set rng = Cells.Find(tvalue)
rng.Select
Selection.Offset(0, 1).Select
ActiveCell.Copy
Selection.Offset(0, 3).Select
ActiveSheet.Paste

 

[abcd]

that's a .end(wldown) for example


you can type in
range("A1").end(xldown).select
if you want to select the last cell down in the A column

you also can select all the range by
range( range("A1"), range("A1").end(xldown)).select

this last line is better this way:
With Range("A1")
Range(.Cells(1), .End(xlDown)).Select
end with

because you write and call only once A1

 

[Jim Robinson]

Thanks Ron. It worked, with a small modification.