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.

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Jim Robinson" wrote in message ...
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.

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


"Jim Robinson" wrote:

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.

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

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

--
Regards,
Tom Ogilvy

"Jim Robinson" wrote in message
...
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.

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






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


"Jim Robinson" wrote:

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.

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
--


Gary


"Jim Robinson" wrote in message
...
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.

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

Thanks Ron. It worked, with a small modification.

"Ron de Bruin" wrote:

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Jim Robinson" wrote in message ...
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.