something that will help me with reconcilations.....

  • Thread starter Thread starter sokevin
  • Start date Start date
S

sokevin

Hi....


when i do reconciliations..

i have a Debit side with single large number like 1,000,456.45 and on
the Credit side i have many smaller numbers like 45,000; 10,000 ;3,456
etc etc



is there a program/macro where i can input 1,000,456.45 (as the control
total) then do a search for possible combinations of numbers on the
Credit side which add up to 1,000,456,45.


thanks :)
 
Here is some basic code. As written it checks 3 number combination, bu
can be extended by adding additional loops for n4 etc. It assumes
list of numbers in column a and a check total in cell B1.

Depending on the length of data, this can take a long time to run. I
you add more loops, even with a relatively small list you are gettin
into an "overnight" run time. Some time can be saved by sorting th
number list. eg. if you have a negative checktotal then sort negativ
numbers to the top of the list.

'-----------------------------------------------------------------
'-brianb 2000
Sub basic_loop3()
Dim CheckNum As Double
Dim TestNum As Double
Dim Total As Double
Dim RowCount As Long
'-------------------------------------------------
CheckNum = ActiveSheet.Range("B1").Value
RowCount = ActiveSheet.Range("A1").End(xlDown).Row
'- main loops
For n1 = 1 To RowCount
For n2 = 2 To RowCount
For n3 = 3 To RowCount

'------------------------------------------------------------------
Application.StatusBar = " 3 Numbers " & n1 & ":" & n2
":" & n3
Total = ActiveSheet.Cells(n1, 1).Value _
+ ActiveSheet.Cells(n2, 1).Value _
+ ActiveSheet.Cells(n3, 1).Value
'- CHECK
If Abs(CheckNum - Total) < 1 Then
rsp = MsgBox("Numbers found : TOTAL = " & CheckNu
& vbCr _
& ActiveSheet.Cells(n1, 1).Value & vbCr _
& ActiveSheet.Cells(n2, 1).Value & vbCr _
& ActiveSheet.Cells(n3, 1).Value & vbCr, , "NUMBER
FOUND")
End
End If

'-------------------------------------------------------------------
Next n3
Next n2
Next n1
'--------------------
MsgBox ("No result found.")
Application.StatusBar = False
End Sub
'------------------------------------------------------------
 
Back
Top