Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Type mismatch on LastRow
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 |
#2
|
|||
|
|||
you did not tell us what "did not work sometime" means. What doesn't work?
what message? try dim last row as LONG -- Don Guillett SalesAid Software "daniel chen" wrote in message ... 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 |
#3
|
|||
|
|||
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" "Don Guillett" wrote in message ... you did not tell us what "did not work sometime" means. What doesn't work? what message? try dim last row as LONG -- Don Guillett SalesAid Software "daniel chen" wrote in message ... 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 |
#4
|
|||
|
|||
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 --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "daniel chen" wrote in message ... 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" "Don Guillett" wrote in message ... you did not tell us what "did not work sometime" means. What doesn't work? what message? try dim last row as LONG -- Don Guillett SalesAid Software "daniel chen" wrote in message ... 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 |
#5
|
|||
|
|||
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" wrote in message ... 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 --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "daniel chen" wrote in message ... 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" "Don Guillett" wrote in message ... you did not tell us what "did not work sometime" means. What doesn't work? what message? try dim last row as LONG -- Don Guillett SalesAid Software "daniel chen" wrote in message ... 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 |
#6
|
|||
|
|||
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. |
#7
|
|||
|
|||
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" wrote in message ... 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. |
#8
|
|||
|
|||
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 --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "daniel chen" wrote ... 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. |
#9
|
|||
|
|||
Thanks again, David
"David McRitchie" wrote in message ... 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 --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "daniel chen" wrote ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic Formulas with Dynamic Ranges | Excel Worksheet Functions | |||
Why type mismatch - R/T error 13 | Excel Discussion (Misc queries) | |||
Convert data type of cells to Text,Number,Date and Time | Excel Worksheet Functions | |||
Convert data of cells to any type: Number, Date&Time, Text | Excel Discussion (Misc queries) | |||
Custom Type Charts Problem | Excel Worksheet Functions |