Microsoft Office Forums - View Single Post

macropod · 10-31-2015, 02:06 PM

Here's a trivial demonstration of using the Find command to find something in row 3 and return the corresponding value from row 2:

Code:

Sub Demo()
Dim xlRng As Range, StrTxt As String
StrTxt = InputBox("Please input the text to find")
With ActiveSheet
  Set xlRng = .Range("3:3").Find(What:=StrTxt, LookIn:=xlValues, _
    LookAt:=xlWhole, SearchOrder:=xlByColumns, MatchCase:=True, SearchFormat:=False)
  If Not xlRng Is Nothing Then
    MsgBox "Row 3: " & StrTxt & " found in column " & xlRng.Column & vbCr & _
      "Row 2: " & .Cells(2, xlRng.Column).Text & " found in column " & xlRng.Column
  End If
End With
End Sub