Type mismatch !

[Roshintosh]

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 !

 

[Norman Jones]

Hi Roshintosh,

Try:

Dim var1 As Variant

 

[Roshintosh]

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 !

 

[paul.robinson]

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

 

[Norman Jones]

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.

 

[Leith Ross]

Hello Roshintosh & Paul,

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

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

The help files tell you how to raise an error in cell, but not how t
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 Ros

 

[Dave Peterson]

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]

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 !