Type mismatch on LastRow

[daniel chen]

The following codes did not work sometime.
Can you help me with its limitation if any, Please.
It hang up as "Type mismatch"
on LastRow = Cells(.Rows.Count, "A").End(xlUp).Row

Dim LastRow As Integer
Set LogWksh = Worksheets("TestSheet")
With LogWksh
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

 

[Don Guillett]

you did not tell us what "did not work sometime" means. What doesn't work?
what message?
try
dim last row as LONG

 

[daniel chen]

The macro was working fine when I first wrote it.
Sometime it would stop working while I am on the net.
I did try - Dim LastRow as Long
as well as - Dim LastRow as Variant
It ceased to execute at - LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
with an error message "Type mismatch"

 

[David McRitchie]

worked for me in Excel 2000 after dimensioning LogWksh
but failure to dimension would get a very explicit error, as
would not having the named worksheet. Do you have
Option Explicit
at the top of the module. LastRow should be as Long.
Try copying the code again.

Dim LastRow As Long, LogWksh As Worksheet
Set LogWksh = Worksheets("TestSheet")
With LogWksh
LastRow = .Cells(.Rows.Count, "A").End(xlUp).row
'-- MsgBox LastRow
End With

 

[daniel chen]

Hi, David
I think you have solved my problem.
I had been using these codes

Dim LastRow As Integer
With Sheets("TestSheet")
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

Using your codes on my problematic macro, it went away.
Thanks you very much.

 

[David McRitchie]

Hi Daniel,
Good, but it is important to know what was changed and why.

Options Explicit should be at the top of your module from Excel 97 on.
There are 65536 rows in a worksheet so the variable for the
number of rows should be Long as Integer is not large enough.

Sorry that I had not actually tested with the variable as Integer
as that does in fact provide a subscript out of range error.

 

[daniel chen]

Hi David,
I do have Options Explicit at the top on all my macros.
Thanks for this extra info about integer.
I thought Long was for decimal numbers only.

 

[David McRitchie]

Hi Daniel,

In your VBE HELP (F1) look up topic "Data Type Summary"
and do look it up because you will need to know this for helping
you with Options Explicit for other data types as well.

Integer, 2 bytes, value -32,768 to 32,767
Long (long integer), 4 bytes value -2,147,483,648 to 2,147,483,647

INFO: How VB Interprets Numbers, Constants and Numeric Types
http://support.microsoft.com/kb/199809

You can use the TypeName VBA function, to determine
how a variable is actually being used such as if something was not declared
or was declared as variant Look up TypeName in your VBE Help.
http://www.mvps.org/dmcritchie/excel/vba.htm#explicit

 

[daniel chen]

Thanks again, David

Hi Daniel,

In your VBE HELP (F1) look up topic "Data Type Summary"
and do look it up because you will need to know this for helping
you with Options Explicit for other data types as well.

Integer, 2 bytes, value -32,768 to 32,767
Long (long integer), 4 bytes value -2,147,483,648 to 2,147,483,647

INFO: How VB Interprets Numbers, Constants and Numeric Types
http://support.microsoft.com/kb/199809

You can use the TypeName VBA function, to determine
how a variable is actually being used such as if something was not
declared
or was declared as variant Look up TypeName in your VBE Help.
http://www.mvps.org/dmcritchie/excel/vba.htm#explicit