Help with VBA to move cursor N columns

[randyharris]

Hi,

I would appreciate a little VBA help.

In my workbook, on the Menu worksheet in Cell D20 will be a number between 1 and 12.

I am making active Cell G9 on the Trended Data worksheet, and what I am trying to do is move the selected cell to the right by the same number as indicated in cell D20 on the Menu worksheet.

So if for example when G9 is selected on Trended Data, let say D20 on Menu = 7, I'd like to move my selected cell on Trended Data to the right 7 columns which is N9.

Thanks,

Randy

 

[pgc01]

Hi Randy

EDIT: Sorry, wrong interpretation

PGC

 

[HalfAce]

Hello randyharris,
You mentioned making a cell in column G the active cell so this code limits the action
to only work when column G of the Trended Data sheet is selected.
That can be changed if needed.
I think this in the sheet code module of your Tranded Data sheet will do what
you've asked.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target, Columns("G:G")) Is Nothing Then Exit Sub
Dim ThisMany As Integer
ThisMany = Sheets("Menu").Range("D20").Value
ActiveCell.Offset(, ThisMany).Select
End Sub

 

[Datsmart]

To have the cursor move each time you select G9, you need an event macro.

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    Dim VRange As Range, cell As Range
    Dim Msg As String
    Set VRange = Sheets("Trending Data").Range("G9")
    For Each cell In Target
        If Union(cell, VRange).Address = VRange.Address Then
            With cell
                [g9].Offset(, Sheets("Menu").Range("D20").Value).Select
            'or Call Macro
            End With
        End If
    Next cell
End Sub

Paste this code into the Sheet Module for "Trending Data".

 

[randyharris]

PGC reworked his code a little for me, and it works exactly as I had hoped.

Thanks to everyone.

Sub test()

Sheets("Trended Data").Select
Range("G9").Select

ActiveCell.Offset(, Worksheets("Menu").Range("D20").Value).Select
ActiveCell.FormulaR1C1 = "test"

End Sub

 

[tusharm]

PGC reworked his code a little for me, and it works exactly as I had hoped.

Thanks to everyone.

Sub test()

Sheets("Trended Data").Select
Range("G9").Select

ActiveCell.Offset(, Worksheets("Menu").Range("D20").Value).Select
ActiveCell.FormulaR1C1 = "test"

End Sub

It is almost never necessary to select/activate objects.

    Worksheets("trended data") _
        .Cells(9, Range("g:g").Column _
            + Worksheets("menu").Range("d20").Value).Value = "test"