none
Search a test only in First Row RRS feed

  • Question

  • Hi everyone,

    Good Morning!!

    I want to search a specific Text in only first row of my excel workbook. For example, the text which will be searched is placed in Sheet1("A5"). Once I click on a command button, the system should search that text in First Row(only) of Sheet2. If it is available then it should return the column name.

    Suppose, I am looking for "Hobert" in First Row of Sheet2. Lets say, it is present in Y1 cell. Then, system should return "Y" through a message box.

    Any help would be appreciated.

    Thanks 

    Saturday, September 12, 2015 10:02 AM

Answers

  • Try the following. In the Find code it is your option whether to use  LookAt:=xlPart, _ or  LookAt:=xlWhole, _

    xlPart means that the text is found in the cell but there could also be other text in the cell.

    xlWhole means that only the text being searched is to be in the cell.

    Private Sub CommandButton1_Click()
        Dim wsToSearch As Worksheet
        Dim rngToFind As Range
        Dim strToFind As String
        Dim arrSplit
        Dim strColId As String
       
        Set wsToSearch = Worksheets("Sheet2")
       
        strToFind = Worksheets("Sheet1").Range("A5")
       
        With wsToSearch.Rows(1)
       
            Set rngToFind = .Find(What:=strToFind, _
                            LookIn:=xlFormulas, _
                            LookAt:=xlPart, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
                           
            If Not rngToFind Is Nothing Then
                arrSplit = Split(rngToFind.EntireColumn.Address(0, 0), ":")
                strColId = arrSplit(0)
            Else
                MsgBox strToFind & " not found."
                Exit Sub
            End If
        End With
       
        MsgBox "Column Id is " & strColId

    End Sub


    Regards, OssieMac

    • Marked as answer by Hobert P Saturday, September 12, 2015 6:48 PM
    Saturday, September 12, 2015 11:08 AM