Results 1 to 14 of 14
  1. #1
    Bronze Lounger
    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 re-create the three formula columns in some sort of vba loop??

    Any pointers greatfully received.

    Thanks
    Nath
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Formula's > VBA (2003)

    How would a macro know where the data ends? You have some empty rows in both areas.

  3. #3
    5 Star Lounger
    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>


  4. #4
    Bronze Lounger
    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

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Formula's > VBA (2003)

    If setting calculation to manual is not an option because the data have to be up-to-date 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.

  6. #6
    Bronze Lounger
    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 re-calculate 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

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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?

  8. #8
    Bronze Lounger
    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

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Formula's > VBA (2003)

    Try the code in the attached text file.
    Attached Files Attached Files

  10. #10
    Bronze Lounger
    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

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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).

  12. #12
    Bronze Lounger
    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
    Attached Files Attached Files

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Formula's > VBA (2003)

    Does the attached version work better?
    Attached Files Attached Files

  14. #14
    Bronze Lounger
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •