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.
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
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.