Type Mismatch error

[carbonbased65]

Hi, I hope you are well.

I am trying to create a simple script to dynamically list all worksheets that have the text "Include" in cell A4 but am getting a Type Mismatch error.
I think it has something to do with using ws.Name in the Worksheets() expression because if I test this using a sheet name e.g. Worksheets("Nova") it will return all sheets (as Nova has "Include" in cell A4.
This script lists all the worksheet names perfectly if I do not use the if statement to refine the output.

Any assistance you can offer to resolve this would be most appreciated.

Sub list_sheets()

Dim ws As Worksheet
Dim msg As String

For Each ws In ThisWorkbook.Worksheets
    If Worksheets(ws.Name).Range("A4") = "Include" Then
       msg = msg & ws.Name & vbNewLine
    End If
Next ws

MsgBox (msg)

End Sub

Thank You
BR
-Andrew

 

[Joe4]

Welcome to the Board!

Since "ws" is already a Worksheet object, you should just be able to replace this:

If Worksheets(ws.Name).Range("A4") = "Include" Then

with this:

If ws.Range("A4") = "Include" Then

 

[Fluff]

Hi & welcome to MrExcel.
Which line of code gives the error?

 

[Joe4]

Hi & welcome to MrExcel.
Which line of code gives the error?

They had originally tried to highlight this line in the code:

    If Worksheets(ws.Name).Range("A4") = "Include" Then

but as you know, you cannot format text when using VBA Code tags. So I cleaned up that code in the original post.

So I am guessing that is the offending line.

 

[mikerickson]

You would get that error if some sheet has an error value (e.g. #DIV/0) in A4.
You might try

If CStr(Worksheets(ws.Name).Range("A4")) = "Include" Then

 

[carbonbased65]

Welcome to the Board!

Since "ws" is already a Worksheet object, you should just be able to replace this:

If Worksheets(ws.Name).Range("A4") = "Include" Then

with this:

If ws.Range("A4") = "Include" Then

Thanks Joe for the object tip - appreciated.

 

[carbonbased65]

You would get that error if some sheet has an error value (e.g. #DIV/0) in A4.
You might try

If CStr(Worksheets(ws.Name).Range("A4")) = "Include" Then

Thanks for the solution mikerickson this works perfectly !
I just checked and 1 sheet had a #ref error in it