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🙏
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Sort by date Sort by votes
is smallestdate a named range in your workbook or a variable in your CODE.

It may be a good idea to post your code where you declare and use the function and variable(s).
 
Upvote 0
try ">=#" & SmallestDate & "#"
The single quote in your first example ought to be a problem for sure.

More of the code would probably help, especially the Dim statements. Might as well post the whole procedure if you do (please use vba button on posting toolbar and paste between the resulting code tags).
 
Upvote 0
awoohaw said:
is smallestdate a named range in your workbook or a variable in your CODE.

It may be a good idea to post your code where you declare and use the function and variable(s).
Click to expand...
Date variable from
Dim SmallestDate as date

Entire Procedure quite long and am not familiar with this platform.
 
Upvote 0
as @Micron wrote... copy your procedure... and use the "VBA" quoting button above in each POST editing bar.

1712177412067.png
 
Upvote 0
awoohaw said:
is smallestdate a named range in your workbook or a variable in your CODE.

It may be a good idea to post your code where you declare and use the function and variable(s).
Click to expand...
Sorry single quote does not exist. It's meant to be ">=" & SmallestDate or ">=SmallestDate". Let me put the # and see the outcome.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
PatrickK777777 said:
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
Click to expand...
Tried to bring the code here and this is the result. Note that I've used ">=" & smallestdate before using ">=smallestdate". The result appears to satisfy the idea that there's no match.
 
Upvote 0
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?
I'm feeling magnanimous. If anyone wants to wait, I will fix it and re-post.
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Solved
Challenge with countif limitations
2
Replies
14
Views
230
PatrickK777777
P
W
  • Solved
Countif multiple columns
Replies
2
Views
192
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
267
Knockoutpie
Knockoutpie
I
  • Question
CountIF alternative for performance improvements
2 3
Replies
21
Views
419
ItalianPlatinum
I
E
  • Solved
COUNTIF and SUMIF formula with multiple criteria
Replies
2
Views
206
excelnewb221
E

Forum statistics

Threads
1,215,999
Messages
6,128,193
Members
449,431
Latest member
Taekwon

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