Macro to check values based on certain text

**Admin** · 10-18-2012, 09:13 PM

hi

Code:

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

**Howardc** · 10-18-2012, 10:25 PM

Thanks very much for the help. Code works perfectly

**Howardc** · 10-31-2012, 03:57 PM

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

**Admin** · 10-31-2012, 10:53 PM

Hi

Try

Code:

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

**Howardc** · 10-31-2012, 11:06 PM

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

**Excel Fox** · 11-01-2012, 12:20 AM

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?

**Howardc** · 11-01-2012, 11:06 AM

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

**Admin** · 11-01-2012, 05:38 PM

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

**Howardc** · 11-01-2012, 07:11 PM

Thanks for the help, much appreciated

**Howardc** · 11-04-2012, 12:14 PM

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