Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Type Mismatch Error
Here is my code:
Dim LastCell As Long LastCell = Cells(Rows.Count, 1).End(xlUp) Range("H2").Select ActiveCell.FormulaR1C1 = _ "=SUMPRODUCT(COUNTIF(RC[1],{""*MILLERSTEVENM*"",""*PISCOPOGINA*"",""*LASKAAN THO*"",""*NELSONBECKY*""})*{1,2,3,4})" Range("H3").Select ActiveCell.FormulaR1C1 = _ "=SUMPRODUCT(COUNTIF(RC[1],{""*MILLERSTEVENM*"",""*PISCOPOGINA*"",""*LASKAAN THO*"",""*NELSONBECKY*""})*{1,2,3,4})" Range("H2:H3").Select Selection.Copy Range("R6:R" & LastCell).Select Selection.AutoFill Destination:=Range("R6:R" & LastCell), Type:=xlFillDefault I keep get a Type Mismatch error on the designation of lastcell. Is this because I have 5455 records? Or some other reason that is not currently available in the knowledge base? From the knowledge base (although info only goes up to Excel9; I am working with Excel 11): The maximum number of elements in the array is limited by available memory or the Excel worksheet maximum size (65536 rows X 256 columns). However, the maximum number of elements in the array that you can pass to Excel using the Excel Transpose function is 5461. If you exceed this limit, you receive the following error message: Run-time error '13': Type Mismatch |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Type Mismatch Error
Cells(Rows.Count, 1).End(xlUp) gives a Range as the result, your LastCell is
a Long. Make it Cells(Rows.Count, 1).End(xlUp).Row and I think that will do it. -- - K Dales "Nicole Seibert" wrote: Here is my code: Dim LastCell As Long LastCell = Cells(Rows.Count, 1).End(xlUp) Range("H2").Select ActiveCell.FormulaR1C1 = _ "=SUMPRODUCT(COUNTIF(RC[1],{""*MILLERSTEVENM*"",""*PISCOPOGINA*"",""*LASKAAN THO*"",""*NELSONBECKY*""})*{1,2,3,4})" Range("H3").Select ActiveCell.FormulaR1C1 = _ "=SUMPRODUCT(COUNTIF(RC[1],{""*MILLERSTEVENM*"",""*PISCOPOGINA*"",""*LASKAAN THO*"",""*NELSONBECKY*""})*{1,2,3,4})" Range("H2:H3").Select Selection.Copy Range("R6:R" & LastCell).Select Selection.AutoFill Destination:=Range("R6:R" & LastCell), Type:=xlFillDefault I keep get a Type Mismatch error on the designation of lastcell. Is this because I have 5455 records? Or some other reason that is not currently available in the knowledge base? From the knowledge base (although info only goes up to Excel9; I am working with Excel 11): The maximum number of elements in the array is limited by available memory or the Excel worksheet maximum size (65536 rows X 256 columns). However, the maximum number of elements in the array that you can pass to Excel using the Excel Transpose function is 5461. If you exceed this limit, you receive the following error message: Run-time error '13': Type Mismatch |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Type Mismatch Error
Thank you.. what you wrote helped.
Now, however, I am getting a 1004 error (yes, i am trying to automate) and (no, i have declared ap, workbook, and worksheet cause I haven't figure out how yet) and (yes, I have tried to set that up three times now with examples I found on the web, but they didn't work.) The error comes up on the last line of the code from below: Selection.AutoFill Destination:=Range("R6:R" & LastCell), Type:=xlFillDefault "K Dales" wrote: Cells(Rows.Count, 1).End(xlUp) gives a Range as the result, your LastCell is a Long. Make it Cells(Rows.Count, 1).End(xlUp).Row and I think that will do it. -- - K Dales "Nicole Seibert" wrote: Here is my code: Dim LastCell As Long LastCell = Cells(Rows.Count, 1).End(xlUp) Range("H2").Select ActiveCell.FormulaR1C1 = _ "=SUMPRODUCT(COUNTIF(RC[1],{""*MILLERSTEVENM*"",""*PISCOPOGINA*"",""*LASKAAN THO*"",""*NELSONBECKY*""})*{1,2,3,4})" Range("H3").Select ActiveCell.FormulaR1C1 = _ "=SUMPRODUCT(COUNTIF(RC[1],{""*MILLERSTEVENM*"",""*PISCOPOGINA*"",""*LASKAAN THO*"",""*NELSONBECKY*""})*{1,2,3,4})" Range("H2:H3").Select Selection.Copy Range("R6:R" & LastCell).Select Selection.AutoFill Destination:=Range("R6:R" & LastCell), Type:=xlFillDefault I keep get a Type Mismatch error on the designation of lastcell. Is this because I have 5455 records? Or some other reason that is not currently available in the knowledge base? From the knowledge base (although info only goes up to Excel9; I am working with Excel 11): The maximum number of elements in the array is limited by available memory or the Excel worksheet maximum size (65536 rows X 256 columns). However, the maximum number of elements in the array that you can pass to Excel using the Excel Transpose function is 5461. If you exceed this limit, you receive the following error message: Run-time error '13': Type Mismatch |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Type Mismatch Error
ummm..
I would like it noted that I caught this (my H's didn't match): Range("H2:H3").Select Selection.Copy Range("H4:H" & LastCell).Select Selection.AutoFill Destination:=Range("H4:H" & LastCell), Type:=xlFillDefault "K Dales" wrote: Cells(Rows.Count, 1).End(xlUp) gives a Range as the result, your LastCell is a Long. Make it Cells(Rows.Count, 1).End(xlUp).Row and I think that will do it. -- - K Dales "Nicole Seibert" wrote: Here is my code: Dim LastCell As Long LastCell = Cells(Rows.Count, 1).End(xlUp) Range("H2").Select ActiveCell.FormulaR1C1 = _ "=SUMPRODUCT(COUNTIF(RC[1],{""*MILLERSTEVENM*"",""*PISCOPOGINA*"",""*LASKAAN THO*"",""*NELSONBECKY*""})*{1,2,3,4})" Range("H3").Select ActiveCell.FormulaR1C1 = _ "=SUMPRODUCT(COUNTIF(RC[1],{""*MILLERSTEVENM*"",""*PISCOPOGINA*"",""*LASKAAN THO*"",""*NELSONBECKY*""})*{1,2,3,4})" Range("H2:H3").Select Selection.Copy Range("R6:R" & LastCell).Select Selection.AutoFill Destination:=Range("R6:R" & LastCell), Type:=xlFillDefault I keep get a Type Mismatch error on the designation of lastcell. Is this because I have 5455 records? Or some other reason that is not currently available in the knowledge base? From the knowledge base (although info only goes up to Excel9; I am working with Excel 11): The maximum number of elements in the array is limited by available memory or the Excel worksheet maximum size (65536 rows X 256 columns). However, the maximum number of elements in the array that you can pass to Excel using the Excel Transpose function is 5461. If you exceed this limit, you receive the following error message: Run-time error '13': Type Mismatch |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Visual Basic Error Run Time Error, Type Mismatch | Excel Discussion (Misc queries) | |||
keep getting type mismatch error | Excel Programming | |||
Help: Compile error: type mismatch: array or user defined type expected | Excel Programming | |||
Type mismatch error | Excel Programming | |||
Befuddled with For Next Loop ------ Run - Time Error '13' Type Mismatch Error | Excel Programming |