Challenge with vba countifs

P

PatrickK777777

New Member
Joined
Apr 3, 2024
Messages
20
Office Version
  1. 2016
Platform
  1. Windows
Am having a challenge here. Trying to use the application.worksheetfunction.countifs without success in a particular scenario. I want to count all values in range b5:b10000 greater than or equal to a date-type variable 'SmallestDate'. I've tried criteria like ">=" & SmallestDate', ">=SmallestDate" but am getting a result like as if the the matching values do not exist. Any ideas/answers are highly valued🙏
 
Sort by date Sort by votes
Micron said:
Sorry, not even going to attempt to read that. I've never seen that result from using VBA code tags on this forum. Must have been something else?
Click to expand...
Dim OpeningBalance As Double

Dim ObservedBalance As Double

Dim CalculatedBalance As Double

Dim TotalImbalance As Double

Dim TotalCalculatedBalance As Double

Dim DateInFlows As Double

Dim DatelOutFlows As Double

Dim DateDebtors As Double

Dim DateCreditors As Double

Dim SmallestDate As Date

Dim BiggestDate As Date

Dim DateToWorkOn As Date

Dim lastRowInIncomesColumn As Long

Dim lastRowInExpensesColumn As Long

Dim ReferenceBook As String

Dim EnteredValue As String

Set ws = ThisWorkbook.Sheets("sheet1")

Sheet1BalancingMethod = ""

'get user's desire to balance off the book

Response = MsgBox("You have initiated balancing off of this work-sheet. Do you want to continue?", vbQuestion + vbYesNo, "Confirmation")

If Response = vbYes Then

'INITIATE DATE ORDER

ws.Range("BD1:BG1").Calculate

'get biggest date

If ws.Cells(1, "BE").Value > ws.Cells(1, "BG").Value Then

BiggestDate = CDate(ws.Cells(1, "BE").Value)

Else

BiggestDate = CDate(ws.Cells(1, "BG").Value)

End If

'get smallest date

If ws.Cells(1, "BD").Value <= ws.Cells(1, "BF").Value And ws.Cells(1, "BD").Value <> 0 Then

SmallestDate = CDate(ws.Cells(1, "BD").Value)

ElseIf ws.Cells(1, "BF").Value <= ws.Cells(1, "BD").Value And ws.Cells(1, "BF").Value <> 0 Then

SmallestDate = CDate(ws.Cells(1, "BF").Value)

ElseIf ws.Cells(1, "BD").Value >= ws.Cells(1, "BF").Value And ws.Cells(1, "BF").Value = 0 Then

SmallestDate = CDate(ws.Cells(1, "BD").Value)

ElseIf ws.Cells(1, "BF").Value >= ws.Cells(1, "BD").Value And ws.Cells(1, "BD").Value = 0 Then

SmallestDate = CDate(ws.Cells(1, "BF").Value)

End If

If ws.Cells(1, "BD").Value = 0 And ws.Cells(1, "BF").Value = 0 Then

SmallestDate = BiggestDate

End If

'Set REFERENCE BOOK

If RemoveRepeatingSpaces(ws.Cells(3, "P").Value) = "" Then

ReferenceBook = InputBox("Enter the reference book U want to balance.")

Else

ReferenceBook = RemoveRepeatingSpaces(ws.Cells(3, "P").Value)

End If

'Check for presence of multiple reference books

If Application.WorksheetFunction.CountIfs(ws.Range("DA6:DA1048576"), ReferenceBook) + Application.WorksheetFunction.CountIfs(ws.Range("DK6:DK1048576"), ReferenceBook) <> Application.WorksheetFunction.CountA(ws.Range("DA6:DA1048576")) + Application.WorksheetFunction.CountA(ws.Range("DK6:DK1048576")) Then

ReferenceBook = RemoveRepeatingSpaces(InputBox("Multiple reference books found. Please confirm the reference book which you are balancing off."))

End If

If Application.WorksheetFunction.CountIfs(ws.Range("DA6:DA1048576"), ReferenceBook) + Application.WorksheetFunction.CountIfs(ws.Range("DK6:DK1048576"), ReferenceBook) = 0 Then

