Type mismatch
 

Can anyone tell me why this produces Run-time error 13: Type mismatch?

Thanks in advance.

----------

Option Explicit
Private Sub testloop()

Dim lstrow As Long
Dim i As Long
Dim agtname As String

lstrow = Range("a" & Rows.Count).End(xlUp).Rows
For i = 2 To lstrow
agtname = Range("a" & i).Value ' *** also tried .Text & still got error
MsgBox "Current Name is " & agtname
i = i + 1
Next i
End Sub

Type mismatch
 

It is not that you assignment of the range value (or text) is wrong you
just need to change your last row code to this......

lstrow = Cells(Rows.Count, "A").End(xlUp).Row

Cheers
Nigel

"Patti" wrote in message
...
Can anyone tell me why this produces Run-time error 13: Type mismatch?

Thanks in advance.

----------

Option Explicit
Private Sub testloop()

Dim lstrow As Long
Dim i As Long
Dim agtname As String

lstrow = Range("a" & Rows.Count).End(xlUp).Rows
For i = 2 To lstrow
agtname = Range("a" & i).Value ' *** also tried .Text & still got
error
MsgBox "Current Name is " & agtname
i = i + 1
Next i
End Sub

Type mismatch
 

In addition to Nigels comment are you sure you need the line i = i +
1
as it is "incrementing the loop count - causing your code to skip a row as
you go?"
humm

"Patti" wrote in message
...
Can anyone tell me why this produces Run-time error 13: Type mismatch?

Thanks in advance.

----------

Option Explicit
Private Sub testloop()

Dim lstrow As Long
Dim i As Long
Dim agtname As String

lstrow = Range("a" & Rows.Count).End(xlUp).Rows
For i = 2 To lstrow
agtname = Range("a" & i).Value ' *** also tried .Text & still got
error
MsgBox "Current Name is " & agtname
i = i + 1
Next i
End Sub

Type mismatch
 

Thanks Nigel - you were right of course. And good catch JMay - it was not
my intention to skip a row (just learning how to walk through a range!)

Regards,

Patti





"JMay" wrote in message news:aHjuc.2915$Tw.234@lakeread06...
In addition to Nigels comment are you sure you need the line i = i
+
1
as it is "incrementing the loop count - causing your code to skip a row as
you go?"
humm

"Patti" wrote in message
...
Can anyone tell me why this produces Run-time error 13: Type mismatch?

Thanks in advance.

----------

Option Explicit
Private Sub testloop()

Dim lstrow As Long
Dim i As Long
Dim agtname As String

lstrow = Range("a" & Rows.Count).End(xlUp).Rows
For i = 2 To lstrow
agtname = Range("a" & i).Value ' *** also tried .Text & still got
error
MsgBox "Current Name is " & agtname
i = i + 1
Next i
End Sub