View original (French)

VBA search across multiple Excel sheets

COCOPSL -  
Le Pingou Posted messages 12280 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

9 answers

  1.
    Le Pingou Posted messages 12280 Registration date   Status Contributor Last intervention   1 480
     
    Hello,
    Just passing by, try this correction: 
    If c Is Nothing Then
    MsgBox Message
    End If


    --
    Regards.
    The Penguin
    0
    1. COCOPSL
       
      Good evening.
      Thank you, but it’s indeed the loop that I put at the end, and it does not work.
      0
  2. Le Pingou Posted messages 12280 Registration date   Status Contributor Last intervention   1 480
     
    Hello,
    Well no, you have 
    If c Is Nothing Then
    Message
    End If

    MSGBOX is missing in front of Message. 
    If c Is Nothing Then
    MsgBox Message
    End If


    --
    Best regards.
    The Penguin
    0
    1. COCOPSL
       
      Hello!
      Sorry.... ☺️
      I also tried with MsgBox = "this data..." but it didn't work.
      So I will test yours.
      0
  3. Le Pingou Posted messages 12280 Registration date   Status Contributor Last intervention   1 480
     
    Hello,
    I tested your code, please modify the following part: [If Then end If] 
    If Not c Is Nothing Then firstAddress = c.Address .Select .Range(c.Address).Select Else MsgBox Message End If End With Next Ws End Sub


    --
    Best regards.
    The Penguin
    0
    1. COCOPSL
       
      Hello,
      With your code as with the one I tested, I get the message as many times as there are sheets...????
      0
  4. Le Pingou Posted messages 12280 Registration date   Status Contributor Last intervention   1 480
     
    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
    0
    1. COCOPSL
       
      Thank you for your response.
      Indeed, the value is not found on every sheet. The value is located in one of these sheets, and that's it. (For example: the value 20 is found only once in my folder containing 15 sheets).
      Hence my request for assistance.
      0
  6. Le Pingou Posted messages 12280 Registration date   Status Contributor Last intervention   1 480
     
    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
    0
  7. COCOPSL
     
    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.
    0
  8. Le Pingou Posted messages 12280 Registration date   Status Contributor Last intervention   1 480
     
    Hello,
    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
    0
  9. COCOPSL
     
    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
    0
  10. Le Pingou Posted messages 12280 Registration date   Status Contributor Last intervention   1 480
     
    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
    0