Formula inserted in VBA produces the wrong results.

[myfathersson]

Well here's one for the books.

I have a VBA routine that inserts a formula into a cell. When I step through the routine the formula produces the correct results, but when I run the routine without stepping through it, incorrect results are produced.

The formula is designd to sum the values in a specific cell across multiple sheets. The sheets are named with prefixes identifying the job category. In Excel, if you enter a wildcard formula as follows, Excel replaces the wildcard with a range of sheet names.

=sum('Prefix*'!k22)

For example the formula

=sum('SWS(I)-*'!k22)

will cause Excel to produce a formula something like

=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

=sum('SWS(I)-JBloggins'!k22)

Here's my 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.

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 of 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 multiple sheets. It is these cross-sheet sum formulas that I'm trying to generate to sum the sheets in stored in the workbook. Note: the names and number of timesheet worksheets varies 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.

 

[bradhog]

I think you have tried all the possible solutions but you need to check again the code if any tag is remained open. Also check if there is any overwriting. Sometimes these small things tend to give errors even in a correct formula or code.....