CODEWITHSUNDEEP
vbaexcel
camelCaseCowboy
camelCaseCowboy

Reputation: 986

Autofill formulas not dynamic

I'm trying to autofill down some formulas, but instead of updating the cell reference when copying down, it keeps the same reference. This is happening for copying down both the VLookup and If(and) statements. The relevant part of the code is below, the part in question below the comments 'Now let's get the 2014 Data and below.

    Sub sidebyside()
'
' test Macro
'

'
Dim lastRow2014 As Long

ActiveSheet.Range("$A$10:$V$4388").AutoFilter Field:=11, Criteria1:="1"
    lastRow2014 = Range("A" & Rows.Count).End(xlUp).Row


  ' Get side-by-side data for comparison
  Range("E10").Value = "Full Name"
  Range("Y10").Value = "Full name"
  Range("Z10").Value = "2014 Data"
  Range("AA10").Value = "2016 Data"


    Range("Y11").Select
    Range("Y11").Formula = "=E11"

  'Copy this down to the lastrow of the lefthand side (lastRow2014)
    Range("Y11").AutoFill Destination:=Range("Y11", "Y" & lastRow2014)

    'Now let's get the 2014 data
    'Range("I11: I" & lastRow2014).Copy Destination:=Range("Z11")
    Range("Z11").FormulaR1C1 = "=VLOOKUP(Y11,E: I, 5, FALSE)"
    Range("Z11").AutoFill Destination:=Range("Z11", "Z" & lastRow2014)


    'Let's get the corresponding 2016 data.
        'VLOOKUP!
    Range("AA11").FormulaR1C1 = "=VLOOKUP(Y11,P:T,5,FALSE)"
    Range("AA11").Select
    'Selection.AutoFill Destination:=Range("AA3:AA" & Range(
   Selection.AutoFill Destination:=Range("AA11:AA" & Range("T" & Rows.Count).End(xlUp).Row)


    'Now, let's calculate the percentage.  The trick will be the reference.
    Range("AA4").FormulaR1C1 = "='Summary 2'!M3" 'This is the index from the summary tab
Range("AA10").Value = "Within?"
Range("AA11").Formula = "=IF(AND(Y11 <=(Z11 + ($AA$4 * Z11)), Y11 >= (Z11 - (Z11 * $AA$4))), ""WITHIN"", ""NOT"")"
Range("AA11").Select
Selection.FillDown

Upvotes: 0

Views: 91

Answers (1)

S. Cannon
S. Cannon

Reputation: 69

Instead of populating the first formula and filling it down, populate all formulas at once:

For example:

Range("Y11:Y" & lastRow2014).Formula = "=E11"

instead of:

Range("Y11").Select
Range("Y11").Formula = "=E11"

'Copy this down to the lastrow of the lefthand side (lastRow2014)
Range("Y11").AutoFill Destination:=Range("Y11", "Y" & lastRow2014)

Sticking to this, you can modify your Z equation with:

Range("Z11:Z" & lastRow2014).Formula = "=VLOOKUP(Y11,E:I, 5, FALSE)"

The issue you are running into is the extra space in your VLOOKUP. "E: I" should be "E:I".

Upvotes: 1

Related Questions