[Solved] VBA Excel: How can I use VLookup and IF in VBA?

by


try to use this code:
First way (you can use user defined function):

Function getSomeData(E3 As Range, Table5 As Range, F26 As Range)      
   getSomeData = ""

   If WorksheetFunction.VLookup(E3, Table5, 2, 0) >= F26 Then
      getSomeData= WorksheetFunction.VLookup(E3, Table5, 4, 0) * F26
   End If
End Function

Than you can call it in any cell just typing =getSomeData(E3;Tabel5;F26) in it. Advantages of this approach is that when you change data in related cells (E3, F26 or Table5), function will recalculate the value in cell.

Second way:

Sub getSomeDataSub()    
   Dim E3 As Range, F26 As Range, Table5 As Range

   Range("F27") = ""
   Set E3 = Range("E3")
   Set Table5 = Range("Table5")
   Set F26 = Range("F26")
   If WorksheetFunction.VLookup(E3, Table5, 2, 0) >= F26 Then
      Range("F27")= WorksheetFunction.VLookup(E3, Table5, 4, 0) * F26
   End If
End Sub

you can call this macros from the ribbon and it will put some data in F27 cell. In this approach you should call this macro each time you change values in related cells to recalculate it in F27.

7

solved VBA Excel: How can I use VLookup and IF in VBA?