Cleaning up VLOOKUP / INDEX(MATCH()) VBA

0
votes

I recorded the following macro: 

ActiveCell.FormulaR1C1 = _
            "=INDEX(Sheet1!R2C2:R491C2,MATCH(Sheet2!RC[-2],Sheet1!R2C1:R491C1,0))"
        Range("D2").End (xlToLeft)
        Range("C2").End (xlDown)
        Range("D882").Select
        Range(Selection, Selection.End(xlUp)).Select
        Selection.FillDown

which, as some of you may be able to tell, is the result not just of recording but of trying to edit the 'select' statements out of said recording. However, VBA tells me that I am using the .End property incorrectly. What is the best way to edit this so that select is not used, and so that the end of the range depends solely on the value in column A being filled for each row?

The structure for a more general macro would be:

1) Select cell D2 in Sheet(1) (the cell under Header_1)
2) Write an INDEX(MATCH()) formula here referencing some specific columns in Sheet(1) and Sheet(2)
3) Select D2 in Sheet(2) and copy down to Dn where n is the first row where An = "".

Then I would simply repeat this for the other two INDEX(MATCH()) formulas needed.

UPDATE:

Is there also a way to have INDEX(MATCH()) set up so that the column range continues as long as cells are not blank (e.g. R2C1:Rnonblank). More specifically, can I just use the same & Cells() method? These ranges will be longer or shorter depending on the workbook being targeted as well.

2
vbaexcel

2 Answers

1
votes

On the assumption this formula goes in column D from row 2 down as far as there are entries in column A, and needs to use all cells on Sheet1 column B:

Dim LR as long
lr = sheets("Sheet1").Cells(Rows.Count, "B").End(xlUp).row
Range("D2:D" & Cells(Rows.Count, "A").End(xlUp).Row).FormulaR1C1 = _
                "=INDEX(Sheet1!R2C2:R" & lr & "C2,MATCH(RC[-2],Sheet1!R2C1:R" & lr & "C1,0))"
1
votes

Short Answer You don't need these lines, they don't do anything. They got recorded when you were navigating to the bottom of your data using the keyboard.

Long Answer The .End property returns a Range object, but your macro isn't doing anything on those lines. Instead of doing all of this selecting, decide what range you want and call .FillDown on that range. The easiest is to have a fixed range: Range("D2:D822").Filldown but you can make it smarter than that, if you'd like.

Try describing, in detail, how you would decide what cells to pulldown and we can help you translate it into code.