Macro to show Message where that is a variance

[howard]

I have a spreadsheet, which contains the word variance in column A and and value in column D that is in line with the word "variance".

I have written code that will advise me where there is a value that is either greater or less that zero in column D in line with the word "variance", but cannot get it to work propertly. I have also attached sample data below. It would be appreciated if you could assist me

Sub Variance_Message()
Sheets("sheet1").Select
Dim ws As Worksheet, r As Range, msg As String, ff As String
For Each ws In Sheets
Set r = ws.Columns("b").Find("Variance")
If Not r Is Nothing Then
ff = r.Address
Do
If (r.Offset(, 3).Value< 0) + (r.Offset(, 3).Value > 0) Then
msg = msg & ws.Name & r.Address(0, 0)
End If
Set r = ws.Columns("b").FindNext(r)
Loop Until ff = r.Address
End If
Next
MsgBox IIf(Len(msg) > 0, msg, "No Variances Found")
End Sub

Variances.xls
	A	B	C	D
5
6	BER1	1145		0.00
7		1146		0.00
8		1147		0.00
9		1148		25.00
10		1149		180.00
11		1180		0.00
12
13	Variance			205.00
14
15	BER2	1145		0.00
16		1146		0.00
17		1147		0.00
18		1148		35.00
19		1149		0.00
20		1180		0.00
21
22	Variance			35.00
23
Sheet1

 

[Jerry Sullivan]

Hi Howard,

Try this modified version:

Sub Variance_Message()
    Dim ws As Worksheet, r As Range, msg As String, ff As String
    For Each ws In ActiveWorkbook.Sheets
        Set r = ws.Columns("A").Find(what:="Variance", LookIn:=xlValues)
        If Not r Is Nothing Then
            ff = r.Address
            Do
                If (r.Offset(, 3).value < 0) Or (r.Offset(, 3).value > 0) Then
                        msg = msg & ws.Name & "." & r.Address & vbLf
                End If
                Set r = ws.Columns("A").FindNext(r)
            Loop Until ff = r.Address
        End If
    Next
    MsgBox IIf(Len(msg) > 0, msg, "No Variances Found")
End Sub

The primary change was the need for an Or operator instead of + to test if your value in Column D is not 0.

Alternatively, you could replace that line with:

                If (r.Offset(, 3).value <> 0) Then

I'm guessing your reference to Column "b" instead of "a" was merely the
result of deleting a column prior to transfering your code and screen shot
to this Board.

 

[howard]

Hi Jerry

Thanks for the help, much appreciated

Regards

Howard