re: Type Mismatch Error

[Nicole Seibert]

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*"",""*LASKAANTHO*"",""*NELSONBECKY*""})*{1,2,3,4})"
Range("H3").Select
ActiveCell.FormulaR1C1 = _

"=SUMPRODUCT(COUNTIF(RC[1],{""*MILLERSTEVENM*"",""*PISCOPOGINA*"",""*LASKAANTHO*"",""*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

[K Dales]

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*"",""*LASKAANTHO*"",""*NELSONBECKY*""})*{1,2,3,4})"
> Range("H3").Select
> ActiveCell.FormulaR1C1 = _
>
> "=SUMPRODUCT(COUNTIF(RC[1],{""*MILLERSTEVENM*"",""*PISCOPOGINA*"",""*LASKAANTHO*"",""*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

[Nicole Seibert]

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*"",""*LASKAANTHO*"",""*NELSONBECKY*""})*{1,2,3,4})"
> > Range("H3").Select
> > ActiveCell.FormulaR1C1 = _
> >
> > "=SUMPRODUCT(COUNTIF(RC[1],{""*MILLERSTEVENM*"",""*PISCOPOGINA*"",""*LASKAANTHO*"",""*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

[Nicole Seibert]

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*"",""*LASKAANTHO*"",""*NELSONBECKY*""})*{1,2,3,4})"
> > Range("H3").Select
> > ActiveCell.FormulaR1C1 = _
> >
> > "=SUMPRODUCT(COUNTIF(RC[1],{""*MILLERSTEVENM*"",""*PISCOPOGINA*"",""*LASKAANTHO*"",""*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