Results 1 to 3 of 3

Thread: VBA Inserted Formula is incorrect when program is run.

  1. #1

    VBA Inserted Formula is incorrect when program is run.



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Well here's one for the books.

    Excel Version: 2007

    I have a VBA routine that inserts a formula into a cell. When I step through the routine the formula is resolved and inserted correctly, but when I run the routine without stepping through it, the formula is not correctly resolved and/or inserted.

    The formula is designed to sum the value in a specific cell across multiple sheets. The sheets are named with prefixes (e.g., the job category). In Excel, if you enter a wildcard formula as follows, Excel replaces the wildcard with a range of sheet names matching the search term.

    Code:

    Code:
    =sum('Prefix*'!k22)
    For example the formula

    Code:

    Code:
    =sum('SWS(I)-*'!k22)
    will cause Excel to resolve the wildcard and produce a formula something like

    Code:

    Code:
    =sum('SWS(I)-BJones:SWS(I)-SRogers, SWS(I)-JBloggins'!k22)
    Which is what I get when I step through the routine. But when I run the routine I get

    Code:

    Code:
    =sum('SWS(I)-JBloggins'!k22)
    Which means I only get the total from one sheet instead of all sheets with the prefix "SWS(I)-".

    Here's my routine.

    Code:

    Code:
    Private Sub UpdateSumTSandByCatSheets(oDic As Object)
    
            Dim lstoByCatTable As ListObject, rwoTableRow As ListRow, arrKeys As Variant, idx As Integer, strFormula As String
            Application.Calculation = xlCalculationManual
    
            'Update the summary_timesheet
            For Each c In wbTemplate.Worksheets("summary_timesheet").Range("hoursworkedblock")
                strFormula = WorksheetFunction.Substitute(c.Formula, "#REF", "'*-*'")
                c.Formula = strFormula
            Next c
            
            'Update the by_category sheet
            'Instantiate table object and table row object
            Set lstoByCatTable = wbTemplate.Worksheets("by_category").ListObjects("ByCatTable")
            Set rwoTableRow = lstoByCatTable.ListRows(1)
            'Dump the key values from oDic into an array.
            arrKeys = oDic.keys
            
            'the ByCatTable in the by_category sheet in the template has one blank row by default.  Fill in this first row with the first
            'item from oDic.  Note: the oDic key is the long form category name and the item value is the short form.
            'The value put in the Days column of the ByCatTable is a formula set to sum all the Total Days (cell k22)
            'from all sheets with a prefix of a given short cat name.
            rwoTableRow.Range(1, BYCATTABLECATCOL).Value = arrKeys(0)
            strFormula = "=sum('" & oDic.Item(arrKeys(0)) & "-*'!k22)"
            rwoTableRow.Range(1, BYCATTABLEDAYSCOL).Formula = strFormula
            
            'Loop through the oDic key array, adding and filling in table rows for each item in the array.
            For idx = 1 To UBound(arrKeys) 'we start at 1 because item 0 (the first item) was stored previously.
                Set rwoTableRow = lstoByCatTable.ListRows.Add 'add a new table row at the bottom of the table
                rwoTableRow.Range(1, BYCATTABLECATCOL).Value = arrKeys(idx) 'put the long cat name in the Category column
                rwoTableRow.Range(1, BYCATTABLEDAYSCOL).Formula = "=sum('" & oDic.Item(arrKeys(idx)) & "-*'!k22)" 'put the sum formula in the Days column.
            Next idx
    
            Application.Calculation = xlCalculationAutomatic
            
    End Sub
    Things I've tried:
    - using application.wait to slow down the process thinking the auto calc process is too slow to keep up with the program.
    - turn off auto calc before the formula generation then turn it back on again after all is complete.
    - building the formula in a string first before using it in the statement to insert the formula.
    - changing the search criteria (e.g., exclude the "-" and only qualifying on the "SWS(I)".
    - changing the "-" to a "_".

    Some Background to explain the why's and the wherefores:
    My company has recently implemented an online time tracking system. Our clients each require timesheets to be provided in the billing package we send them; these sheets must be in the format specified by the client. The time tracking system does not produce the timesheets in the required format so I wrote a program that uses a CSV dump from the tracking system to generate the timesheets in the required format. The timesheets are all put into one workbook (one workbook for each client) and in the case here, the workbook also contains summary sheets the have sum formulas to sum across the multiple timesheets. It is these cross-sheet sum formulas that I'm trying to generate to sum the sheets stored in the workbook. Note: the names, number and order of the timesheet worksheets vary from month to month, which is the reason I need to regen the formula each time.

    Any ideas as to what is going on? Anyone see any gotchyas in the code or anywhere?

    Of course this is the last routine I had to write...it was all going so well.

  2. #2
    Conjurer Simi's Avatar
    Join Date
    Feb 2012
    Location
    Utah, USA
    Posts
    187
    Articles
    0
    I am not sure how to answer your question, however I may have a different way to get the desired result.
    Instead of trying to update a formula on your master sheet to include every timesheet. You could just use a variable to store the sum'd value and then step from sheet to sheet by sheet index number instead of by sheet name.

  3. #3
    Thanks simi. I have reprogrammed to total in
    memory then dump the totals into the sheet. I never did find a solution or reason for the problem.

Posting Permissions

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