VBA Range.Offsets must not go beyond set cell range

[saesa]

How can I make perform this macro in given range A1:G5 for example? If macro is supposed to go beyond range with offsets, it has to stay on the last cell within the range.

Thanks.

 

[GWteB]

Which macro are you referring to?

 

[saesa]

Function RShift()
Selection.Offset(0, 2).Value2 = Selection.Value2
Selection.Value2 = "Moved"
Selection.Offset(0, 2).Select

I want to move cell with right key until i hit the border of selection.

 

[GWteB]

You can't link a macro to the right arrow key, just CTRL-? key combinations.
Can you provide a little bit more about your final goal?

 

[saesa]

OK, there you go. I did link a macro to just right arrow. When I press right arrow, selected cell moves to right side.

bandicam 2020-01-18 05-05-17-184 - Streamable

Check out this video on Streamable using your phone, tablet or desktop.

streamable.com

I want the macro to not exceed boundaries of A300:G310 with offsets.

 

[GWteB]

Presumably you're using the Worksheet_SelectionChange Event, and when the cursor moves, the value2 of the previous chosen cell is changed ("Moved"). Am i right?

 

[saesa]

No, just this:
Application.OnKey "{LEFT}", "LShift"
Application.OnKey "{RIGHT}", "RShift"
Application.OnKey "{UP}", "UShift"
Application.OnKey "{DOWN}", "DShift"
Application.OnKey "{NUMLOCK}", "TabShift"
Application.OnKey "{ENTER}", "EnterShift"


So any ideas?

 

[mikerickson]

Dim dColumn as Range
With Selection
    If .Row <=5 And .Column <=6 Then
        Select Case .Column
            Case Is <=5
                dColumn = .Column + 2
            Case Is = 6
                dColumn = 7
        End Select
        .EntireRow.Cells(1, dColumn).Value = .Value
        .Value = "moved"
        .EntireRow.Cells(1, dColumn).Select
    End If
End With

 

[saesa]

Dim dColumn as Range
[B]With Selection[/B]
  ...

Thanks, but gives this.

 

[MARK858]

Sub yoursubname()
' code Mike posted
End Sub