search for string in all sheets of an excel file using macros
I have written a macro which will search a string in all the sheets of an excel file. This macro will activate the first sheet as well as the cell in the sheet which contains the search string. If not found then it will show a message. This macro runs fine. I wanted to extend this functionality to cover all the sheets which contains this string and not the first one. So I modified the macro but it is not working as expected. I have given the code below and also commented at the place where it is showing the error.
Dim sheetCount As Integer Dim datatoFind Sub Button1_Click() Find_Data End Sub Private Sub Find_Data() Dim counter As Integer Dim currentSheet As Integer Dim notFound As Boolean Dim yesNo As String notFound = True On Error Resume Next currentSheet = ActiveSheet.Index datatoFind = InputBox("Please enter the value to search for") If datatoFind = "" Then Exit Sub sheetCount = ActiveWorkbook.Sheets.Count If IsError(CDbl(datatoFind)) = False Then datatoFind = CDbl(datatoFind) For counter = 1 To sheetCount Sheets(counter).Activate Cells.Find(What:=datatoFind, After:=ActiveCell, LookIn:=xlFormulas, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False).Activate If InStr(1, ActiveCell.Value, datatoFind) Then If HasMoreValues(counter + 1) Then 'Not completing the method and directly entering yesNo = MsgBox("Do you want to continue search?", vbYesNo) If yesNo = vbNo Then notFound = False Exit For End If End If Sheets(counter).Activate End If Next counter If notFound Then MsgBox ("Value not found") Sheets(currentSheet).Activate End If End Sub Private Function HasMoreValues(ByVal sheetCounter As Integer) As Boolean HasMoreValues = False Dim str As String For counter = sheetCounter To sheetCount Sheets(counter).Activate str = Cells.Find(What:=datatoFind, After:=ActiveCell, LookIn:=xlFormulas, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False).Value 'Not going further than this i.e. following code is not executed If InStr(1, str, datatoFind) Then HasMoreValues = True Exit For End If Next counter End Function
I was able to solve my problem and have posted the code for the ones who might need it
Dim sheetCount As Integer Dim datatoFind Sub Button1_Click() Find_Data End Sub Private Sub Find_Data() Dim counter As Integer Dim currentSheet As Integer Dim notFound As Boolean Dim yesNo As String notFound = True On Error Resume Next currentSheet = ActiveSheet.Index datatoFind = StrConv(InputBox("Please enter the value to search for"), vbLowerCase) If datatoFind = "" Then Exit Sub sheetCount = ActiveWorkbook.Sheets.Count If IsError(CDbl(datatoFind)) = False Then datatoFind = CDbl(datatoFind) For counter = 1 To sheetCount Sheets(counter).Activate Cells.Find(What:=datatoFind, After:=ActiveCell, LookIn:=xlFormulas, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False).Activate If InStr(1, StrConv(ActiveCell.Value, vbLowerCase), datatoFind) Then notFound = False If HasMoreValues(counter) Then yesNo = MsgBox("Do you want to continue search?", vbYesNo) If yesNo = vbNo Then Sheets(counter).Activate Exit For End If Else Sheets(counter).Activate Exit For End If Sheets(counter).Activate End If Next counter If notFound Then MsgBox ("Value not found") Sheets(currentSheet).Activate End If End Sub Private Function HasMoreValues(ByVal sheetCounter As Integer) As Boolean HasMoreValues = False Dim str As String Dim lastRow As Long Dim lastCol As Long Dim rRng As Excel.Range For counter = sheetCounter + 1 To sheetCount Sheets(counter).Activate lastRow = ActiveCell.SpecialCells(xlLastCell).Row lastCol = ActiveCell.SpecialCells(xlLastCell).Column For vRow = 1 To lastRow For vCol = 1 To lastCol str = Sheets(counter).Cells(vRow, vCol).Text If InStr(1, StrConv(str, vbLowerCase), datatoFind) Then HasMoreValues = True Exit For End If Next vCol If HasMoreValues Then Exit For End If Next vRow If HasMoreValues Then Sheets(sheetCounter).Activate Exit For End If Next counter End Function
Regards,
Samar
The problem is that Cells.Find
returns a range. When you use it in your function HasMoreValues
, you use it like this:
Cells.Find(...).Value
But the returned range does not convert to a .value correctly. You can fix this by using .text
instead of .value
, like this:
Cells.Find(...).text
Or completely:
str = Cells.Find(What:=datatoFind, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).text
To be completely correct, you should probably set
the result of the find to a Range variable, and then access it through that, in case the Find search returns nothing. However according to the documentation Cells.Find
always returns a range of one cell, so you might be okay.
上一篇: 在Excel中运行操作