Macro to check values based on certain text

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

Code:

---

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

---

@ Howard

Please add code tags while posting codes :)

Hi

Try

Code:

---

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

---

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

HTML Code:

---

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

---

Your assistance is most appreciated

Hi

Is there any error values in the sum range ?

Quote:

---

Originally Posted by Admin

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

Hi

Replace the error line with

Code:

---

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

---

Quote:

---

Originally Posted by Admin

Hi

Replace the error line with

Code:

---

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

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

Quote:

---

Originally Posted by Admin

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

hi

Code:

---

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

---

Thanks very much for the help. Code works perfectly

Hi

You helped me a while ago with a macro that finds the text 'check" and column A and if the value in the rows that are in line with "Check" is <0 or > 0 , then a message box must pop advise the cell number that there are variance the tolerance is 0.00001. The macro works well where there are postive variances, but does not pick up the negative variances.

See code below for ease of reference

Your assistance inresolving this will be most appreciated

HTML Code:

---

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)
   
                    If Evaluate("abs(sumif(" & myRange.Address(external:=1) & ",{"">0"",""<0""}))") > 0.00001 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

---

Hi

Try

Code:

---

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

---

Thanks for the reply. When running the macro, it comes up with type mismatch 13

I picked up one small error, ) before >0.00001 but it still does not solve the problem. It would be appreciated if you would check & correct

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

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,

Code:

---

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?

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

Code:

---

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

---

Hi

Howard,

Please add code tags while posting code. This time I added for you.

this line

Code:

---

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

---

should write as

Code:

---

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

---

also add an End If after Next i

Thanks for the help, much appreciated

Hi Admin

I picked up that if the first value in say column B in line with the text "check" is negative, the row number is not displayed. However, if postive the row number is displayed. It would be appreciated if you could amend the code to incorporate this

Hi Admin

Attached please find sample data. When activating the macro and there is only one value being a debit in the same row as the text check, it is not shown in the message box. All values that are not zero, except values that are more or less than 5 dicimal places are to be shown-see sample data, B52, which is not shown in message box

Hi

Just remove the ABS part from the formula.

Thanks for the help,I removed the ABS part, but the value in cell B52 (-20) is not shown in the message box. If this value is positive, the row number will be displayed ijn the message box

If I have 20 in B52 and -20 in C52 , then B52 & C52 will be displayed in the message box

It would be appreciated if you could correct this

Hi

Use this formula.

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

Quote:

---

Originally Posted by Admin

Hi

Use this formula.

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

---

Thanks for the help, it would perfectly now.

I would like to exclude anothetr sheet when checking varianced called "Months". How do I include this. Tried, but could not get it to work

For Each sh In Worksheets
If UCase(sh.Name) <> "CONSOLIDATED" Then


cAddress = ""

Hi Admin

Managed to sort out the code

If UCase(sh.Name) <> "CONSOLIDATED" And UCase(sh.Name) <> "MONTHS" Then
cAddress = ""

Regards

Howard