Macro to check values based on certain text

[Howardc]

I have the word "Check" in Column A. Where Check appears in cloumn A I would like to determine whether the values in the same row as where "check" appears <> (not equal) to zero. This needs to be checked on all sheets, except the sheet "Consolidated"

I have code to do this, but when running the code, I get an error message "unable to get the sum property of the worksheet function class" -the folowing code is highlighted when I click on Debug

If Application.WorksheetFunction.Sum(myrange) <> 0 Then

The full code is

Sub Check_Variance()
        Dim sh
        Dim caddress As String, lastrowSH1 As Long
        Dim startcell As Range, endcell As Range, myrange As Range


        For Each sh In Worksheets
        If UCase(sh.Name) <> "CONSOLIDATED" Then
        caddress = ""
        lastrowSH1 = Sheets(sh.Name).Range("A" & Rows.Count).End(xlUp).Row

        For Each cell In Sheets(sh.Name).Range("A1:A" & lastrowSH1)
        Debug.Print sh.Name
        Debug.Print cell.Value
        If UCase(cell.Value) = "CHECK" Then
        Set startcell = Sheets(sh.Name).Range("B" & cell.Row)
        Set endcell = Sheets(sh.Name).Range("Q" & cell.Row)
        Set myrange = Sheets(sh.Name).Range(startcell, endcell)

         If Application.WorksheetFunction.Sum(myrange) <> 0 Then
          For i = 1 To 26
           If cell.Offset(0, i) <> 0 Then
            If caddress = "" Then
            caddress = cell.Offset(0, i).Address
            Else
            caddress = caddress & ", " & cell.Offset(0, i).Address
            End If
           End If
           Next i
         End If
        End If
        Next cell
        If caddress <> "" Then
        MsgBox "Please check the following address(es) on " & sh.Name & vbNewLine & vbNewLine & caddress
        End If
        End If
        Next sh
        End Sub

[Admin]

@ Howard

Please add code tags while posting codes

[Admin]

Hi

Try

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("A" & .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("Q" & cCell.Row)
                        Set myRange = .Range(startCell, endCell)
    
                        If Application.WorksheetFunction.Sum(myRange) <> 0 Then
                            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
                    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

[Howardc]

Thanks for the reply. I still get "unable to get the sum property of the worksheet function class" -the folowing code is highlighted when I click on Debug

If Application.WorksheetFunction.Sum(myrange) <> 0 Then

Your assistance is most appreciated

[Admin]

Hi

Is there any error values in the sum range ?

[Howardc]

Hi

Is there any error values in the sum range ?

There are two #N/A's, which I think is causing the problem. Would it be possible to incorporate this into the macro i.e if the valiue is <> 0 or there is an error , the user must be advised via the message box

[Admin]

Hi

Replace the error line with

If Evaluate("sumif(" & myRange.Address(external:=1) & ",{"">0"",""<0""})") <> 0 Then

[Howardc]

Hi

Replace the error line with

If Evaluate("sumif(" & myRange.Address(external:=1) & ",{"">0"",""<0""})") <> 0 Then

Thanks for the help-code works perfectly, much appreciated. I need one small amendment. Some the values in the same row as check are for very small eg 0.0001 or (0.0001). I would like the code amended so that where the variance start is more than three decimals plances, it must be ignored

[Admin]

Not sure I understood you correctly. Can you provide an example row ?

[Howardc]

Not sure I understood you correctly. Can you provide an example row ?

Attached please find row of sample data. Where the variance is very small i.e tolerance showing more than three decimal places it must be ignored as tolerance is minute

Your assistance in this regard is most appreciated