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

[Admin]

hi


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

[Howardc]

Thanks very much for the help. Code works perfectly

[Howardc]

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

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]

Hi

Try


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

[Howardc]

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]

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

[Admin]

Hi

Howard,

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

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

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

also add an End If after Next i

[Howardc]

Thanks for the help, much appreciated

[Howardc]

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

[Howardc]

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

[Admin]

Hi

Just remove the ABS part from the formula.

[Howardc]

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

[Admin]

Hi

Use this formula.

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

[Howardc]

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 = ""

[Howardc]

Hi Admin

Managed to sort out the code

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

Regards

Howard