Type mismatch !

If I've got the following code in vba

var1 as string
var1 = Cells(1, 1).Value


If the cell in question contains the value "#N/A", then I get a type
mismatch error on the line var1 = Cells(1, 1).Value.

However, no error if the value in the cell in "N/A"

What can I do to get around this, I need var1 to contain the value in
the cell, no matter what value it is.

Also, I wonder why VBA doesn't tell you which line it was executing
when it comes across a type mismatch, I had to find out by stepping
through the code !

Hi Roshintosh,

Try:

Dim var1 As Variant


---
Regards,
Norman



"Roshintosh" <roshintosh@yahoo.co.uk> wrote in message
news:1131589295.639239.14390@o13g2000cwo.googlegroups.com...
> If I've got the following code in vba
>
> var1 as string
> var1 = Cells(1, 1).Value
>
>
> If the cell in question contains the value "#N/A", then I get a type
> mismatch error on the line var1 = Cells(1, 1).Value.
>
> However, no error if the value in the cell in "N/A"
>
> What can I do to get around this, I need var1 to contain the value in
> the cell, no matter what value it is.
>
> Also, I wonder why VBA doesn't tell you which line it was executing
> when it comes across a type mismatch, I had to find out by stepping
> through the code !
>

Cheers Norman,

Declaring the variable as variant makes no difference unfortunately.

If the value in the cell is #N/A, then the statement

dim var1 as variant
var1 = Cells(1, 1).Value

gives a type mismatch. (run time error 13)

I'm totally confused !

Hi

Try
var1 as string
var1 = Cells(1, 1).Text

I'd have thought the Variant declaration would do it, but it seems not.
Anyone out there know why not? I couldn't find the type of an error in
the Errors collection

You could also do
var1 as Variant
var1 = Cstr(Cells(1,1).Value)

but this returns
Error 2042

regards
Paul

Hi Roshintosh,

> Declaring the variable as variant makes no difference unfortunately.

If the variable is declared as a string, the macro will break with a
run-time 13 type mismatch code.

I tried:

Sub ABC()
Dim var1 As Variant
var1 = Cells(1, 1).Value
Debug.Print "var1", var1
Debug.Print "#N/A error number", CVErr(xlErrNA)
End
End Sub

This produced the following in the imediate window:

var1 Error 2042
#N/A error number Error 2042

Thus, the variant variable correctly holds the cell's error value.

---
Regards,
Norman



"Roshintosh" <roshintosh@yahoo.co.uk> wrote in message
news:1131622578.522911.321030@g43g2000cwa.googlegroups.com...
> Cheers Norman,
>
> Declaring the variable as variant makes no difference unfortunately.
>
> If the value in the cell is #N/A, then the statement
>
> dim var1 as variant
> var1 = Cells(1, 1).Value
>
> gives a type mismatch. (run time error 13)
>
> I'm totally confused !
>

Hello Roshintosh & Paul,

Worksheet cells are all variants. When an error is found, Excel sets the cell to an Error type variant. This can be verified 2 ways. First by using IsError() and second by using VarType(). IsError will return True if the cell is an Error variant type and VarType returns 10 if the cell is an Error variant type.

To answer your question about getting a cell value, if there is an error Excel will only return the Error, not the value. So, your best bet is test for an error and handle it.

The help files tell you how to raise an error in cell, but not how to convert an Error type back to something useful like an Error Number. Here's how to do that...

Sub ErrorTest()

Dim Ret
Dim ErrNum

Ret = Range("A1").Value
If IsError(Ret) Then
E = CInt(Ret)
End If


End Sub

Hope this helps you some.

Sincerely,
Leith Ross

dim Var1 as Variant
if iserror(cells(1,1).value) then
var1 = cells(1,1).text
else
var1 = cells(1,1).value
end if




Roshintosh wrote:
>
> If I've got the following code in vba
>
> var1 as string
> var1 = Cells(1, 1).Value
>
> If the cell in question contains the value "#N/A", then I get a type
> mismatch error on the line var1 = Cells(1, 1).Value.
>
> However, no error if the value in the cell in "N/A"
>
> What can I do to get around this, I need var1 to contain the value in
> the cell, no matter what value it is.
>
> Also, I wonder why VBA doesn't tell you which line it was executing
> when it comes across a type mismatch, I had to find out by stepping
> through the code !

--

Dave Peterson

Thanks Dave

I just didn't notice the .text property !

I'm new to VBA

I spent a good while today putting in unnecessary error handling for
using the .value property
Now I can take it out !