MsgBox ("The reference book provided has no entries made under it.")

Exit Sub

End If

If RemoveRepeatingSpaces(ReferenceBook) = "" Then

MsgBox ("Reference book is null.")

Exit Sub

End If

'Initialise values to paste at the end of procedure

TotalImbalance = 0

TotalCalculatedBalance = 0

'Check user desired balancing method

If SmallestDate <> BiggestDate Then

UserFormForBalancingOffSheet1.Show

'In case date is only one

Else

DateToWorkOn = BiggestDate

GoTo GeneralisedBalancing

End If

InvalidEntryHandlingPoint:

If Err.Number <> 0 Then

MsgBox ("Invalid value.")

Err.Clear

ws.Cells(3, "X").Calculate

Exit Sub

End If

On Error GoTo 0

'Update date ranges

If Sheet1BalancingMethod = "DATE-RANGE-ONE-BY-ONE" Or Sheet1BalancingMethod = "DATE-RANGE-ALL-AT-ONCE" Then

On Error GoTo InvalidEntryHandlingPoint

SmallestDate = DateValue(InputBox("Enter desired start date"))

BiggestDate = DateValue(InputBox("Enter desired end date"))

On Error GoTo 0

End If

'If desiring ONE-BY-ONE DATE BALANCING

If Sheet1BalancingMethod = "ALL-DATES-ONE-BY-ONE" Or Sheet1BalancingMethod = "DATE-RANGE-ONE-BY-ONE" Then

For DateToWorkOn = SmallestDate To BiggestDate

'In case the date has desired data

If Application.WorksheetFunction.CountIfs(ws.Range("DA6:DA1048576"), ReferenceBook, ws.Range("A6:A1048576"), DateToWorkOn) + Application.WorksheetFunction.CountIfs(ws.Range("DK6:DK1048576"), ReferenceBook, ws.Range("P6:P1048576"), DateToWorkOn) > 0 Then

On Error GoTo InvalidEntryHandlingPoint

OpeningBalance = Evaluate(InputBox("Enter the Opening balance for " & DateToWorkOn))

ObservedBalance = Evaluate(InputBox("Enter the observed closing balance for " & DateToWorkOn))

On Error GoTo 0

DateInFlows = Application.WorksheetFunction.SumIfs(ws.Range("H6:H1048576"), ws.Range("A6:A1048576"), DateToWorkOn, ws.Range("DA6:DA1048576"), ReferenceBook)

DateOutFlows = Application.WorksheetFunction.SumIfs(ws.Range("T6:T1048576"), ws.Range("P6:P1048576"), DateToWorkOn, ws.Range("DK6:DK1048576"), ReferenceBook)

DateDebtors = Application.WorksheetFunction.SumIfs(ws.Range("J6:J1048576"), ws.Range("A6:A1048576"), DateToWorkOn, ws.Range("DA6:DA1048576"), ReferenceBook)

DateCreditors = Application.WorksheetFunction.SumIfs(ws.Range("V6:V1048576"), ws.Range("P6:P1048576"), DateToWorkOn, ws.Range("DK6:DK1048576"), ReferenceBook)

CalculatedBalance = OpeningBalance + DateInFlows - DateDebtors - DateOutFlows + DateCreditors

If CalculatedBalance < ObservedBalance Then

'get the last row

lastRowInIncomesColumn = ws.Cells(ws.Rows.count, "C").End(xlUp).Row

If lastRowInIncomesColumn < 5 Then

lastRowInIncomesColumn = 5

End If

ws.Cells(lastRowInIncomesColumn + 1, "A").Value = DateToWorkOn

ws.Cells(lastRowInIncomesColumn + 1, "C").Value = "Unexplained income"

ws.Cells(lastRowInIncomesColumn + 1, "D").Value = 1

ws.Cells(lastRowInIncomesColumn + 1, "H").Value = ObservedBalance - CalculatedBalance

ws.Cells(lastRowInIncomesColumn + 1, "N").Value = "Bal-b/f=" & OpeningBalance & " " & "Obs.Bal=" & ObservedBalance & " " & "Calc.Bal=" & CalculatedBalance

ws.Cells(lastRowInIncomesColumn + 1, "O").Value = 0

