Posts by r_hind

    Basically iteration is looping through calculations until a condition is met.

    Most calculations don't require iteration but consider this:-

    Price = Costs plus Margin BUT:-
    Costs = Product Cost + Distribution Cost + Overhead
    Overhead = Price * 12%

    How do you determine what the overhead amount is without knowing the price? How do you know the price without knowing the overhead amount?

    Very simply excel iterates (loops) changing the amounts until the conditiion (that overhead is 12% of sales) is true.

    Yes is better to have a clean calculation...but frankly spreadsheets usually help people calculate something they might not otherwise and sometimes the time and effort of the clean calculation isn't worth it.

    Hope this explains it.

    Yes excel is always re-calculating ...although even that can be switched off. It only iterates if set to.

    Most people would use excel with iteration on to make calculations easier...but if you want to speed things up and be more particular you might switch iteration off and pay more attention to formulae.

    In a simple costing scenario you might make an allowance for overheads being equal to say 12% of Sales...but since Sales includes all costs and profit the calculation forces a circular reference. Of course the calculation can be done without forcing a circular reference but most wouldn't do that.

    Neale and Derk really know what they're talking about....but personally I'd leave iteration switched on and see if everything works ok.

    I usually just set the iterations to 100 but for no scientific reason just usually works for the level of accuracy I need.

    You do have iterations turned on don't you?

    While calculation will be sped up if you don't have circular references sometimes they're unavoidable.

    But they won't work without iteration turned on.

    Check out Tools, Options then the calculation tab.

    you could of course read the range into an array, transfer (in reverse order into another array then copy back into the range

    Public vba_OldArray(), vba_NewArray(), ArraySize As Integer

    Public Sub Swapper()
    vba_OldArray() = Range("OldArray").Value
    vba_NewArray() = Range("OldArray").Value

    ArraySize = UBound(vba_OldArray, 1)

    'vba_NewArray(ArraySize, 1)

    For X = 1 To ArraySize
    vba_NewArray(X, 1) = vba_OldArray((ArraySize + 1) - X, 1)
    Next X

    Range("OldArray").Value = vba_NewArray()

    End Sub

    I frequently run historical queries on large sets of data.

    In my scenario...I create a file per month per acct. Each file contains significant quantities of transactions.

    I use standard naming conventions like say AAA_BBBBB_YYYY_MM

    where AAA = 3 letter abbreviation for Group name for accounts (several different accounts in one company)

    BBBBB = Accunt number
    YYYY = Year
    MM = Month

    By establishing a text variable representing the filenames I'm searching for (eg AAA_?????_2003_??.csv for all accounts in Group Name AAA for any month in the Year 2003) and feeding this to the .FileName property of Application.FileSearch...I can then search through all the files in a folder and produce a list of all the files that are to be searched.

    Then I establish the field number for which I'm going to search given a specific criteria.

    The routine works something like this:-
    Open each file (from the list)
    Apply an autofilter - search based on certain field and specified criteria.
    Copy filtered records
    Paste returned records (append) to a sheet or store into an array and then when complete transfer array to sheet.

    This technique is very fast, can work on countless files (each containing thousands of records) and works well as long as the list of returned records is less than 65,536 (the excel limit in terms of rows in a worksheet).

    If you need more help or sample code let me know.

    Surely you're going to use averages of some sort?

    Either: current month (number) + 7500/(total kms to date/ total number of months)


    If the historical stats show seasonal trends you may wish to allocate a consistent (or average) number of kms for specific months.

    Is this sort of information you're looking for? If not perhaps you're better posting an example spreadsheet illustrating what you're trying to do.

    Excel has built in Registry functions. I use the following code to get a value from the registry and increment it. Note: I'm using Excel 2002 and I'm not certain whether the same function works in earl

    Sub RegistryAmend()

    Dim MySettings As Variant
    ' Place some settings in the registry.
    NxtNo = GetSetting(appname:="TaskNumber", section:="NextNumber", _
    key:="Next", Default:="25")
    NxtNo = NxtNo + 1
    SaveSetting "TaskNumber", "NextNumber", "Next", NxtNo

    ActiveCell.Value = GetSetting(appname:="TaskNumber", section:="NextNumber", _
    key:="Next", Default:="25")

    End Sub

    You can send a whole array to a named range in one hit!

    The following give a clue...

    Range("[DestinationRangeName"]).value = SourceArray()

    I would usually do this by applying a Range Name to the Source Area (if the data is continuous - no break in rows or columns then select using ActiveCell.CurrentRegion.Select).

    Names.Add Name:="CurrRegn", RefersTo:=ActiveCell.CurrentRegion

    SourceArray() = Range("CurrRegn").value

    I've made some changes to the previously posted spreadsheet....note that this illustrates two different methods...the formulas are not yet complete...see grey cells in Column BC as an indication of the sort of lookup table formula thart could be used.

    Note the use of dynamically named ranges.

    I'm not certain that a pivot table will give you the sort of structure or layout you're looking for:-

    How about posting a sample spreadsheet showing the inputs and how the individual sheets might look (as you would want)....will be a lot easier to keep the solution relevant that way