VBA code to display where there is a value in Col D next to "Variance"

[howard]

I have written code to check if there is a value not equal to zero in Col D in the same row as where text "variance" appears in Col A


I want the code to check if there is a variance (Value not equal to zero) in Col D on all sheets, except the last sheet


The code runs, but does not display a message if there are any variances <> 0


In the example below Cell D5 must be displayed as there is a variance <>

Kindly amend my code pertaining to the above

Book1
	A	B	C	D
1	BR1	1270		-75217
2	BR2	1271		50000
3	BR3	1275		25619
4
5	Variance			402
6
7
8
9	BR1	1785		-85200
10	BR2	1776		45000
11	BR3	1772		40200
12
13	Variance			0
14
15
16
Sheet1

Cell Formulas
Range	Formula
D5	=SUM(D1:D4)
D13	=SUM(D9:D12)

 Sub Variance_Message()
With Application
.CutCopyMode = False
.Calculation = xlCalculationManual
End With

Dim ws As Worksheet, r As Range, msg As String, ff As String, I As Long
For I = 1 To Count

For Each ws In Sheets(I)




    Set r = ws.Columns("a").Find("Variance")
    If Not r Is Nothing Then
        ff = r.Address
        Do
            If Round(r.Offset(, 3).Value, 2) <> 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")
Next I

With Application
.CutCopyMode = False
.Calculation = xlCalculationAutomatic
End With


End Sub

 

[dmt32]

Hi,
not tested but see if this update to your code does what you want

Sub Variance_Message()
Dim ws As Worksheet
Dim r As Range
Dim msg As String, ff As String
Dim I As Long


For Each ws In ThisWorkbook.Worksheets
    Set r = ws.Columns("a").Find("Variance")
    If Not r Is Nothing Then
        ff = r.Address
        Do
            If Val(r.Offset(, 3).Text) <> 0 Then
             msg = msg & ws.Name & " " & r.Address(0, 0) & Chr(10)
            End If
            Set r = ws.Columns("a").FindNext(r)
        Loop Until ff = r.Address
    End If
    
    Set r = Nothing
Next




MsgBox IIf(Len(msg) > 0, msg, "No Variances Found")


End Sub

Dave

 

[howard]

Thanks for the help. I only shows if there is a variance in Col D on the last sheet where variance appears in Col A in the same row


I want the message box to show the cell address for each sheet , except the last sheet where there is a value in Col D <> 0 where "Variance" appears in Col A

Kindly amend your code

 

[dmt32]

I want the message box to show the cell address for each sheet , except the last sheet where there is a value in Col D <> 0 where "Variance" appears in Col A

Sorry, omitted that part of code - see if this update helps

Sub Variance_Message()
Dim ws As Worksheet
Dim r As Range
Dim msg As String, ff As String
Dim I As Long


For Each ws In ThisWorkbook.Worksheets
    Set r = ws.Columns("a").Find("Variance", lookat:=xlWhole, LookIn:=xlValues)
'ignore last sheet
    If ws.Index = Sheets.Count Then Exit For
    
    If Not r Is Nothing Then
        ff = r.Address
        Do
            If Val(r.Offset(, 3).Text) <> 0 Then
             msg = msg & ws.Name & " " & r.Address(0, 0) & Chr(10)
            End If
            Set r = ws.Columns("a").FindNext(r)
        Loop Until ff = r.Address
    End If
    
    Set r = Nothing
Next




MsgBox IIf(Len(msg) > 0, msg, "No Variances Found")


End Sub

Code should Find Text "Variance" in each but the last worksheet & if value in Column D on same Row <> 0 then add Sheet Name & Cell Address to msg variable.

Dave

 

[howard]

Thanks for the updated code

Need one more amendment please


If there variance is in debit in Col D where variance appears in the same row in Col A , then the message displayed. However, if the variance is in credit, the cell address containing the credit variance in Col D where text variance appears in Col A in the same row is not displayed

 

[dmt32]

try changing this line:

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

to this

If Val(r.Offset(, 3).Text) < 0 Then

and see if this does what you want

Dave

 

[howard]

Hi Dave

Thanks for the help. need to show values > 0 as well as values < 0 In Col D where text "Variance" appears in Col A in the same row


I have amended the code as follows and it works perfectly

 Sub Variance_Message()

Dim ws As Worksheet
Dim r As Range
Dim msg As String, ff As String
Dim I As Long


For Each ws In ThisWorkbook.Worksheets
    Set r = ws.Columns("a").Find("Variance", lookat:=xlWhole, LookIn:=xlValues)
'ignore last sheet
    If ws.Index = Sheets.Count Then Exit For
     
    If Not r Is Nothing Then
        ff = r.Address
        Do
             If Round(r.Offset(, 3).Value, 2) <> 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
    
    Set r = Nothing
Next




MsgBox IIf(Len(msg) > 0, msg, "No Variances Found")


End Sub