problem with transpose array in VBA

[BuglerX]

*Attached is an access datase in zip format. Hold down shift when running as there is a form that runs on startup.*

I have a database that I use to generate excel spreadsheets from sql statements based on date. For some reason It woks with some data but not other data. Here is the scenario:

Choose dates 11/6/2011 to 11/9/2011. Exports perfectly.
Choose dates anything after 11/9/2011 and it crashes with unable to set the FormulaArray property of the Range class.

Here is a snippet:

Set objExcel = CreateObject("Excel.Application")
    objExcel.Workbooks.Add
    Set objResultsWorkbook = objExcel.Workbooks(objExcel.Workbooks.Count)
    Set objResultsSheet = objResultsWorkbook.Worksheets("Sheet1")
    Set objResultsRange = objResultsSheet.Range("A2:E" & 2 + UBound(varResults, 2))
    objResultsRange.FormulaArray = objExcel.Transpose(varResults)
    objResultsSheet.Range("A1").Value = "Item Sold"

Second line from bottom.objResultsRange.FormulaArray = objExcel.Transpose(varResults) is where it is crashing on dates after 11/9/2011

I'm thinking it's an item in 1 of the fields I'm pulling that is crashing the transpose array. Are there any restrictions to characters in an array?

All suggestions are GREATLY appreciated. I need help on this pronto. Can someone take a look and let me know.

 

[BuglerX]

I've seen some restrictions on Application.transpose. I don't know how they would impact my formula. This is definitely a problem with Array and the transposition.

Anybody help ? The example database will give example data.

 

[jdraw]

I'm not an Excel person, but googling the error message indicates a possible issue

This problem occurs when you try to pass a formula that contains more than 255 characters, and you are using the FormulaArray property in Visual Basic for Applications.

I don't know if it applies, but may be worth looking at.

 

[BuglerX]

I saw that but I'm not sure how the formula is being parsed into the objExcel.Transpose(). Can you help me find out where in debug|local window it is and I can count the characters for the different date sets that I may be using with the example data?

Thanks again for all your assistance.

 

[jdraw]

Just saw your post. I found this via google
http://www.techonthenet.com/excel/formulas/transpose.php

You may want to do a char count of the Range you are using in Excel A2:E2.
Here's a link that may be releavant to counting chars in range of cells.
http://jsbi.blogspot.com/2007/04/excel-tip-how-to-perform-character.html

As I said, I am very familiar with Excel.

 

[BuglerX]

Ok I seem to have figured out why I was having problems with the above code.

I had a series of ====== in the data array varResults. When I replaced the ====== the code worked perfectly.

So my next question is How do I keep the '=====' in the data array and not have the formulaArray throw an error?