Auto filling formula VBA

0
votes

Looking for some help with a VBA function

I have data on two sheets I need to perform an index match on.

The data size will vary every time the compare is run.

I have coded the VBA to call the data and populate both sheets but running the comparison is causing a problem.

I have created the below function, its running without error but not populating the formula in cell starting J2 to end of the J range.

Sub FormulaFill()

Dim strFormulas(1 To 1) As Variant

With ThisWorkbook.Sheets("Export Worksheet")
    strFormulas(1) = "=INDEX('sheet1'!E:E,MATCH('Export Worksheet'!A2,'sheet1'!A:A,0))"

    .Range("J:J").FillDown
End With

End Sub

Any help would be greatly appreciated.

W

Image after updated code applied

enter image description here

2
vbaexcel

2 Answers

1
votes

You were writing the formula to an array variable, not a cell, then you tried to fill the entire column by using J:J. This means it was trying to fill the entire column with the contents of cell J1, the top cell, not J2.

Here is the code with corrections.

Sub FormulaFill()

    With ThisWorkbook.Sheets("Export Worksheet")
        .Cells(2, 10).Formula = "=INDEX('sheet1'!E:E,MATCH('Export Worksheet'!A2,'sheet1'!A:A,0))"
        .Range(.Cells(2, 10), .Cells(.Cells(.Rows.Count, 9).End(xlUp).Row, 10)).FillDown
    End With

End Sub

The .Cells(.Rows.Count, 9).End(XlUp).Row determines the last filled row of column 9 (I) and the code uses that number in the range to use for the autofill of column 10 (J)

0
votes

It's because you're not filling the cell with the formula. 

Sub FormulaFill()

Dim strFormulas(1 To 1) As Variant

With ThisWorkbook.Sheets("Export Worksheet")

strFormulas(1) = "=INDEX('sheet1'!E:E,MATCH('Export      Worksheet'!A2,'sheet1'!A:A,0))"
.Range("J1").Forumla = strFormulas(1) 

.Range("J:J").FillDown
End With

End Sub