Error: Type mismatch

i have a vba code included the following:
shortest = count(1)
For r = 1 To 200
If count(r) < shortest And count(r) > 0 Then
shortest = count(r)
End If
Next
Rows("shortest:shortest").Select
Selection.Cut
Rows("1:1").Select
Selection.Insert Shift:=xlDown

However, when i try to run the code, the debugger promt an error
message said : type mismatch and then highlight the code:
Rows("shortest:shortest").select.
I declared "shortest" as variant.

Can any pro point out my mistake?Thank you very much

ekkehard
Tue Mar 11 05:38:53 CDT 2008

youu917@gmail.com schrieb:
> i have a vba code included the following:
> shortest = count(1)
> For r = 1 To 200
> If count(r) < shortest And count(r) > 0 Then
> shortest = count(r)
> End If
> Next
> Rows("shortest:shortest").Select
> Selection.Cut
> Rows("1:1").Select
> Selection.Insert Shift:=xlDown
>
> However, when i try to run the code, the debugger promt an error
> message said : type mismatch and then highlight the code:
> Rows("shortest:shortest").select.
> I declared "shortest" as variant.
>
> Can any pro point out my mistake?Thank you very much

VBScript does not interpolate/substitute values for variable names
in string literals. So assuming shortest holds 5 after the loop
and therefore you want to cut Rows( "5:5" ) try

Rows( shortest & ":" & shortest ).Select

Bob
Tue Mar 11 06:37:28 CDT 2008

youu917@gmail.com wrote:
> i have a vba code
Do not take the following as a rebuke: I am attempting to help you get
answers to your questions more efficiently. One key to efficiency is asking
your questions in a newsgroup where the question is on topic.

vba <> vbscript
They are two different languages, although the point could be made that
vbscript is based on VB and VBA.

Based on what you are doing in your code snip, it is highly likely you are
using Excel VBA. There are newsgroups devoted to Excel VBA where this
question will be more on topic.

However, since it may save a vbscript beginner from a similar error, read
on. :-)

> included the following:
> shortest = count(1)
> For r = 1 To 200
> If count(r) < shortest And count(r) > 0 Then
> shortest = count(r)
> End If
> Next
> Rows("shortest:shortest").Select
> Selection.Cut
> Rows("1:1").Select
> Selection.Insert Shift:=xlDown
>
> However, when i try to run the code, the debugger promt an error
> message said : type mismatch and then highlight the code:
> Rows("shortest:shortest").select.
> I declared "shortest" as variant.
>
> Can any pro point out my mistake?Thank you very much

This is a string literal:
"shortest:shortest"
You are telling the runtime engine to find a cell with the address
"shortest:shortest". it doesn't exist, does it. :-)


This is a string built by concatenating string variables:
shortest & ":" & shortest

At runtime, if shortest contains 5, the engine will substitute the current
values of shortest for the variable and spit out a string containing "5:5".
Note that you have correctly used & instead of + to concatenate the string.
& coerces the addends to strings to guarantee you will get the correct
result.




--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"