Macro to check values based on certain text

[Excel Fox]

Howardc, what admin has posted is working fine on the sample file you've attached. Also, the bracket should be ), and not the other way around.

Once again,

Evaluate("sumproduct(--(abs(" & myRange.Address(external:=1) & ")>0.0001)," & myRange.Address(external:=1) & ")")

is correct. Do you want to attach a sample file again, in case this is not working?

[Howardc]

Hi Excelfox

Thanks for the reply. See full code below

When running the macro, it comes up with "type mismatch 13" and the folowing code is highlighted


If cCell.Offset(0, i) <> 0 Then

It would be appreciated if you could assist me

sub Check_Variance()
            Dim sh  As Worksheet, cCell As Range
            Dim cAddress As String, lastRowSH1 As Long, i As Long
            Dim startCell As Range, endCell As Range, myRange As Range


            For Each sh In Worksheets
                If UCase(sh.Name) <> "CONSOLIDATED" Then
                    cAddress = ""
                    
                    With sh
                        lastRowSH1 = .Range("B" & .Rows.Count).End(xlUp).Row
                        For Each cCell In .Range("A1:A" & lastRowSH1)
                            Debug.Print sh.Name
                            Debug.Print cCell.Value
                            If UCase(cCell.Value) = "CHECK" Then
                                Set startCell = .Range("B" & cCell.Row)
                                Set endCell = .Range("Z" & cCell.Row)
                                Set myRange = .Range(startCell, endCell)
            
                          Evaluate("sumproduct(--(abs(" & myRange.Address(external:=1) & ")>0.0001)," & myRange.Address(external:=1) & ")") 
                           
                                    For i = 1 To 26
                                        If cCell.Offset(0, i) <> 0 Then
                                            If cAddress = "" Then
                                                cAddress = cCell.Offset(0, i).Address
                                            Else
                                                cAddress = cAddress & ", " & cCell.Offset(0, i).Address
                                            End If
                                        End If
                                    Next i
                                End If
                            
                        Next cCell
                        If cAddress <> "" Then
                            MsgBox "Please check the following address(es) on " & sh.Name & vbNewLine & vbNewLine & cAddress
                        End If
                    End With
                End If
            Next sh
        End Sub