Does Application.Goto Reference:=Index accept variables?

[Kefkolo]

Hi,
I am trying to set up an Index where I can direct my VBA code to copy the correct line to the clipboard so I can paste it elsewhere. When I use a number for the row it works but with a variable it doesn't. (But I need the variable for the For/Next because it will change as the user adds and subtracts).

I'm running Excel 2010 on Win XP. My VBA knowledge is very limited.

The code I am talking about that works is:

 Application.Goto Reference:="INDEX(PipeConCatActive,1)"

The line that doesn't work gives me the error:"Run-time error '1004' The text you entered is not a valid reference or defined name."

 Application.Goto Reference:="INDEX(PipeConCatActive,idxNum)"

I don't understand because as part of my debug I'm messaging out the values and they seem accurate.

My data is in the worksheet GraphicsData (currently in U48:U63 - RangeName: PipeConCatActive)

Here is the full code with the line that works commented out (in dark blue):

[B]Sub CreateTextBox2()[/B]
[I]'[/I]
[I]' CreateTextBox Macro[/I]
[I]'[/I]
[B]Dim varPipeActive As Integer[/B]
[B]Dim Shape1name As String[/B]
[B]Dim idxNum As Integer[/B]
[B]Dim varTextboxLoc As Integer[/B]
[B]Dim varTextboxlocIncr As Integer[/B]


[B]idxNum = 1[/B]
[B]varTextboxLoc = 600[/B]




[COLOR=#40e0d0][I]' *** Used to get the number of rows so I know how many rows to index[/I][/COLOR]
    [B]varPipeActive = Sheets("GraphicsData").Range("V47").Value [/B]  [COLOR=#40e0d0][I]'Get the value[/I][/COLOR]
    [B]MsgBox ("The number of rows is: " & varPipeActive) [/B]         [COLOR=#40e0d0][I]'This is normally commented out. It is just a debug check[/I][/COLOR]
[COLOR=#40e0d0][I]' ***[/I][/COLOR]


    [B]Sheets("GraphicsData").Select[/B]                               [COLOR=#40e0d0][I]'Goto the correct worksheet[/I][/COLOR]
    [B]For idxNum = 1 To varPipeActive[/B]                             [COLOR=#40e0d0][I]'Loop through the commands until you reach the number specified in varPipeActive.[/I][/COLOR]
[B]        MsgBox ("IdxNum is: " & idxNum & " and varPipeAcive is: " & varPipeActive)[/B]
[COLOR=#0000ff][I]        'Application.Goto Reference:="INDEX(PipeConcatActive,1)"[/I][/COLOR]
        [B]Application.Goto Reference:="INDEX(PipeConcatActive,idxNum)"[/B] [COLOR=#40e0d0][I]'goto the row in the range "PipeConcatActive" as specified in IdxNum[/I][/COLOR]
        [B]Selection.Copy[/B]                                              [COLOR=#40e0d0][I]'Copy the row's text to the clipboard[/I][/COLOR]
        [B]Sheets("TimeLinePipe").Select[/B]                               [COLOR=#40e0d0][I]'Switch to the other datasheet where the pipeline is at.[/I][/COLOR]
[B]        ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 3, 0.75, 381, 16.5) _[/B]
[B]        .Select[/B]                                                [COLOR=#40e0d0] [I]'Sets the size of the textbox[/I][/COLOR]
        [B]ActiveSheet.Paste[/B]                                   [COLOR=#40e0d0][I]'Paste the text into the textbox[/I][/COLOR]
            [B]With Selection.ShapeRange.Fill[/B]                  [COLOR=#40e0d0][I]'I still need to work in this section. I need to set the color depending on the status[/I][/COLOR]
[B]                    .Visible = msoTrue[/B]
[B]                    .ForeColor.RGB = RGB(202, 217, 236)[/B]
[B]                    .Transparency = 0[/B]
[B]                    .Solid[/B]
[B]            End With[/B]

[B]            Selection.Left = 1[/B]
[B]            Selection.Top = varTextboxLoc[/B]
[B]            Range("a1").Select[/B]
[B]            varTextboxLoc = varTextboxLoc + 20[/B]
[B]    Next idxNum[/B]


[B] End Sub[/B]

Thank you for any help you can provide. I'm sure my code is clumsy, but it's the best I could figure out from looking at other code and from what logic I understand. At least this way even if it's not the fastest or most efficient, I have a better understanding of what is going on.
FYI, besides an answer to my problem, I would love any beginner reference to just the syntax of VBA. I come up with syntax I think should work but then that's not the way. A good example is

Selection.Copy

works to copy to the clipboard but

Selection.Paste

does not. I have to use

 ActiveSheet.Paste

and i don't understand why. Seems like there would be something written that would help better than the Microsoft reference. I keep thinking if I just understood the rules of VBA syntax it would go a long way to being able to use the program.
Best,
Kefkolo

 

[Kefkolo]

Ignore this post. I see several errors that I would need to correct first. Can someone tell be how I can delete my own post?