Worksheetfunction.VLookup

Private Sub CommandButton1_Click()
Dim i As Long, z As Long, s As Long, b As Long
Dim Text As String
Dim rng As Range
Dim variable As Variant
Dim wks_IW73 As Worksheet, wks_ILA As Worksheet
Application.ScreenUpdating = False
z = Worksheets("IW73").UsedRange.Rows.Count
Set wks_IW73 = Worksheets("IW73")
Set wks_ILA = Worksheets("ILA")
Set rng = wks_ILA.Range(wks_ILA.Cells(1, 1), wks_ILA.Cells(18, 3))
'Spalte "IH-Leistungsart" ermitteln
s = wks_IW73.Application.Match("IH-Leistungsart", wks_IW73.Rows(1), 0)
'Spalte "Gerätedaten" ermitteln
b = wks_IW73.Application.Match("Gerätedaten", wks_IW73.Rows(1), 0)
Text = UserForm1.Seriennummer
For i = 2 To z Step 1
If Cells(i, b) = Text Then
On Error Resume Next
variable = ActiveSheet.WorksheetFunction.VLookup(Val(Cells(i, s)), rng, 3, False)
If variable = "Kunde" Then
UserForm1.Image1.Picture = LoadPicture("D:\logo1.jpg")
Else
If variable = "Harman" Then
UserForm1.Image1.Picture = LoadPicture("D:\logo2.jpg")
End If
End If
End If
Next i
Application.ScreenUpdating = True
End Sub