VBA Application.Goto Macro

[Diaxus]

I have researched this for several hours and attempted every option and fix I could find or think of, and having failed I figure it's time to ask about my specific need.

I have a formula in cell D2 of sheet7("Missions") that first chooses a table from six table options on the same worksheet based on input in cell C2. Next, the formula looks for a line of text based on what is written in cell C3. Finally, I used CONCATENATE to insert "C" before the numeric value that the formula will find, giving me a variable cell reference. The exact formula in cell D2 is:

=CONCATENATE("C",IF(Table76[@[Search Input]]="Lord Harrow",MATCH(C3,Table75[Lord Harrow],0),0))

That much is simply to fill you in on how I am attempting to fulfill my goal. The problem is this. I have created an ActiveX button on the same worksheet (which feeds on VBA code in sheet7) that is intended to go to the cell the above formula depicts. I am trying to use the Application.Goto method. Below is what I have now:

Option Explicit
Private Sub CommandButton1_Click()
Dim L As String
Dim G As Range
Set L = Range("D2").Value
Set G = Range(L)
Application.Goto G, True
End Sub

Naturally, Option Explicit is separated, in case anyone is wondering. I have tried various other codes, such as:

Option Explicit
Private Sub CommandButton1_Click()
Dim r As Integer
Set r = Range("D2").Value
Application.Goto r, True
End Sub

and

Option Explicit
Private Sub CommandButton1_Click()
Dim r As Range
Set r = Range("D2").Value
Application.Goto r, True
End Sub

among many others.

The current error is "Compile Error: Object Required" which highlights the "Set" line. I don't understand, because I thought the object was already set by the "Dim" command.

Any help would be greatly appreciated, as by this point I'm pretty sure I've received at least half of all the possible error codes. Thank you for your time.

 

[Diaxus]

I misspoke. The error highlights the Sub routine itself, and the "L=" in the first "Set" line is selected in blue.

 

[mole999]

if L in D2 is 250 then how does
Set G = Range(L) work as Set G = Range(250) which wouldn't be a range the nearest I can think of is Rows(L:L)

 

[MARK858]

What happens if you remove the Set in front of L?
i.e.

Private Sub CommandButton1_Click()
Dim L As String
Dim G As Range
Set L = Range("D2").Value
Set G = Range(L)
Application.Goto G, True
End Sub

becomes

Private Sub CommandButton1_Click()
Dim L As String
Dim G As Range
L = Range("D2").Value
Set G = Range(L)
Application.Goto G, True
End Sub

 

[Diaxus]

The idea at this point was to make the macro interpret the results of D2 as string, named "L", and then turn L into a range, "G". I'm not sure that is a good idea, or that it is what is actually happening, but the error seems to be almost from the outset, with "Set L".

 

[Diaxus]

Mark, you're a genius! Why did that affect anything?

 

[MARK858]

L is a String not a range and so you don't use Set, Set only applies to objects.

 

[Diaxus]

Hence the "Object Required" error... Thank you, I'll remember that! And thanks to you as well Mole for your time!