1. ## Pop up Message

I have text "variance in Col A and values in Col D on several sheets. Where the value in Col D in the same row as the text "variance" is not equal to zero, then I want a message to advise where row number and sheet the variance is on

I have written code to do this, but it tell me that there are variabnce in Col D when in fact they are zero

Your assistance in resolving this is most appreciated

Sub Variance_Message()

HTML Code:
```Dim ws As Worksheet, r As Range, msg As String, ff As String
For Each ws In Sheets
Set r = ws.Columns("a").Find("Variance")
If Not r Is Nothing Then
Do
If (r.Offset(, 4).Value <> 0) Then
msg = msg & ws.Name & r.Address(0, 0)
End If
Set r = ws.Columns("a").FindNext(r)
End If
Next
MsgBox IIf(Len(msg) > 0, msg, "No Variances Found")
End Sub```

2. Howard,

Try this minor change:
r.Offset(, 4).Value
to
Round(r.Offset(, 4).Value,0)

Of course if your variance values have decimal places you'll have to adjust the 0 to what ever number of decimal places are significant, e.g. if your significant values are like .001, .103, etc then change the 0 to 3, etc.

HTH

3. Howard,

Try changing the offset to 3 to get the column D. An offset of 4 will put you in column E

Code:
```Dim ws As Worksheet, r As Range, msg As String, ff As String
For Each ws In Sheets
Set r = ws.Columns("a").Find("Variance")
If Not r Is Nothing Then
Do
If r.Offset(, 3).Value <> 0 Then
msg = msg & ws.Name & r.Address(0, 0) & ", "
End If
Set r = ws.Columns("a").FindNext(r)
End If
Next
MsgBox IIf(Len(msg) > 0, msg, "No Variances Found")```

4. Hi Retired Geek & Maudibe

Thanks for the help. The offset must be , 3.

I have incorporated the round functon as suggested by Retired Geek

Regards

Howard

5. Hi Howard

As per RG's note, don't forget to set round to 2 if you are working in dollars and cents.
(Good job you're not working in pounds, shillings and pence)

zeddy

6. Hi Zeddy

Thanks for the advice-I had rounded to 2 and it works perfectly

Regards

Howard

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•