Vba: vlookup for entire column

1
votes

I have column B with cost centre codes and column D with department. i need to do a vlookup to fill in the department name of the relevant cost centre code.

The current code is like this: Dim MyStringVar1= Application.Vlookup(Range("B7"),_ Worksheets("VLOOKUP Table").Range("A2:B1000"),2,True)

However, i dont just want to do vlookup for D7 only but rather the entire column. My range is not fixed(there may be 100 or 200rows in Column B,depending on the number of projects.)

How do I apply the formula for the entire column? Instead of D7 only

3
vba

3 Answers

2
votes

let's say you want to add formula =VLOOKUP(B2,'VLOOKUP Table'!A$2:B$1000,2,TRUE) to range D3:D10, where 'B2' will increment after every row, then the code would be :

Range("D3:D10").Formula= "=VLOOKUP(B2,'VLOOKUP Table'!A$2:B$1000,2,TRUE)"
1
votes
Dim This As Worksheet

Set This = ThisWorkbook.Sheets(1)

This.Activate
    This.Range("D7", Range("A2").End(xlDown).Offset(0, 3)).Formula = "=VLOOKUP(B2,[INSERT SHEET]!$A:$D,2,FALSE)"

If the data is in sheet2, you just insert sheet2 in the [INSERT SHEET]. It doesn't matter if the name is sheet2 or named something else, just call it sheet2

0
votes

You could use relative references in an R1C1 formula, then just paste it to your destination range, you'd need to find the destination range first, perhaps using something like .end(xldown).row

See this for reference on the general idea:

http://macromatician.blogspot.co.uk/2013/02/how-to-add-formula-to-worksheet-range.html