ws.Cells(lastRowInIncomesColumn + 1, "DA").Value = ReferenceBook

ws.Cells(lastRowInIncomesColumn + 1, "A").EntireRow.Calculate

ElseIf CalculatedBalance > ObservedBalance Then

'get the last row

lastRowInExpensesColumn = ws.Cells(ws.Rows.count, "R").End(xlUp).Row

If lastRowInExpensesColumn < 5 Then

lastRowInExpensesColumn = 5

End If

ws.Cells(lastRowInExpensesColumn + 1, "P").Value = DateToWorkOn

ws.Cells(lastRowInExpensesColumn + 1, "R").Value = "Unexplained deficit"

ws.Cells(lastRowInExpensesColumn + 1, "S").Value = 1

ws.Cells(lastRowInExpensesColumn + 1, "T").Value = CalculatedBalance - ObservedBalance

ws.Cells(lastRowInExpensesColumn + 1, "Z").Value = "Bal-b/f=" & OpeningBalance & " " & "Obs.Bal=" & ObservedBalance & " " & "Calc.Bal=" & CalculatedBalance

ws.Cells(lastRowInExpensesColumn + 1, "AA").Value = 0

ws.Cells(lastRowInExpensesColumn + 1, "DK").Value = ReferenceBook

ws.Cells(lastRowInExpensesColumn + 1, "A").EntireRow.Calculate

End If

TotalImbalance = TotalImbalance + (ObservedBalance - CalculatedBalance)

TotalCalculatedBalance = TotalCalculatedBalance + CalculatedBalance

End If

Next DateToWorkOn

'If desiring AT-ONCE BALANCING

ElseIf Sheet1BalancingMethod = "ALL-DATES-ONE-BY-ONCE" Or Sheet1BalancingMethod = "DATE-RANGE-ALL-AT-ONCE" Then

GeneralisedBalancing:

On Error GoTo InvalidEntryHandlingPoint

OpeningBalance = Evaluate(InputBox("Enter the total Opening balance"))

ObservedBalance = Evaluate(InputBox("Enter the total Observed closing balance"))

On Error GoTo 0

DateInFlows = Application.WorksheetFunction.SumIfs(ws.Range("H6:H1048576"), ws.Range("A6:A1048576"), ">=SmallestDate", ws.Range("A6:A1048576"), "<=BiggestDate", ws.Range("DA6:DA1048576"), ReferenceBook)

DateOutFlows = Application.WorksheetFunction.SumIfs(ws.Range("T6:T1048576"), ws.Range("P6:P1048576"), ">=SmallestDate", ws.Range("P6:P1048576"), "<=BiggestDate", ws.Range("DK6:DK1048576"), ReferenceBook)

DateDebtors = Application.WorksheetFunction.SumIfs(ws.Range("J6:J1048576"), ws.Range("A6:A1048576"), ">=SmallestDate", ws.Range("A6:A1048576"), "<=BiggestDate", ws.Range("DA6:DA1048576"), ReferenceBook)

DateCreditors = Application.WorksheetFunction.SumIfs(ws.Range("V6:V1048576"), ws.Range("P6:P1048576"), ">=SmallestDate", ws.Range("P6:P1048576"), "<=BiggestDate", ws.Range("DK6:DK1048576"), ReferenceBook)

CalculatedBalance = OpeningBalance + DateInFlows - DateDebtors - DateOutFlows + DateCreditors

If CalculatedBalance < ObservedBalance Then

'get the last row

lastRowInIncomesColumn = ws.Cells(ws.Rows.count, "C").End(xlUp).Row

If lastRowInIncomesColumn < 5 Then

lastRowInIncomesColumn = 5

End If

ws.Cells(lastRowInIncomesColumn + 1, "A").Value = BiggestDate

ws.Cells(lastRowInIncomesColumn + 1, "C").Value = "Unexplained income"

ws.Cells(lastRowInIncomesColumn + 1, "D").Value = 1

ws.Cells(lastRowInIncomesColumn + 1, "H").Value = ObservedBalance - CalculatedBalance

