Type Mismatch

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mike - 22 Jan 2006 03:17 GMT

I receive a 'type mismatch' when trying to use the following line of code to
check the contents of cells and then assign a constant to the variable 'item'.

If Range("n1").Value <> "" And Range("n2:n50").Value = "" Then item = 1

Reply to this Message

Ron Rosenfeld - 22 Jan 2006 03:48 GMT

>I receive a 'type mismatch' when trying to use the following line of code to
>check the contents of cells and then assign a constant to the variable 'item'.
>
>If Range("n1").Value <> "" And Range("n2:n50").Value = "" Then item = 1

I think the problem has to do with trying to get the Value property of a
multicell Range object. I think you have to loop through the range to do what
you are trying to do.

--ron

Reply to this Message

Tom Ogilvy - 22 Jan 2006 04:00 GMT

In this case, you don't need to loop if you only want all the cells (n2:n50)
to be empty as the second condition
If Range("n1").Value <> "" And Application.CountA(Range("n2:n50")) = 0 Then
item = 1

Signature

Regards,
Tom Ogilvy

> >I receive a 'type mismatch' when trying to use the following line of code to
> >check the contents of cells and then assign a constant to the variable 'item'.
[quoted text clipped - 6 lines]
>
> --ron

Reply to this Message

Ron Rosenfeld - 22 Jan 2006 04:50 GMT

>In this case, you don't need to loop if you only want all the cells (n2:n50)
>to be empty as the second condition
>If Range("n1").Value <> "" And Application.CountA(Range("n2:n50")) = 0 Then
>item = 1

That's true.

I was being more general.

--ron

Reply to this Message

Ron Rosenfeld - 22 Jan 2006 04:53 GMT

That's true.

I was being more general and neglected to focus on the OP's issue.
--ron

Reply to this Message

Norman Jones - 22 Jan 2006 03:53 GMT

Hi Mike,

Try;

'=============>>
Public Sub Tester003()
Dim myItem As Long

If Range("n1").Value <> "" And _
Application.CountA(Range("n2:n50")) = 0 Then myItem = 1
MsgBox myItem

End Sub
'<<============

BTW, I changed Item to myItem because Item ia a reserved word.

---
Regards,
Norman

>I receive a 'type mismatch' when trying to use the following line of code
>to
> check the contents of cells and then assign a constant to the variable
> 'item'.
>
> If Range("n1").Value <> "" And Range("n2:n50").Value = "" Then item = 1

Reply to this Message

Mike - 22 Jan 2006 04:37 GMT

Worked great, thanks Norman

> Hi Mike,
>
[quoted text clipped - 23 lines]
> >
> > If Range("n1").Value <> "" And Range("n2:n50").Value = "" Then item = 1

Reply to this Message