Apply vlookup formula in VBA Excel – debugvba.com

vlookup function vba excel

Objective

To apply the vlookup formula in a column using VBA Excel.

Approach

I have written this post with the presumption that you are familiar with implementation of vlookup formula in excel. To demonstrate how to apply vlookup formula we have got the sample data in column A, C and D.

As we can see in the image below that we have column SID which contains State ID in cell A2 to A6. These are lookup values, which means we shall look for these values in table array. Another cell range where we shall find the data is C2:D6. This range (i.e. C2:D6) is called as table array. SID is the common column in both the ranges, it will be used for lookup.

Code

We can see in the code below, first we have applied the vlookup formula in cell F2. Then we have dragged the formula to last populated row, which is 6th row. We have used FillDown method of Range object.

Sub vLookUpFormula()

Dim LookUpValue As String
Dim LookUpRange As String

LookUpRange = "C$2:D$6"
LookUpValue = "A2"

'Find last populated row
LastPopulatedRow = Range("A" & Rows.Count).End(xlUp).Row
 
'Apply the formula in first cell
Range("F2").Formula = "=vlookup(" & LookUpValue & "," & LookUpRange & " ,2,0)"
 
'Drag the formula down to last populated row
Range("F2: " & "F" & LastPopulatedRow).FillDown

End Sub

 

All the values from column A were found in column C, except SID 13. Due to this reason we can see the error #N/A in cell F5.

 

vlookup function vba excel

 

We can see in the image below that vlookup formula is dragged down to last populated row by fill down method.

 

vlookup function vba excel

 

Note

  1. We have used dollar sign in the variable “LookupRange” to fix it, as we shall drag this formula down.
  2.  Formula can be seen in the formula bar.


Reference

Count files in a folder using VBA Excel