CODEWITHSUNDEEP
excelvba
mij nivek
mij nivek

Reputation: 81

How to used vlookup in VBA?

I have a workbook, I need to populate the result in every rows in my Sheet1, C13:c31 and cell H13:H31 by using vlookup. The lookup value is in Sheet1, cell B13:B31. My table array is in Sheet4. I need to get result in Sheet4, column C & D and display the result in Sheet 1, cell c13:c31 and cell H13:h31. (I don't want to display the formula, that is why I want to used VBA instead.

Please check my code below, because it is not working.

Sub Vlookup()
 Dim c As Range
 Dim d As Range

   If Sheet1.Range("B13").Value = "*" Then
   c = Application.WorksheetFunction.Vlookup(Sheet1.Range("B13").Value, Sheet4.Range("A:E"), 3, False)
   d = Application.WorksheetFunction.Vlookup(Sheet1.Range("B13").Value, Sheet4.Range("A:E"), 4, False)
  End If
 If Sheet1.Range("B14").Value = "*" Then
 c = Application.WorksheetFunction.Vlookup(Sheet1.Range("B13").Value, Sheet4.Range("A:E"), 3, False)
    d = Application.WorksheetFunction.Vlookup(Sheet1.Range("B13").Value, Sheet4.Range("A:E"), 4, False)
End If
 If Sheet1.Range("B15").Value = "*" Then
  c = Application.WorksheetFunction.Vlookup(Sheet1.Range("B13").Value, Sheet4.Range("A:E"), 3, False)
  d = Application.WorksheetFunction.Vlookup(Sheet1.Range("B13").Value, Sheet4.Range("A:E"), 4, False)
 End If
End Sub

Upvotes: 0

Views: 68

Answers (2)

Tim Williams
Tim Williams

Reputation: 166126

This should give you some idea of how it can be done:

Sub Vlookup()

    Dim c As Range, v, r1, r2, rngSearch As Range
    
    Set rngSearch = Sheet4.Range("A:E")
    
    For Each c In Sheet1.Range("B13:C31").Cells 'loop the input range
        v = c.Value
        If Len(v) > 0 Then                      'is there anything to look up?
             'drop the `WorksheetFunction` to prevent run-time
             '   error if there's no match
             v1 = Application.Vlookup(v, rngSearch, 3, False)
             v2 = Application.Vlookup(v, rngSearch, 4, False)
             
             'IsError(vx) will be True if no match was found 
             c.EntireRow.Columns("C").Value = IIf(IsError(v1), "-", v1) ' "-" if no match
             c.EntireRow.Columns("C").Value = IIf(IsError(v2), "-", v1)
        End If
    Next c

End Sub

Upvotes: 1

vvuser
vvuser

Reputation: 15

Could be a problem with the if statements.

You can try If IsEmpty(Range("B13").Value) = False Then instead of the wildcard "*".

Upvotes: 0

Related Questions