ws.Cells(lastRowInIncomesColumn + 1, "N").Value = "Bal-b/f=" & OpeningBalance & " " & "Obs.Bal=" & ObservedBalance & " " & "Calc.Bal=" & CalculatedBalance

ws.Cells(lastRowInIncomesColumn + 1, "O").Value = 0

ws.Cells(lastRowInIncomesColumn + 1, "DA").Value = ReferenceBook

ws.Cells(lastRowInIncomesColumn + 1, "A").EntireRow.Calculate

ElseIf CalculatedBalance > ObservedBalance Then

'get the last row

lastRowInExpensesColumn = ws.Cells(ws.Rows.count, "R").End(xlUp).Row

If lastRowInExpensesColumn < 5 Then

lastRowInExpensesColumn = 5

End If

ws.Cells(lastRowInExpensesColumn + 1, "P").Value = BiggestDate

ws.Cells(lastRowInExpensesColumn + 1, "R").Value = "Unexplained deficit"

ws.Cells(lastRowInExpensesColumn + 1, "S").Value = 1

ws.Cells(lastRowInExpensesColumn + 1, "T").Value = CalculatedBalance - ObservedBalance

ws.Cells(lastRowInExpensesColumn + 1, "Z").Value = "Bal-b/f=" & OpeningBalance & " " & "Obs.Bal=" & ObservedBalance & " " & "Calc.Bal=" & CalculatedBalance

ws.Cells(lastRowInExpensesColumn + 1, "AA").Value = 0

ws.Cells(lastRowInExpensesColumn + 1, "DK").Value = ReferenceBook

ws.Cells(lastRowInExpensesColumn + 1, "A").EntireRow.Calculate

End If

TotalImbalance = TotalImbalance + (ObservedBalance - CalculatedBalance)

TotalCalculatedBalance = TotalCalculatedBalance + CalculatedBalance

'CANCEL

ElseIf Sheet1BalancingMethod = "CANCEL" Then

Exit Sub

End If

'Reset balancing method

Sheet1BalancingMethod = ""

ws.Cells(3, "S").Value = TotalCalculatedBalance

ws.Cells(3, "X").Calculate

MsgBox ("Total Calculated cash balance is " & TotalCalculatedBalance)

If TotalImbalance >= 0 Then

MsgBox ("Net unexplained surplus is " & TotalImbalance)

Else

MsgBox ("Net unexplained deficit is " & Right(TotalImbalance, Len(TotalImbalance) - 1))

End If

End If

End Sub


Hope this gets u better motivation to help
 
Upvote 0

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Tetra201 said:
The following mini-sub works as intended:
VBA Code: 
Sub Test()
    Dim SmallestDate As Date
    SmallestDate = #1/3/2024#
    MsgBox WorksheetFunction.CountIfs([B5:B10000], ">=" & SmallestDate)
End Sub
Click to expand...
Thanks but this didn't work for me
 
Upvote 0
Sorry guys for the several spaces in the procedure code above but...tranferring it to the phone caused those issues inexplicably. Can't use PC network right now. Still waiting for help
 
