Thursday, September 22, 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
    
    Src_Sht.Select
    Cells.Select

 Set LastCell = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell)   
Set Srch_Result = Selection.Find(What:=Srch_Str, After:=LastCell)

' To Search directly in the specific Column
Srch_Result = Sheets("MySht").Columns(2).Find(What:=Srch_Str, LookAt:=xlWhole)

 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
   
        Srch_Result.Activate
        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
    
    Else
    
    MsgBox "Search Item Not Found", vbOKOnly, "Search Completed"
    ActiveSheet.Range("A1").Select
    
   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:

Thanks, TAMATAM

No comments:

Post a Comment

Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.

Featured Post from this Blog

How to compare Current Snapshot Data with Previous Snapshot in Power BI

How to Dynamically compare two Snapshots Data in Power BI Scenario: Suppose, we have a sample Sales data, which is stored with Monthly Snaps...

Popular Posts from this Blog