Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Time Error "13" Type Mismatch
I am getting a Run Time Error '13' Type Mismatch Error on the following line
of code: Var1 = Application.WorksheetFunction.Average(Array1) The Array is filled with data from the cells in the spreadsheet while in a For next loop. It populates the arrray after a Calculate. Whenever I increase the loop 65,000 runs, I get the error on this line of code. The Array is dimensioned as a Variant. Is a Type Mismatch of this kind occuring becuase I am loading a spreadsheet error (i.e. DIV/0 etc) into the array and the Average function fails? It does not seem to fail on runs less than 65000. How can I check the contents of the array to see what would cause the Average function to fail? Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Time Error "13" Type Mismatch
Came across this note on the microsoft site:
http://support.microsoft.com/default...;en-us;Q177991 Suggesting that the error occurs because the function is contrained by the 65326 rows contraints in Excel. I find this confusing as I am always loading the array from the same cell in Excel. How do I use the Average, STD, Min, Max worksheet functions from Excel in VBA if I have this following contraints given that my array will have well over 65000 items? Thanks EM "ExcelMonkey" wrote: I am getting a Run Time Error '13' Type Mismatch Error on the following line of code: Var1 = Application.WorksheetFunction.Average(Array1) The Array is filled with data from the cells in the spreadsheet while in a For next loop. It populates the arrray after a Calculate. Whenever I increase the loop 65,000 runs, I get the error on this line of code. The Array is dimensioned as a Variant. Is a Type Mismatch of this kind occuring becuase I am loading a spreadsheet error (i.e. DIV/0 etc) into the array and the Average function fails? It does not seem to fail on runs less than 65000. How can I check the contents of the array to see what would cause the Average function to fail? Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Time Error "13" Type Mismatch
You won't use them. You will have to write your own functions that provide
that information without the limitation. -- Regards, Tom Ogilvy "ExcelMonkey" wrote in message ... Came across this note on the microsoft site: http://support.microsoft.com/default...;en-us;Q177991 Suggesting that the error occurs because the function is contrained by the 65326 rows contraints in Excel. I find this confusing as I am always loading the array from the same cell in Excel. How do I use the Average, STD, Min, Max worksheet functions from Excel in VBA if I have this following contraints given that my array will have well over 65000 items? Thanks EM "ExcelMonkey" wrote: I am getting a Run Time Error '13' Type Mismatch Error on the following line of code: Var1 = Application.WorksheetFunction.Average(Array1) The Array is filled with data from the cells in the spreadsheet while in a For next loop. It populates the arrray after a Calculate. Whenever I increase the loop 65,000 runs, I get the error on this line of code. The Array is dimensioned as a Variant. Is a Type Mismatch of this kind occuring becuase I am loading a spreadsheet error (i.e. DIV/0 etc) into the array and the Average function fails? It does not seem to fail on runs less than 65000. How can I check the contents of the array to see what would cause the Average function to fail? Thanks |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Time Error "13" Type Mismatch
If you are looping anyway to generate the array, why not calculate you
average in the loop yourself: for each cell in YourRange RunTotal=RunTotal+csng(cell.value) cellcount=cellcount+1 Next MyAvg=RunTotal/cellcount However, I'm sure this method will be slow. What about : with application.worksheetfunction MyAvg=.Sum(...ranges..)/.CountA(...ranges..) end with NickHK "ExcelMonkey" wrote in message ... I am getting a Run Time Error '13' Type Mismatch Error on the following line of code: Var1 = Application.WorksheetFunction.Average(Array1) The Array is filled with data from the cells in the spreadsheet while in a For next loop. It populates the arrray after a Calculate. Whenever I increase the loop 65,000 runs, I get the error on this line of code. The Array is dimensioned as a Variant. Is a Type Mismatch of this kind occuring becuase I am loading a spreadsheet error (i.e. DIV/0 etc) into the array and the Average function fails? It does not seem to fail on runs less than 65000. How can I check the contents of the array to see what would cause the Average function to fail? Thanks |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Time Error "13" Type Mismatch
Sorry I tried to post a reply but my Internet explorer crashed. This sounds
like a common issue Tom. Is this a bug in Excel or simply due to the fact that I have loaded my array from a changing cell in Excel vs loading it from calcs within VBA. Do you know of any common functions for Avv, STD, Min, Max which can be applied to an array afters its filled? I want to minimize the time lag in my code. Still a little shocked that Excel cannot accomodate this. EM "Tom Ogilvy" wrote: You won't use them. You will have to write your own functions that provide that information without the limitation. -- Regards, Tom Ogilvy "ExcelMonkey" wrote in message ... Came across this note on the microsoft site: http://support.microsoft.com/default...;en-us;Q177991 Suggesting that the error occurs because the function is contrained by the 65326 rows contraints in Excel. I find this confusing as I am always loading the array from the same cell in Excel. How do I use the Average, STD, Min, Max worksheet functions from Excel in VBA if I have this following contraints given that my array will have well over 65000 items? Thanks EM "ExcelMonkey" wrote: I am getting a Run Time Error '13' Type Mismatch Error on the following line of code: Var1 = Application.WorksheetFunction.Average(Array1) The Array is filled with data from the cells in the spreadsheet while in a For next loop. It populates the arrray after a Calculate. Whenever I increase the loop 65,000 runs, I get the error on this line of code. The Array is dimensioned as a Variant. Is a Type Mismatch of this kind occuring becuase I am loading a spreadsheet error (i.e. DIV/0 etc) into the array and the Average function fails? It does not seem to fail on runs less than 65000. How can I check the contents of the array to see what would cause the Average function to fail? Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Reasons for "Run-time error '13':Type mismatch"? | Excel Programming | |||
Error Handling to mitigate "Run Time Erorr 13 Type Mismatch" | Excel Programming | |||
Run-time Error "13" - File Type Mismatch | Excel Programming | |||
Help with Run-time error: "Type Mismatch" | Excel Programming | |||
"FIND" generates "Type mismatch" error | Excel Programming |