Finding and comparing dollar totals calculated from two different sheets to raise a warning if they do not match

[SkywardPalm]

I was curious if I do not need the ElseIf statement, since the If and ElseIf have similar structures and I potentially need to search in more columns if the Current total was not found (shifted to the right due to sub-section values). Maybe there is a more dynamic way of finding the $ total that is somewhere in row 228 of the SUMMARY sheet (displayed below)? Sometimes there is a previous dollar total on the same row (potentially in columns F>beyond that should be ignored). Also, I am getting "Compile Error: Else without If" on the last Else statement. Wondering if I am not calling the If, ElseIf, Else properly. Thanks in advance!

Option Explicit

Sub DollarTotalCheck()
    Dim wsMaster As Worksheet, wsSUMMARY As Worksheet
    Dim extHeaderCell As Range
    Dim summaryTotal As Single, summaryTotal2 As Single, masterTotal As Single

    Set wsMaster = ThisWorkbook.Sheets("Master")
    Set wsSUMMARY = ThisWorkbook.Sheets("SUMMARY")
    
'Find the cell having "Extended" value
    Set extHeaderCell = wsMaster.Range(wsMaster.Cells(1, 1), wsMaster.Cells(1, _
            wsMaster.Cells(1, Columns.Count).End(xlToLeft).Column)).Find("Extended")
    
    With wsMaster
        'If Found
        If Not extHeaderCell Is Nothing Then
            extHeaderCell.Activate
            masterTotal = ActiveCell.Offset(, 1).Value
            masterTotal = FormatNumber(masterTotal, 2)
            Debug.Print vbNewLine & "Master Extended Total Found: " & masterTotal
        'If Not Found
        Else
            Debug.Print vbNewLine & "Master: Extended Header Not Found"
        End If
    End With
    
'Find the SUMMARY sheet current total and compare. If they match, Exit Sub
    
    With wsSUMMARY
        If Application.WorksheetFunction.IsNumber(Range("C228")) = True Then
            summaryTotal = Range("C228").Value
            summaryTotal = FormatNumber(summaryTotal, 2)
            'if Totals DO NOT Match, dialog appears to check Totals
            If summaryTotal <> masterTotal Then
                MsgBox "Please check Extended Totals match!" & vbCrLf & "SUMMARY = " & summaryTotal & vbCrLf & "Master  =" & masterTotal
            'else Exit Sub
            Else
                Exit Sub
            End If
        ElseIf Application.WorksheetFunction.IsNumber(Range("D228")) = True Then
            summaryTotal2 = Range("D228").Value
            summaryTotal2 = FormatNumber(summaryTotal2, 2)
            'if Totals DO NOT Match, dialog appears to check Totals
            If summaryTotal2 = masterTotal Then
                MsgBox "Please check Extended Totals match!" & vbCrLf & "SUMMARY = " & summaryTotal2 & vbCrLf & "Master  =" & masterTotal
            'else Exit Sub
            Else
                Exit Sub
        Else
            Debug.Print vbNewLine & "SUMMARY Total Not Found"
        End If
    End With

End Sub

The row containing the totals can look like below:

SampleSheet.xlsm
	A	B	C	D	E	F	G	H	I
228		Current Total	$ 89,001.29		Previous Total	$ 49,988.64		$ 39,012.65
SUMMARY

 

[DanteAmor]

Try this:

Sub DollarTotalCheck()
  Dim wsMaster As Worksheet, wsSUMMARY As Worksheet
  Dim extHeaderCell As Range, f As Range
  Dim masterTotal As Double

  Set wsMaster = ThisWorkbook.Sheets("Master")
  Set wsSUMMARY = ThisWorkbook.Sheets("SUMMARY")
  
  Set extHeaderCell = wsMaster.Range("1:1").Find("Extended")
  If Not extHeaderCell Is Nothing Then
    masterTotal = extHeaderCell.Offset(, 1).Value
    Set f = wsSUMMARY.Range("C228:D228").Find(masterTotal, , xlValues, xlWhole)
    If Not f Is Nothing Then
      MsgBox "Match"
    Else
      MsgBox "Please check Extended Totals match!"
    End If
  End If
End Sub

 

[SkywardPalm]

Try this:

Sub DollarTotalCheck()
  Dim wsMaster As Worksheet, wsSUMMARY As Worksheet
  Dim extHeaderCell As Range, f As Range
  Dim masterTotal As Double

  Set wsMaster = ThisWorkbook.Sheets("Master")
  Set wsSUMMARY = ThisWorkbook.Sheets("SUMMARY")
 
  Set extHeaderCell = wsMaster.Range("1:1").Find("Extended")
  If Not extHeaderCell Is Nothing Then
    masterTotal = extHeaderCell.Offset(, 1).Value
    Set f = wsSUMMARY.Range("C228:D228").Find(masterTotal, , xlValues, xlWhole)
    If Not f Is Nothing Then
      MsgBox "Match"
    Else
      MsgBox "Please check Extended Totals match!"
    End If
  End If
End Sub

Thank you for the help! I'm having an issue where the sub is only running once before save (I am calling the macro before save, and want it to run every time ctrl+s is pressed). Any ideas what I could be doing wrong?

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Call DollarTotalCheck

End Sub

 

[DanteAmor]

Thank you for the help! I'm having an issue where the sub is only running once before save (I am calling the macro before save, and want it to run every time ctrl+s is pressed). Any ideas what I could be doing wrong?

Are you sure you have Control + s to save?
Test with Control + g

 

[SkywardPalm]

Are you sure you have Control + s to save?
Test with Control + g

Yes, it works the first time I save in one instance but not if I change something and try saving again.

 

[DanteAmor]

Do you have other macros in the sheet? one that contains this instruction:
Application.EnableEvents = False