Find Worksheet based on Value

[quanweitkd]

I have a raw data report with up to 100 worksheets. There are a few worksheets that contains "No records found" showing on cell A4.
Is there a VBA code that can loop through all the worksheets and make the first occurrence of "no record found" worksheet active?
I have tried more than 10 solutions online but all failed.

 

[Alex Blakenburg]

Try:
Edited

Sub FindFirstEmptySheet()

    Dim sht As Worksheet
  
    For Each sht In Worksheets
        If InStr(1, sht.Range("A4"), "No records found", vbTextCompare) Then
            sht.Activate
        End If
    Next sht

End Sub

 

[Alex Blakenburg]

Oops try this one:

Sub FindFirstEmptySheet()

    Dim sht As Worksheet
    
    For Each sht In Worksheets
        If InStr(1, sht.Range("A4"), "No records found", vbTextCompare) Then
            'Debug.Print sht.Name
            sht.Activate
            Exit For
        End If
    Next sht

End Sub

 

[Alex Blakenburg]

Although the above works I should have done something like this:
(if you are sure its an exact match you can remove the UCase from both sides)

Sub FindFirstEmptySheet()

    Dim sht As Worksheet
  
    For Each sht In Worksheets
        If UCase(sht.Range("A4")) = UCase("No records found") Then
            sht.Activate
            Exit For
        End If
    Next sht

End Sub

 

[quanweitkd]

Try:
Edited

Sub FindFirstEmptySheet()

    Dim sht As Worksheet
 
    For Each sht In Worksheets
        If InStr(1, sht.Range("A4"), "No records found", vbTextCompare) Then
            sht.Activate
        End If
    Next sht

End Sub

No response after running it. Not sure what is the problem. This is the file I am working on.

https://kingstride-my.sharepoint.co...hBivsmnkj5MQ4ByvdzCsZTAIliNMsadhzvvw?e=7hdXIS

 

[Alex Blakenburg]

Sharepoint often does not allow Public Links to be sent out. Can you use google drive, drop box or another sharing platform and make it available to anyone with the link. I can't get to your file.
Just to be sure you need to send it to me, can you try my macro in post #4 first, to see if it makes any difference.

 

[quanweitkd]

Sharepoint often does not allow Public Links to be sent out. Can you use google drive, drop box or another sharing platform and make it available to anyone with the link. I can't get to your file.
Just to be sure you need to send it to me, can you try my macro in post #4 first, to see if it makes any difference.

I have tried the macro on post #4 also no response.
You can access my file via this Notion link. This is not the original work file, hence sharing it on public platform is ok.

Excel Forum

A new tool for teams & individuals that blends everyday work apps into one.

bennychankw.notion.site

Appreciate your help!

 

[Alex Blakenburg]

I had the search text wrong no "s" and you have an exclamation mark as well, here it is updated.

Sub FindFirstEmptySheet()

    Dim sht As Worksheet
  
    For Each sht In Worksheets
        Debug.Print sht.Name & "-" & sht.Range("A4")
        If UCase(sht.Range("A4")) = UCase("No record found!") Then
            sht.Activate
            Exit For
        End If
    Next sht

End Sub

 

[quanweitkd]

It worked! Thank you Alex!!

 

[Alex Blakenburg]

Thanks for letting me know. Sorry it took quite that many goes.