Error 9: Subscript Out of Range

[billyheanue]

Whats good homies,

I am a excel noob and I get this error 9 when I try to run this. Since it doesnt specify what line is actually wrong, Im wondering if any of the VB Guru's can point me in the right direction in terms of whats wrong with this code? Very confused!

Private Sub CommandButton8_Click()


Dim x As Integer
Dim y As Integer
Dim colindexval As Double
Dim resizeval As Double


resizeval = Sheet1.Cells(19, 12).Value
colindexval = Sheet1.Cells(16, 12).Value
Sheet9.Range(x, y).Resize(resizeval, 1).Formula = "=VLOOKUP(Sheet1!B16,Sheet2!" _
    & Sheets("Sheet2").Range("A12").CurrentRegion.Address & ",Sheet1!L$29,FALSE)"
    
End Sub

 

[MARK858]

Counting from the left what number tab would the 2 sheets numbers be?

 

[dcunningham]

Technician Report Summary would be Sheet14 and Summary Print would be Sheet15.

 

[MARK858]

Does the code below work? if yes copy and paste the sheet names it puts in the immediate window into the code I previously posted.
If you can't see the immediate window then press Ctrl+G. (and yes I do hate typing code on a phone)

Sub Testit()
Sheets(14).Range("G2:G8561")
    .Copy Sheets(15).Range("C10")
End With
Debug.Print Sheets(14).Name
Debug.Print Sheets(15).Name
End Sub

 

[dcunningham]

I get a compile error when I try to run that code. It says "Invalid or unqualified reference". It highlights the .Copy in the second line.

 

[Rick Rothstein]

Does the code below work? if yes copy and paste the sheet names it puts in the immediate window into the code I previously posted.
If you can't see the immediate window then press Ctrl+G. (and yes I do hate typing code on a phone)

Sub Testit()
Sheets(14).Range("G2:G8561")
    .Copy Sheets(15).Range("C10")
End With
Debug.Print [B][COLOR="#FF0000"]"< &"[/COLOR][/B] Sheets(14).Name [B][COLOR="#FF0000"]"& >"[/COLOR][/B]
Debug.Print [B][COLOR="#FF0000"]"< &"[/COLOR][/B] Sheets(15).Name [B][COLOR="#FF0000"]"& >"[/COLOR][/B]
End Sub

I would add what I show in red above so you can see if there are any leading or trailing "invisible" characters.

 

[Rick Rothstein]

I would add what I show in red above so you can see if there are any leading or trailing "invisible" characters.

Try this modification of Mark's code...

Sub Testit()
  Sheets(14).Range("G2:G8561").Copy Sheets(15).Range("C10")
  End With
  Debug.Print "< &"; Sheets(14).Name; "& >"
  Debug.Print "< &"; Sheets(15).Name; "& >"
End Sub

 

[MARK858]

I would add what I show in red above so you can see if there are any leading or trailing "invisible" characters.

Wonderful plan

 

[dcunningham]

I get a Compile Error: "End With without With". Is that good or bad?

 

[MARK858]

Delete the End With in post #16

 

[dcunningham]

I get: "Run-time error '1004': Application-defined or object-defined error".

Just want to take this moment to thank the two of you for all the help.