Evening all.
I'm having a very frustrating problem with some Excel arrays, and as this forum has solved all my Access problems to date, I thought I'd turn to you now.
The purpose of the model is to take accruals data submitted in a spreadsheet, look up various fields from another source, and output the results to a template for upload into the GL system.
I have two tables in two separate excel workbooks. The source table has been read into Temp() dynamic array, and the lookup table has been read into TUCostCentreList() dynamic array.
Variables Prop and CCtr are declared as variants.
Prop is the property number as held in the third column of the Temp array. To ensure that I'm comparing like with like, I've converted it to string format :
Prop = CStr(Temp(i, 3)) - i is the value for loopng through the Temp array
I then look up the property number in the Cost Centre array, where the property number is in column 7, and the Cost Centre is in column 5 :
CCtr = TUCostCentreList(Application.Match(Prop, Application.Index(TUCostCentreList, 0, 7), 0), 5)
There is an error handler to write the line number i and the property number if not found.
My problem is that sometimes, the lookup line produces a "Type mismatch" error. This does not happen every time the lookup fails - that would make more sense! As it loops through the Temp array, some of the errors are captured in the error handling routine, and some cause the break error. There doesn't seem to be any correlation between the lines that cause the error, only that if I move the suspect lines around, they always cause the error
Any thoughts anyone has that may help would be gratefully appreciated.
Many thanks in anticipation
Paul
I'm having a very frustrating problem with some Excel arrays, and as this forum has solved all my Access problems to date, I thought I'd turn to you now.
The purpose of the model is to take accruals data submitted in a spreadsheet, look up various fields from another source, and output the results to a template for upload into the GL system.
I have two tables in two separate excel workbooks. The source table has been read into Temp() dynamic array, and the lookup table has been read into TUCostCentreList() dynamic array.
Variables Prop and CCtr are declared as variants.
Prop is the property number as held in the third column of the Temp array. To ensure that I'm comparing like with like, I've converted it to string format :
Prop = CStr(Temp(i, 3)) - i is the value for loopng through the Temp array
I then look up the property number in the Cost Centre array, where the property number is in column 7, and the Cost Centre is in column 5 :
CCtr = TUCostCentreList(Application.Match(Prop, Application.Index(TUCostCentreList, 0, 7), 0), 5)
There is an error handler to write the line number i and the property number if not found.
My problem is that sometimes, the lookup line produces a "Type mismatch" error. This does not happen every time the lookup fails - that would make more sense! As it loops through the Temp array, some of the errors are captured in the error handling routine, and some cause the break error. There doesn't seem to be any correlation between the lines that cause the error, only that if I move the suspect lines around, they always cause the error
Any thoughts anyone has that may help would be gratefully appreciated.
Many thanks in anticipation
Paul
