Results 1 to 14 of 14
Thread: Formula's > VBA (2003)

20080409, 19:32 #1
 Join Date
 Sep 2007
 Posts
 1,203
 Thanks
 0
 Thanked 0 Times in 0 Posts
Formula's > VBA (2003)
Hi,
Please see attached workbook which is a small sample of data. The scenario is: The top of the worksheet contains debits that we need to clear against the credits at the bottom of the worksheet. At any given time, there may be upto 10,000 credits outstanding. There are 2 matching criteria's > Column N (account number and value) and Column P (unique item ref and value). Providing there are the same amount of debits and credits identical, then a match is created in column R.
The three columns of formulas (which are different at the top to the bottom) provide exactly what I need. However,my problem is that the sheer volume of data and formulas cause the workbook to recalculate every time a cell of data is adjusted. This can take 20 seconds every time, making the book unworkable.
Would it be possible to recreate the three formula columns in some sort of vba loop??
Any pointers greatfully received.
Thanks
Nath

20080409, 20:41 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Formula's > VBA (2003)
How would a macro know where the data ends? You have some empty rows in both areas.

20080409, 21:00 #3
 Join Date
 Aug 2004
 Location
 Connecticut, USA
 Posts
 816
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Formula's > VBA (2003)
You can turn off the auto calculations while you enter the formulas.
Goto Tools /Option... then the Calculations tab
Change from Automatic to Manual
Reverse the process when you are done with the formulas.
Or you could assign macro to a button that would turn the calculation on or off for you.
<pre>Sub CalOnOff()
If Application.Calculation = xlCalculationManual Then
Application.Calculation = xlCalculationAutomatic
MsgBox "calculations are set to AUTOMATIC"
Else
Application.Calculation = xlCalculationManual
MsgBox "calculations are set to MANUAL"
End If
End Sub
</pre>

20080409, 21:29 #4
 Join Date
 Sep 2007
 Posts
 1,203
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Formula's > VBA (2003)
mbarron: We use this option currently, but unfortunately this causes other problems. There is a constant stream of inputting / deleting and updating of data and the 'reconciliation' has to balance at all times. There are many other formula's in the sheet that have to update as the data is altered. Therefore turning calculation to manual is not an option because mistakes can be made when set to manual which can cause a reconciliation imbalance. (Unless only columns N,P&R could be set to manual whilst all the rest of the worksheet stayed as automatic?)
Hans: Could named cells be used at the top and bottom of the debits (rows 10 & 101) and then the same for credits (rows 112 & 890) and looped formula's be input between the named cells? There will always be blank rows at the bottom of each section to allow the input of new data. But the formula's are based on IF there is data in certain cells within those sections.
Thanks
Nath

20080409, 22:09 #5
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Formula's > VBA (2003)
If setting calculation to manual is not an option because the data have to be uptodate all the time, I don't see how VBA code would be better. You'd need to run code whenever the user changes a value to recalculate the values, and VBA is generally less efficient than formulas. Moreover, running code each time the user changes a value effectively disables the Undo feature, so mistakes by the user would be much more problematic.

20080409, 22:25 #6
 Join Date
 Sep 2007
 Posts
 1,203
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Formula's > VBA (2003)
Hans,
The whole worksheet (except columns N,P&R) need to automatically recalculate as the task of inputting / breaking down / updating the data goes on, which is quite a lengthy process. What I need is to be able to populate columns N,P&R to create the matches 'AFTER' all the previous task is completed. For example, there is normally about an hour (or 2's) work to do 'inputting / breaking down / updating' the data, once all complete, create matches.
Thanks,
Nath

20080409, 23:54 #7
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Formula's > VBA (2003)
I started writing a macro but I'm running into a problem. The numbers in the Acc No columns have so many decimal places that they're pushing the limits of what Excel and VBA can handle. The result of concatenating with these values using VBA is sometimes different from the result of the formula. Why do these numbers have so many decimal places?

20080410, 00:08 #8
 Join Date
 Sep 2007
 Posts
 1,203
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Formula's > VBA (2003)
Thankyou Hans, I appreciate your efforts! The live data account no's are 16 digits in length with no decimals. The data that i have provided in the sample is dummy data, I used =RAND().
Thanks
Nath

20080410, 00:33 #9
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Formula's > VBA (2003)
Try the code in the attached text file.

20080410, 01:09 #10
 Join Date
 Sep 2007
 Posts
 1,203
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Formula's > VBA (2003)
Thankyou Hans, the code looks to be on the right track. Though I am getting a debit match of 87 (£19,181.21) against a credit match of 56 (£11,035.11). Also, the empty rows are reporting Match?. I will attempt to understand the code and find the fault.
Massive Thanks,
Nath

20080410, 01:27 #11
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Formula's > VBA (2003)
I haven't attempted to understand your formulas, I have just written code that does the same (I hope).

20080410, 01:39 #12
 Join Date
 Sep 2007
 Posts
 1,203
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Formula's > VBA (2003)
Hans,
I have studied your code and have a (basic) understanding of what is happening, though I am unable to find the fault. There are 2 things. There should be an identical amount of matched credits and debits (48 @ £11,035.11). The blank rows (preferably) should not report match. The credits are matching ok, the fault appears to lie with the debits. I will continue to try and find the error. Attached column Q in red shows the reporting error's.
Thanks
Nath

20080410, 01:59 #13
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Formula's > VBA (2003)
Does the attached version work better?

20080410, 02:10 #14
 Join Date
 Sep 2007
 Posts
 1,203
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Formula's > VBA (2003)
Hans,
BINGO!! 100% perfect!! Works in 'live' aswell. I can't thank you enough!!
Cheers.....
Nathan