CODEWITHSUNDEEP
vbaexcelexcel-formula
Jonathan
Jonathan

Reputation: 45

Autofilling Formulas

So I'm trying to get excel to autofill a formula into column F that compares values in column A (both columns F and A are in the sheet labeled "PMG") with the values in column A in another sheet ("Sheet1"). Here is what I have:

   ThisWorkbook.Sheets("PMG").Range("F2").Formula = "=VLOOKUP(A2,Sheet1!A:A,1,FALSE)"
   ThisWorkbook.Sheets("PMG").Range("F2", "F" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown

This code was originally found here: https://www.mrexcel.com/forum/excel-questions/812384-vba-macro-applies-formula-down-column-until-last-row.html

The problem I'm having is the autofill is only going down as far as the cells are populated in the "Sheet1" A column, instead of as far as the cells are populated in the "PMG" A column. Because of this, instead of 180 rows being auto filled with the formula, only about 80 are being filled.

Thanks for your help!

Upvotes: 0

Views: 69

Answers (1)

QHarr
QHarr

Reputation: 84465

Try wrapping in a With block to ensure working with the right sheet at all times.

Option Explicit
Public Sub test()
    With ThisWorkbook.Sheets("PMG")
        .Range("F2").Formula = "=VLOOKUP(A2,Sheet1!A:A,1,FALSE)"
        .Range("F2:F" & .Cells(Rows.Count, 1).End(xlUp).Row).FillDown
    End With
End Sub

You can also use R1C1 notation rather than fill down.

Option Explicit
Public Sub test()
    With ThisWorkbook.Sheets("PMG")
         .Range("F2:F" & .Cells(Rows.Count, 1).End(xlUp).Row).FormulaR1C1 = "=VLOOKUP(RC[-5],Sheet1!C[-5],1,FALSE)"
    End With
End Sub

Upvotes: 0

Related Questions