Help with VBA to move cursor N columns

randyharris

Board Regular
Joined
Oct 6, 2003
Messages
82
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
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,453
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.
Code:
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

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
To have the cursor move each time you select G9, you need an event macro.
Code:
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

Board Regular
Joined
Oct 6, 2003
Messages
82
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

MrExcel MVP
Joined
May 28, 2002
Messages
11,023
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.
Code:
    Worksheets("trended data") _
        .Cells(9, Range("g:g").Column _
            + Worksheets("menu").Range("d20").Value).Value = "test"
 

Watch MrExcel Video

Forum statistics

Threads
1,113,955
Messages
5,545,167
Members
410,667
Latest member
Gaexel
Top