Code to pop up message when there is a variance

[Howardc]

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()

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
ff = r.Address
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)
Loop Until ff = r.Address
End If
Next
MsgBox IIf(Len(msg) > 0, msg, "No Variances Found")
End Sub


@Howardc, Edit your thread and wrap the code using code tag.

[Dexter1759]

@Howardc, Edit your thread and wrap the code using code tag.[/QUOTE]

Hi Howardc,

It's not 100% clear, exactly what you're trying to do, but one thing that looks out of place to me is the Offset. Try changing it from 4 to 3. The Offset from Col A to D is 3 (B=1, C=2, etc).

Dex

@ Dex, Please do not quote entire post unless it is relevant :)