To apply the vlookup formula in a column using VBA Excel.
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.
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.
We can see in the image below that vlookup formula is dragged down to last populated row by fill down method.
- We have used dollar sign in the variable “LookupRange” to fix it, as we shall drag this formula down.
- Formula can be seen in the formula bar.
Post you may like