VBA search across multiple Excel sheets
COCOPSL
-
Le Pingou Posted messages 12273 Registration date Status Contributor Last intervention -
Le Pingou Posted messages 12273 Registration date Status Contributor Last intervention -
Bonjour,
I have a file with several sheets.
Not being a pro in VBA, I adapted some VBA code to search for the value entered in F2 of sheet 1, which I named "Recherche".
It works perfectly and takes me directly to the cell where the value is located. So far, so good.
However, I would like a msgBox to open when it does not find the value anywhere other than in F2. And I'm stuck there. There is something I'm doing wrong in the loop because I only managed to get as many "the searched value does not exist!!" messages as there are sheets, and in the code below, it does not work.
I also tried to initiate the search via MsgBox but I get stuck at the same point.
Either I'm making my life complicated or there is a much simpler code.
Here is the code:
Sub Recherche()
Dim MaRecherche
Dim Ws As Worksheet
Dim c As Range
Dim Message As String, firstAddress As String
MaRecherche = Worksheets("Recherche").Range("F2").Value
Message = "The searched value does not exist!!!"
For Each Ws In Worksheets
With Ws
Set c = .Columns("A:KT").Find(What:=MaRecherche, LookIn:=xlValues, LookAt:=xlPart)
If Not c Is Nothing Then
firstAddress = c.Address
.Select
.Range(c.Address).Select
End If
End With
Next Ws
If c Is Nothing Then
Message
End If
End Sub
Thank you for any help you can provide.
Configuration: Windows / Chrome 98.0.4758.102
I have a file with several sheets.
Not being a pro in VBA, I adapted some VBA code to search for the value entered in F2 of sheet 1, which I named "Recherche".
It works perfectly and takes me directly to the cell where the value is located. So far, so good.
However, I would like a msgBox to open when it does not find the value anywhere other than in F2. And I'm stuck there. There is something I'm doing wrong in the loop because I only managed to get as many "the searched value does not exist!!" messages as there are sheets, and in the code below, it does not work.
I also tried to initiate the search via MsgBox but I get stuck at the same point.
Either I'm making my life complicated or there is a much simpler code.
Here is the code:
Sub Recherche()
Dim MaRecherche
Dim Ws As Worksheet
Dim c As Range
Dim Message As String, firstAddress As String
MaRecherche = Worksheets("Recherche").Range("F2").Value
Message = "The searched value does not exist!!!"
For Each Ws In Worksheets
With Ws
Set c = .Columns("A:KT").Find(What:=MaRecherche, LookIn:=xlValues, LookAt:=xlPart)
If Not c Is Nothing Then
firstAddress = c.Address
.Select
.Range(c.Address).Select
End If
End With
Next Ws
If c Is Nothing Then
Message
End If
End Sub
Thank you for any help you can provide.
Configuration: Windows / Chrome 98.0.4758.102
9 answers
Hello,
So in this case the value (MaRecherche) is not present on each sheet.
You need to manually check that the value is indeed present in the sheets...
Once again, I tested it with numerical values and everything works correctly.
My test file: https://www.cjoint.com/c/LCbro5W4Wyn
Best regards.
The Penguin
So in this case the value (MaRecherche) is not present on each sheet.
You need to manually check that the value is indeed present in the sheets...
Once again, I tested it with numerical values and everything works correctly.
My test file: https://www.cjoint.com/c/LCbro5W4Wyn
Best regards.
The Penguin
Hello,
Therefore, I conclude that my code according to the attached file (post 7) is correct, and that your issue is resolved!
--
Best regards.
The Penguin
Therefore, I conclude that my code according to the attached file (post 7) is correct, and that your issue is resolved!
--
Best regards.
The Penguin
Hello Le Pingou,
Thank you for your help.
I tested your macro.
I must not have been clear in my explanation.
The macro does indeed select the sought value (which I placed in cell F2 of the "Recherche" sheet); however, when the value does not exist, I would like the message "The value was not found" to be displayed only once, after searching through all the sheets. Not 20 times if I have 20 sheets, 40 times if I have 40 sheets... etc. And that is what yours does, just like ours.
Thank you for your help.
I tested your macro.
I must not have been clear in my explanation.
The macro does indeed select the sought value (which I placed in cell F2 of the "Recherche" sheet); however, when the value does not exist, I would like the message "The value was not found" to be displayed only once, after searching through all the sheets. Not 20 times if I have 20 sheets, 40 times if I have 40 sheets... etc. And that is what yours does, just like ours.
Hello,
Here is the code that should suit you:
--
Regards.
The Penguin
Here is the code that should suit you:
Sub Search() Dim MySearch Dim Ws As Worksheet Dim c As Range Dim Message As String, firstAddress As String Dim NotExist As Boolean, count As Integer MySearch = Worksheets("Search").Range("F2").Value NotExist = False: count = 0 Message = "The searched value " & MySearch & " does not exist!!!" For Each Ws In Worksheets With Ws Set c = .Columns("A:KT").Find(What:=MySearch, LookIn:=xlValues, LookAt:=xlPart) If Not c Is Nothing Then firstAddress = c.Address .Select .Range(c.Address).Select Else NotExist = True count = count + 1 End If End With Next Ws If count <> 0 Then MsgBox Message & "In " & count & " sheet/s " End If End Sub --
Regards.
The Penguin
Hello
I just tested your code but it shows the message even though the value is present on one of the sheets
I tried modifying the code but I haven't found the solution
I just tested your code but it shows the message even though the value is present on one of the sheets
I tried modifying the code but I haven't found the solution
Hello,
No, it's not possible, the indication is only once at the end as long as one or more values are missing.
Please make your file available on https://www.cjoint.com/ and post the link.
See my test file: https://www.cjoint.com/c/LCdpvRTUPVn
Regards.
The Penguin
No, it's not possible, the indication is only once at the end as long as one or more values are missing.
Please make your file available on https://www.cjoint.com/ and post the link.
See my test file: https://www.cjoint.com/c/LCdpvRTUPVn
Regards.
The Penguin
Thank you, but it’s indeed the loop that I put at the end, and it does not work.