Method Range of Object'_Global Failed Error Message

[spyldbrat]

Hello, I am getting the above error message on the code below. The debugger is showing me the second to last line is where the issue is ( Range("b9:b" & UsdRws).FillDown) but I can't figuire out what is wrong.

Sub FB()

Rows("7:7").Select
Selection.AutoFilter
Columns("B:B").Select
Selection.Insert Shift:=xlToRight
Range("B7").Select
ActiveCell.FormulaR1C1 = "Finance Book"
Range("B9").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(VLOOKUP(RC[1],Ad_Hoc!C[-1]:C,2,FALSE),"" "")"
Dim Last_Row As Long
Range("b9:b" & UsdRws).FillDown

End Sub

 

[Fluff]

You haven't given UsdRws as value, therefore you are trying to fill down from B9 to B0

 

[spyldbrat]

What value do I have to give it?

 

[Fluff]

Do you want to copy the formula down to the last row of data?

 

[spyldbrat]

Yes

 

[Fluff]

Try

Sub FB()
Dim UsdRws As Long
UsdRws = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row
Rows("7:7").Select
Selection.AutoFilter
Columns("B:B").Select
Selection.Insert Shift:=xlToRight
Range("B7").Select
ActiveCell.FormulaR1C1 = "Finance Book"
Range("B9").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(VLOOKUP(RC[1],Ad_Hoc!C[-1]:C,2,FALSE),"" "")"
Range("b9:b" & UsdRws).FillDown

End Sub

 

[spyldbrat]

Perfect!

Thank you!

 

[Fluff]

Glad to help & thanks for the feedback