Upvote 0
WOW. Ok maybe this is not 100% correctly formatted, but it is the best I can do for now. I gave up trying to shorten some of the lines (using line continuation characters) part way through. Trying to make dinner at the same time (I'm chief cook and bottle washer as they say). So I can't get back to the original issue for a while. HTH someone in the meantime.
VBA Code: 
Dim OpeningBalance As Double, ObservedBalance As Double, CalculatedBalance As Double
Dim TotalImbalance As Double, TotalCalculatedBalance As Double, DateInFlows As Double
Dim DatelOutFlows As Double, DateDebtors As Double, DateCreditors As Double
Dim SmallestDate As Date, BiggestDate As Date, DateToWorkOn As Date
Dim lastRowInIncomesColumn As Long, lastRowInExpensesColumn As Long
Dim ReferenceBook As String, EnteredValue As String

Set ws = ThisWorkbook.Sheets("sheet1")
Sheet1BalancingMethod = ""

 'get user's desire to balance off the book
Response = MsgBox("You have initiated balancing off of this work-sheet. Do you want to continue?", vbQuestion + vbYesNo, "Confirmation")
If Response = vbYes Then
    'INITIATE DATE ORDER
    ws.Range("BD1:BG1").Calculate
    'get biggest date
    If ws.Cells(1, "BE").Value > ws.Cells(1, "BG").Value Then
        BiggestDate = CDate(ws.Cells(1, "BE").Value)
    Else
        BiggestDate = CDate(ws.Cells(1, "BG").Value)
    End If
    'get smallest date
    If ws.Cells(1, "BD").Value <= ws.Cells(1, "BF").Value And ws.Cells(1, "BD").Value <> 0 Then
        SmallestDate = CDate(ws.Cells(1, "BD").Value)
    ElseIf ws.Cells(1, "BF").Value <= ws.Cells(1, "BD").Value And ws.Cells(1, "BF").Value <> 0 Then
        SmallestDate = CDate(ws.Cells(1, "BF").Value)
    ElseIf ws.Cells(1, "BD").Value >= ws.Cells(1, "BF").Value And ws.Cells(1, "BF").Value = 0 Then
        SmallestDate = CDate(ws.Cells(1, "BD").Value)
    ElseIf ws.Cells(1, "BF").Value >= ws.Cells(1, "BD").Value And ws.Cells(1, "BD").Value = 0 Then
        SmallestDate = CDate(ws.Cells(1, "BF").Value)
    End If
    
    If ws.Cells(1, "BD").Value = 0 And ws.Cells(1, "BF").Value = 0 Then
        SmallestDate = BiggestDate
    End If

    'Set REFERENCE BOOK
    If RemoveRepeatingSpaces(ws.Cells(3, "P").Value) = "" Then
        ReferenceBook = InputBox("Enter the reference book U want to balance.")
    Else
        ReferenceBook = RemoveRepeatingSpaces(ws.Cells(3, "P").Value)
    End If

    'Check for presence of multiple reference books
    If Application.WorksheetFunction.CountIfs(ws.Range("DA6:DA1048576"), ReferenceBook) _ 
       & + Application.WorksheetFunction.CountIfs(ws.Range("DK6:DK1048576"), ReferenceBook) _ 
       & <> Application.WorksheetFunction.CountA(ws.Range("DA6:DA1048576")) _ 
       & + Application.WorksheetFunction.CountA(ws.Range("DK6:DK1048576")) Then
           ReferenceBook = RemoveRepeatingSpaces(InputBox("Multiple reference books found. Please confirm the reference book which you are balancing off."))
    End If

    If Application.WorksheetFunction.CountIfs(ws.Range("DA6:DA1048576"), ReferenceBook) _
       & + Application.WorksheetFunction.CountIfs(ws.Range("DK6:DK1048576"), ReferenceBook) = 0 Then
          MsgBox ("The reference book provided has no entries made under it.")
          Exit Sub
    End If

    If RemoveRepeatingSpaces(ReferenceBook) = "" Then
        MsgBox ("Reference book is null.")
        Exit Sub
    End If

    'Initialise values to paste at the end of procedure
    TotalImbalance = 0
    TotalCalculatedBalance = 0

    'Check user desired balancing method
    If SmallestDate <> BiggestDate Then
        UserFormForBalancingOffSheet1.Show
        'In case date is only one
    Else
        DateToWorkOn = BiggestDate
        GoTo GeneralisedBalancing
    End If

    InvalidEntryHandlingPoint:
    If Err.Number <> 0 Then
        MsgBox ("Invalid value.")
        Err.Clear
        ws.Cells(3, "X").Calculate
        Exit Sub
    End If

    On Error GoTo 0
    'Update date ranges
    If Sheet1BalancingMethod = "DATE-RANGE-ONE-BY-ONE" Or Sheet1BalancingMethod = "DATE-RANGE-ALL-AT-ONCE" Then
        On Error GoTo InvalidEntryHandlingPoint
        SmallestDate = DateValue(InputBox("Enter desired start date"))
        BiggestDate = DateValue(InputBox("Enter desired end date"))
        On Error GoTo 0
    End If

    'If desiring ONE-BY-ONE DATE BALANCING
    If Sheet1BalancingMethod = "ALL-DATES-ONE-BY-ONE" Or Sheet1BalancingMethod = "DATE-RANGE-ONE-BY-ONE" Then
        For DateToWorkOn = SmallestDate To BiggestDate
            'In case the date has desired data
            If Application.WorksheetFunction.CountIfs(ws.Range("DA6:DA1048576"), ReferenceBook, _
               & ws.Range("A6:A1048576"), DateToWorkOn) + Application.WorksheetFunction.CountIfs(ws.Range("DK6:DK1048576"), _
               & ReferenceBook, ws.Range("P6:P1048576"), DateToWorkOn) > 0 Then
                    On Error GoTo InvalidEntryHandlingPoint
                    OpeningBalance = Evaluate(InputBox("Enter the Opening balance for " & DateToWorkOn))
                    ObservedBalance = Evaluate(InputBox("Enter the observed closing balance for " & DateToWorkOn))
                    On Error GoTo 0
                    DateInFlows = Application.WorksheetFunction.SumIfs(ws.Range("H6:H1048576"), _
                       ws.Range("A6:A1048576"), DateToWorkOn, ws.Range("DA6:DA1048576"), ReferenceBook)
                    DateOutFlows = Application.WorksheetFunction.SumIfs(ws.Range("T6:T1048576"), _
                    & ws.Range("P6:P1048576"), DateToWorkOn, ws.Range("DK6:DK1048576"), ReferenceBook)
                    DateDebtors = Application.WorksheetFunction.SumIfs(ws.Range("J6:J1048576"), ws.Range("A6:A1048576"), _ 
                       DateToWorkOn, ws.Range("DA6:DA1048576"), ReferenceBook)
                    DateCreditors = Application.WorksheetFunction.SumIfs(ws.Range("V6:V1048576"), ws.Range("P6:P1048576"), _
                       DateToWorkOn, ws.Range("DK6:DK1048576"), ReferenceBook)
                    CalculatedBalance = OpeningBalance + DateInFlows - DateDebtors - DateOutFlows + DateCreditors
                    If CalculatedBalance < ObservedBalance Then
                        'get the last row
                        lastRowInIncomesColumn = ws.Cells(ws.Rows.count, "C").End(xlUp).Row
                        If lastRowInIncomesColumn < 5 Then
                            lastRowInIncomesColumn = 5
                        End If
                        ws.Cells(lastRowInIncomesColumn + 1, "A").Value = DateToWorkOn
                        ws.Cells(lastRowInIncomesColumn + 1, "C").Value = "Unexplained income"
                        ws.Cells(lastRowInIncomesColumn + 1, "D").Value = 1
                        ws.Cells(lastRowInIncomesColumn + 1, "H").Value = ObservedBalance - CalculatedBalance
                        ws.Cells(lastRowInIncomesColumn + 1, "N").Value = _
                           & "Bal-b/f=" & OpeningBalance & " " & "Obs.Bal=" & ObservedBalance & " " & "Calc.Bal=" & CalculatedBalance
                        ws.Cells(lastRowInIncomesColumn + 1, "O").Value = 0
                        ws.Cells(lastRowInIncomesColumn + 1, "DA").Value = ReferenceBook
                        ws.Cells(lastRowInIncomesColumn + 1, "A").EntireRow.Calculate
                    ElseIf CalculatedBalance > ObservedBalance Then
                        'get the last row
                        lastRowInExpensesColumn = ws.Cells(ws.Rows.count, "R").End(xlUp).Row
                        If lastRowInExpensesColumn < 5 Then
                            lastRowInExpensesColumn = 5
                        End If
                        ws.Cells(lastRowInExpensesColumn + 1, "P").Value = DateToWorkOn
                        ws.Cells(lastRowInExpensesColumn + 1, "R").Value = "Unexplained deficit"
                        ws.Cells(lastRowInExpensesColumn + 1, "S").Value = 1
                        ws.Cells(lastRowInExpensesColumn + 1, "T").Value = CalculatedBalance - ObservedBalance
                        ws.Cells(lastRowInExpensesColumn + 1, "Z").Value = "Bal-b/f=" & OpeningBalance & " " & "Obs.Bal=" & ObservedBalance & " " & "Calc.Bal=" & CalculatedBalance
                        ws.Cells(lastRowInExpensesColumn + 1, "AA").Value = 0
                        ws.Cells(lastRowInExpensesColumn + 1, "DK").Value = ReferenceBook
                        ws.Cells(lastRowInExpensesColumn + 1, "A").EntireRow.Calculate
                    End If
                TotalImbalance = TotalImbalance + (ObservedBalance - CalculatedBalance)
                TotalCalculatedBalance = TotalCalculatedBalance + CalculatedBalance
            End If

        Next DateToWorkOn

    'If desiring AT-ONCE BALANCING
    ElseIf Sheet1BalancingMethod = "ALL-DATES-ONE-BY-ONCE" Or Sheet1BalancingMethod = "DATE-RANGE-ALL-AT-ONCE" Then
        GeneralisedBalancing:
        On Error GoTo InvalidEntryHandlingPoint
        OpeningBalance = Evaluate(InputBox("Enter the total Opening balance"))
        ObservedBalance = Evaluate(InputBox("Enter the total Observed closing balance"))
        On Error GoTo 0
        DateInFlows = Application.WorksheetFunction.SumIfs(ws.Range("H6:H1048576"), ws.Range("A6:A1048576"), ">=SmallestDate", ws.Range("A6:A1048576"), "<=BiggestDate", ws.Range("DA6:DA1048576"), ReferenceBook)
        DateOutFlows = Application.WorksheetFunction.SumIfs(ws.Range("T6:T1048576"), ws.Range("P6:P1048576"), ">=SmallestDate", ws.Range("P6:P1048576"), "<=BiggestDate", ws.Range("DK6:DK1048576"), ReferenceBook)
        DateDebtors = Application.WorksheetFunction.SumIfs(ws.Range("J6:J1048576"), ws.Range("A6:A1048576"), ">=SmallestDate", ws.Range("A6:A1048576"), "<=BiggestDate", ws.Range("DA6:DA1048576"), ReferenceBook)
        DateCreditors = Application.WorksheetFunction.SumIfs(ws.Range("V6:V1048576"), ws.Range("P6:P1048576"), ">=SmallestDate", ws.Range("P6:P1048576"), "<=BiggestDate", ws.Range("DK6:DK1048576"), ReferenceBook)
        CalculatedBalance = OpeningBalance + DateInFlows - DateDebtors - DateOutFlows + DateCreditors
        If CalculatedBalance < ObservedBalance Then
            'get the last row
            lastRowInIncomesColumn = ws.Cells(ws.Rows.count, "C").End(xlUp).Row
            If lastRowInIncomesColumn < 5 Then
                lastRowInIncomesColumn = 5
            End If
            ws.Cells(lastRowInIncomesColumn + 1, "A").Value = BiggestDate
            ws.Cells(lastRowInIncomesColumn + 1, "C").Value = "Unexplained income"
            ws.Cells(lastRowInIncomesColumn + 1, "D").Value = 1
            ws.Cells(lastRowInIncomesColumn + 1, "H").Value = ObservedBalance - CalculatedBalance
            ws.Cells(lastRowInIncomesColumn + 1, "N").Value = "Bal-b/f=" & OpeningBalance & " " & "Obs.Bal=" & ObservedBalance & " " & "Calc.Bal=" & CalculatedBalance
            ws.Cells(lastRowInIncomesColumn + 1, "O").Value = 0
            ws.Cells(lastRowInIncomesColumn + 1, "DA").Value = ReferenceBook
            ws.Cells(lastRowInIncomesColumn + 1, "A").EntireRow.Calculate
        ElseIf CalculatedBalance > ObservedBalance Then
            'get the last row
            lastRowInExpensesColumn = ws.Cells(ws.Rows.count, "R").End(xlUp).Row
            If lastRowInExpensesColumn < 5 Then
                lastRowInExpensesColumn = 5
            End If
            ws.Cells(lastRowInExpensesColumn + 1, "P").Value = BiggestDate
            ws.Cells(lastRowInExpensesColumn + 1, "R").Value = "Unexplained deficit"
            ws.Cells(lastRowInExpensesColumn + 1, "S").Value = 1
            ws.Cells(lastRowInExpensesColumn + 1, "T").Value = CalculatedBalance - ObservedBalance
            ws.Cells(lastRowInExpensesColumn + 1, "Z").Value = "Bal-b/f=" & OpeningBalance & " " & "Obs.Bal=" & ObservedBalance & " " & "Calc.Bal=" & CalculatedBalance
            ws.Cells(lastRowInExpensesColumn + 1, "AA").Value = 0
            ws.Cells(lastRowInExpensesColumn + 1, "DK").Value = ReferenceBook
            ws.Cells(lastRowInExpensesColumn + 1, "A").EntireRow.Calculate
        End If
    TotalImbalance = TotalImbalance + (ObservedBalance - CalculatedBalance)
    TotalCalculatedBalance = TotalCalculatedBalance + CalculatedBalance
        'CANCEL
    ElseIf Sheet1BalancingMethod = "CANCEL" Then
        Exit Sub
    End If

    'Reset balancing method
    Sheet1BalancingMethod = ""
    ws.Cells(3, "S").Value = TotalCalculatedBalance
    ws.Cells(3, "X").Calculate
    MsgBox ("Total Calculated cash balance is " & TotalCalculatedBalance)

    If TotalImbalance >= 0 Then
        MsgBox ("Net unexplained surplus is " & TotalImbalance)
    Else
        MsgBox ("Net unexplained deficit is " & Right(TotalImbalance, Len(TotalImbalance) - 1))
    End If

End If

End Sub
 
Upvote 1
Try following this format and let us know how you go:

VBA Code: 
DateInFlows = Application.WorksheetFunction.SumIfs(ws.Range("H6:H1048576"), _
                ws.Range("A6:A1048576"), ">=" & CLng(SmallestDate), _
                ws.Range("A6:A1048576"), "<=" & CLng(BiggestDate), _
                ws.Range("DA6:DA1048576"), ReferenceBook)
 
Upvote 1
Solution
Alex Blakenburg said:
Try following this format and let us know how you go:

VBA Code: 
DateInFlows = Application.WorksheetFunction.SumIfs(ws.Range("H6:H1048576"), _
                ws.Range("A6:A1048576"), ">=" & CLng(SmallestDate), _
                ws.Range("A6:A1048576"), "<=" & CLng(BiggestDate), _
                ws.Range("DA6:DA1048576"), ReferenceBook)
Click to expand...
This has given the desired outcome. Thanks a lot but could you please explain why it's necessary adding clng
 
Upvote 0
VBA generally works with US Date format. By using CLng we are working with the underlying number and that takes the date format issue out of the equation.
If you want to put the cell value straight into the formula using .Value2 will also avoid the formatting issue.

The only other way I managed to make it work is to feed the date into the formula using the US date format
eg
Rich (BB code): 
cnt = WorksheetFunction.CountIfs(Range("$A$7:$A$15"), ">=" & Format(dt, "mm/dd/yyyy"))
 
Upvote 1
Alex Blakenburg said:
VBA generally works with US Date format. By using CLng we are working with the underlying number and that takes the date format issue out of the equation.
If you want to put the cell value straight into the formula using .Value2 will also avoid the formatting issue.

The only other way I managed to make it work is to feed the date into the formula using the US date format
eg
Rich (BB code): 
cnt = WorksheetFunction.CountIfs(Range("$A$7:$A$15"), ">=" & Format(dt, "mm/dd/yyyy"))
Click to expand...
Ooh! Thanks for the wonderful info.
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Solved
Challenge with countif limitations
2
Replies
14
Views
162
PatrickK777777
P
W
  • Solved
Countif multiple columns
Replies
2
Views
161
windoz
W
Knockoutpie
  • Question
Excel Formula to check if two strings exist in the same row on a sheet without specifying the column
Replies
2
Views
239
Knockoutpie
Knockoutpie
E
  • Solved
COUNTIF and SUMIF formula with multiple criteria
Replies
2
Views
147
excelnewb221
E
J
  • Solved
Simple COUNTIF formulas not working
Replies
6
Views
409
Fluff
Fluff

Forum statistics

Threads
1,215,202
Messages
6,123,625
Members
449,109
Latest member
Sebas8956

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top