'Range' of object '_Global' failed

[ElsiE93]

Hi guys,

I'm having a little difficulty in tweaking some vba that I have got from a recorded macro. When I run it, I get the message 'Run-time error 1004, Method Range of Object_Global failed'.

Once I click Debug, the VBA sheet opens. When I close it the macro has done what I wanted it to do without having to change any of the coding. I need the coding to be dynamic and apply to a variable last row.

VBA Coding is below...

I would really appreciate your help with this

Sub Location_Overs_Vlookup()
'
' Location_Overs_Vlookup Macro
'
Dim Lastrow As Long

'
Columns("H:H").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("H1").Value = "Location"
Range("H2", Range("G" & Rows.Count).End(xlUp).Offset(, 1)).FormulaR1C1 = "=VLOOKUP(RC[-2],Overs!C[-7]:C[-2],5,FALSE)"
With Range("$A$1:$Z$1")
Range("H2").AutoFill Destination:=Range("H2:H" & Lastrow)

End With

End Sub

 

[Oaktree]

The short answer to your question is that you're getting that error because you've declared Lastrow as a variable, but you haven't assigned it a value.

As such, when your code gets to

Range("H2").AutoFill Destination:=Range("H2:H" & Lastrow)

it bombs out because there's no value associated with Lastrow and it thus can't find range H2:H[missing].

... the longer answer to your question is that you can remove the

With Range("$A$1:$Z$1")
Range("H2").AutoFill Destination:=Range("H2:H" & Lastrow)

End With

part of your code and you wouldn't need the Lastrow declaration anyway.

You have a With statement, but you're not doing anything with the range within the with statement... and, you're already assigning a formula to H2:H[end of range] in the row above your with statement. No need to redundantly fill down.

 

[ElsiE93]

Hi Oaktree, thank you for your help! It has worked. I'm still a VBA novice but you've pointed me in the right direction

Elsie