AW: Frage
24.01.2018 10:34:52
Werner
Hallo Philip,
ich bin mal davon ausgegangen, dass du in Zeile 1 nach einem String suchst.
Option Explicit
Public Sub Suchen()
Dim loSpalte As Long, strAdresse As String, strSuche As String
Dim raBereich As Range, raFund As Range
strSuche = "DeinSuchbegriff"
With Worksheets("Tabelle1") 'Blattname anpassen
loSpalte = .Cells(1, .Columns.Count).End(xlToLeft).Column
Set raBereich = .Range(.Cells(1, 1), .Cells(1, loSpalte))
Set raFund = .Rows(1).Find(what:=strSuche, LookIn:=xlValues, lookAt:=xlWhole)
If Not raFund Is Nothing Then
strAdresse = .Range(.Cells(4, raFund.Column + 1), .Cells(250, raFund.Column + 1)). _
Address
MsgBox strAdresse
Else
MsgBox "Suchbegriff " & strSuche & " nicht vorhanden."
End If
End With
Set raFund = Nothing
End Sub
Gruß Werner