Thursday, 22 September 2016

VBA Macro to Find or Search a String in a Specific Sheet in Excel

Excel VBA Macro to Find or Search a String  in a Specific Sheet and get the Address of Cell,Column and Row details
Please note that Search string should be unique in the source data and the below macro returns the first find result details only, though multiple occurrences find in source.
Sub FindStr()
Dim Srch_Result As Range
Dim LastCell as range
Set Sht = ThisWorkbook.Sheets("CPanel")
Set Src_Sht = ThisWorkbook.Sheets("Data")

Srch_Str = Sht.Range("A2").Value

 Set LastCell = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell)   
Set Srch_Result = Selection.Find(What:=Srch_Str, After:=LastCell)
 Set Srch_Result =Selection.Find(What:=Srch_Str, After:=ActiveCell, LookIn:= _
      xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:= _
     xlNext, MatchCase:=False, SearchFormat:=False)
' Selection.FindNext(After:=ActiveCell).Activate

   If Not Srch_Result Is Nothing Then
        Sht.Range("A2").Offset(0, 1).Value = ActiveCell.Address
        Sht.Range("A2").Offset(0, 2).Value = Cells(1, ActiveCell.Column).Value
        Sht.Range("A2").Offset(0, 3).Value = ActiveCell.Column
        Sht.Range("A2").Offset(0, 4).Value = ActiveCell.Row
    MsgBox "Search Item Not Found", vbOKOnly, "Search Completed"
   End If
               Set Srch_Str = Nothing
               Set Sht = Nothing
               Set LastCell=Nothing
               Set Src_Sht = Nothing
 End Sub

Source Data , where we search a string : 

Macro output:


No comments:

Post a Comment

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts