Option Explicit
Sub wverweis()
Dim auswahl, rng1, rng2 As Range
Set auswahl = Rows(11).Find("tofind", LookIn:=xlValues, lookat:=xlWhole)
If Not auswahl Is Nothing Then
Range(Cells(11, 8), Cells(11, auswahl.Column - 1)).Select
Set rng2 = Selection
Else
MsgBox "nix gefunden"
Exit Sub
End If
Set rng1 = Range("F15")
Range("K40").Select
ActiveCell.Formula = "=HLOOKUP(" & rng1.Address & Chr(44) & rng2.Address & ",1,TRUE)"
Range("K40").Select
End Sub
Sub Betterwverweis()
Dim auswahl As Range
Dim rng1 As Range
Dim rng2 As Range
Dim strFormula As String
strFormula = "=HLOOKUP(F15,XX,1,TRUE)"
Set auswahl = Rows(11).Find("tofind", LookIn:=xlValues, lookat:=xlWhole)
If Not auswahl Is Nothing Then
Set rng2 = Range(Cells(11, 8), Cells(11, auswahl.Column - 1))
Range("K40").Formula = Replace(strFormula, "XX", rng2.Address(0, 0))
Else
MsgBox "nix gefunden"
End If
End Sub
Sub Alternativ()
Dim auswahl As Range, rng1 As Range
Set auswahl = Rows(11).Find("tofind", LookIn:=xlValues, lookat:=xlWhole)
If auswahl Is Nothing Then
Range("K40").ClearContents
Exit Sub
End If
Range("K40").Value = auswahl.Offset(, -1).Value
Set rng1 = Range(Cells(11, 8), Cells(11, auswahl.Column - 1)).Find(Range("F15").Value, LookIn:=xlValues, lookat:=xlWhole)
If Not rng1 Is Nothing Then Range("K40").Value = rng1.Value
End Sub