1

I am trying to make a macro that will run a vlookup and then autofill down to the last adjacent cell that contains data. When I originally created the macro the dataset contained 1917 lines of data which is why you will continue to see it reference 1917. However, the dataset varies in size daily.

This particular macro takes a couple of steps before getting to this point:

  1. Converts text to columns
  2. Deletes an unnecessary column that is provided from a supporting report
  3. Adds titles to 5 columns
  4. Resizes all of the columns to fix the data
  5. Then it runs the first vlookup which I need the data to fill down to the last adjacent cell to the left
  6. Then it runs another vlookup which I need to also fill down to the last adjacent cell to the left

Here is the code for the two vlookup I am struggling with:

Range("E2").Select
ActiveCell.FormulaR1C1 = _
    "=VLOOKUP(RC[-4],'CURRENT DAY'!R[-1]C[-4]:R[2498]C[1],5,0)"
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-4],'CURRENT DAY'!R1C1:R2500C6,5,0)"
Range("E2").Select
Selection.AutoFill Destination:=Range("E2:E1917")
Range("E2:E1917").Select
Columns("E:E").EntireColumn.AutoFit
Range("F2").Select
ActiveCell.FormulaR1C1 = _
    "=VLOOKUP(RC[-5],'CURRENT DAY'!R[-1]C[-5]:R[2498]C,6,0)"
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-5],'CURRENT DAY'!R1C1:R2500C6,6,0)"
Range("F2").Select
Selection.AutoFill Destination:=Range("F2:F1917")
Range("F2:F1917").Select
RStoltz
  • 11
  • 1
  • 2
  • It appears that you are inserting one formula then immediately replacing that formula with another, that is not looking into the same area. Look at the two lines after selecting the "E2" Cell. – Scott Craner Sep 25 '15 at 15:58
  • 1
    So, you want to exchange 1917 with the actual last row number? In that case you might want to have a look at this question: http://stackoverflow.com/questions/71180/how-can-i-find-last-row-that-contains-data-in-the-excel-sheet-with-a-macro – Ralph Sep 25 '15 at 16:30
  • Hi Scott, thank you for taking a look at this. They are two different VLookUps. It runs a vlookup and returns one piece of data then I need the macro to fill the column in with the formula down to the last cell that has data adjacent to it. Then it needs to perform the same action but returning a different piece of data. – RStoltz Sep 25 '15 at 16:59
  • I am not having an issue with the vlookup itself. I just cannot get the macro to cascade the formula down to the last cell in that column that has data adjacent to it – RStoltz Sep 25 '15 at 17:07
  • Helo Ralph, thank you for taking a look at this. I believe your analysis to be correct. However, I am unsure on how to implement this change without breaking the rest of the coding. – RStoltz Sep 25 '15 at 17:10

1 Answers1

1

There is not need to autofill. Just find the last row in column A and use that to denote the range in which to put the r1c1 formula

Dim lastrow As Long
lastrow = Range("A2").End(xlDown).Row

Range("E2:E" & lastrow).FormulaR1C1 = "=VLOOKUP(RC[-4],'CURRENT DAY'!R1C1:R2500C6,5,0)"

Range("F2:F" & lastrow).FormulaR1C1 = "=VLOOKUP(RC[-5],'CURRENT DAY'!R1C1:R2500C6,6,0)"

Columns("E:F").EntireColumn.AutoFit
Scott Craner
  • 148,073
  • 10
  • 49
  • 81