CODEWITHSUNDEEP
vbaexcel
Mikkel Astrup
Mikkel Astrup

Reputation: 415

Button not running all VBA

I have a VBA that im running using a button on a seperate sheet. However, for some reason the VBA does not work when I click the button, when I run the VBA using the VBA application, the VBA runs with no problem. The VBA Im running looks like this. 

Sub Fillit()

Worksheets("Sheet3").Range("B3").FormulaLocal = "=INDEKS(Sheet1!$N:$N;SAMMENLIGN(Sheet3!$A:$A&Sheet3!B$1;Sheet1!$R:$R;0))"

LastCol = Worksheets("Sheet3").Cells(1, Columns.Count).End(xlToLeft).Column
lastRow = Worksheets("Sheet3").Cells(Rows.Count, "A").End(xlUp).Row
Worksheets("Sheet3").Range("B3").AutoFill Destination:=Worksheets("Sheet3").Range("B3:B" & lastRow), Type:=xlFillDefault 'fill C column
Worksheets("Sheet3").Range("B3:B" & lastRow).AutoFill Destination:=Worksheets("Sheet3").Range(Cells(3, "B"), Cells(lastRow, LastCol)), Type:=xlFillDefault 'fill all rows

End Sub

When i use the button it only runs the first line of the VBA,

EDIT: I have updated the VBA where i define the sheets. Now the debugger tells me that there is an error in the last line runtime 1004

Upvotes: 0

Views: 52

Answers (1)

user6432984
user6432984

Reputation:

The issue probably has to due with not qualifying your range references.

I agree with @Cyril, there is no reason to use autofill. Simply define your range and then assign the formula to it.
Note: defining a Target range isn't necessary but it make debugging a lot easier.

With Worksheets("Sheet3")
    Set Target = .Range(.Cells(1, Columns.Count).End(xlToLeft), Cells(Rows.Count, "A").End(xlUp))
    Target.FormulaLocal = "=INDEKS(Sheet1!$N:$N;MATCH(Sheet3!$A:$A&Sheet3!B$1;Sheet1!$R:$R;0))"
End With

The OP's formula is using a semicolons to separate values below is the US version of the formula.

'Target.FormulaLocal = "=INDEKS(Sheet1!$N:$N,MATCH(Sheet3!$A:$A&Sheet3!B$1,Sheet1!$R:$R,0))"

Upvotes: 0

Related Questions