Hi,

I have to do different things ([BLOCK 1] and [BLOCK 2]) if Z is a
"Single" (a number or a Range of 1x1) or if Z is a Range (a genuine, not
1x1). My workaround is like

If TypeOf Z Is Range Then
If (Z.Rows.Count=1) And (Z.Columns.Count=1) Then
[Block 1]
Else
[Block 2]
End If
Else
[Block 1]
End If


but I don't like it: [Block 1] two times and the construction
'(Z.Rows.Count=1) And (Z.Columns.Count=1)' seems to be ugly.

I guess that this problem appears frequently.
What would be a good solution?

You can just use Z.Count to count the cells in range Z.

Hope this helps,

Hutch

"Dietmar M. Kehrmann" wrote:

>
> Hi,
>
> I have to do different things ([BLOCK 1] and [BLOCK 2]) if Z is a
> "Single" (a number or a Range of 1x1) or if Z is a Range (a genuine, not
> 1x1). My workaround is like
>
> If TypeOf Z Is Range Then
> If (Z.Rows.Count=1) And (Z.Columns.Count=1) Then
> [Block 1]
> Else
> [Block 2]
> End If
> Else
> [Block 1]
> End If
>
>
> but I don't like it: [Block 1] two times and the construction
> '(Z.Rows.Count=1) And (Z.Columns.Count=1)' seems to be ugly.
>
> I guess that this problem appears frequently.
> What would be a good solution?
>
>

Probably better to use Z.Cells.Count, rather than rely on the default.
Especially since there's no visible cue that Z may refer to a range.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Tom Hutchins" <(E-Mail Removed)> wrote in message
news:668EF993-A35C-46F8-B92C-(E-Mail Removed)...
> You can just use Z.Count to count the cells in range Z.
>
> Hope this helps,
>
> Hutch
>
> "Dietmar M. Kehrmann" wrote:
>
>>
>> Hi,
>>
>> I have to do different things ([BLOCK 1] and [BLOCK 2]) if Z is a
>> "Single" (a number or a Range of 1x1) or if Z is a Range (a genuine, not
>> 1x1). My workaround is like
>>
>> If TypeOf Z Is Range Then
>> If (Z.Rows.Count=1) And (Z.Columns.Count=1) Then
>> [Block 1]
>> Else
>> [Block 2]
>> End If
>> Else
>> [Block 1]
>> End If
>>
>>
>> but I don't like it: [Block 1] two times and the construction
>> '(Z.Rows.Count=1) And (Z.Columns.Count=1)' seems to be ugly.
>>
>> I guess that this problem appears frequently.
>> What would be a good solution?
>>
>>

If (TypeOf Z Is Range) And (Z.Cells.Count>1) Then
[Block 2]
Else
[Block 1]
End Fi

does not work if Z is a number (like 0,5). It works with [Block 2] for a
genuine Range (not of 1x1) and with [Block 1] for a 1x1-Range, but no for
numbers with [Block 1].

Why not?

I think your problem has to do with the fact that VBA does not short-circuit
its logical evaluations. So, even if 'TypeOf Z Is Range' evaluates to
False, VB will still evaluate the 'Z.Cells.Count>1' expression which, of
course, produces an error. Try your code this way...

If TypeOf Z Is Range Then
If Z.Cells.Count > 1 Then
[Block 2]
Else
[Block 1]
End If
End If

Rick


"Dietmar M. Kehrmann" <(E-Mail Removed)> wrote in message
news:480ced25$(E-Mail Removed)...
>
> If (TypeOf Z Is Range) And (Z.Cells.Count>1) Then
> [Block 2]
> Else
> [Block 1]
> End Fi
>
> does not work if Z is a number (like 0,5). It works with [Block 2] for a
> genuine Range (not of 1x1) and with [Block 1] for a 1x1-Range, but no for
> numbers with [Block 1].
>
> Why not?

Aside from changing

(Z.Rows.Count=1) And (Z.Columns.Count=1)

to

Z.Cells.Count

I don't see anything wrong with the construction. It's how VBA works.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Dietmar M. Kehrmann" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>
> Hi,
>
> I have to do different things ([BLOCK 1] and [BLOCK 2]) if Z is a
> "Single" (a number or a Range of 1x1) or if Z is a Range (a genuine, not
> 1x1). My workaround is like
>
> If TypeOf Z Is Range Then
> If (Z.Rows.Count=1) And (Z.Columns.Count=1) Then
> [Block 1]
> Else
> [Block 2]
> End If
> Else
> [Block 1]
> End If
>
>
> but I don't like it: [Block 1] two times and the construction
> '(Z.Rows.Count=1) And (Z.Columns.Count=1)' seems to be ugly.
>
> I guess that this problem appears frequently.
> What would be a good solution?
>

"Jon Peltier" <(E-Mail Removed)> wrote in message
news:O%23ewz2%(E-Mail Removed)...
> Aside from changing
>
> (Z.Rows.Count=1) And (Z.Columns.Count=1)
>
> to
>
> Z.Cells.Count

....particularly if there's any chance the range is not contiguous.

Tim

>
> I don't see anything wrong with the construction. It's how VBA works.
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Tutorials and Custom Solutions
> Peltier Technical Services, Inc. - http://PeltierTech.com
> _______
>
>

But I need

If TypeOf Z Is Range Then
If Z.Cells.Count > 1 Then
[Block 2]
Else
[Block 1]
End If
Else
[Block 1]
End If

and then [Block 1] appears two times. Ugly ^^

Hi Dietmar,

You can perform both tests simultaneously like so:

Dim bIsMultiCellRange As Boolean

On Error Resume Next
bIsMultiCellRange = (TypeOf Z Is Range) And (Z.Cells.Count > 1)
On Error GoTo 0

If bIsMultiCellRange Then
[Block 2]
Else
[Block 1]
End If

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

"Dietmar M. Kehrmann" <(E-Mail Removed)> wrote in message
news:480d91dd$(E-Mail Removed)...
>
>
> But I need
>
> If TypeOf Z Is Range Then
> If Z.Cells.Count > 1 Then
> [Block 2]
> Else
> [Block 1]
> End If
> Else
> [Block 1]
> End If
>
> and then [Block 1] appears two times. Ugly ^^
>

If [Block1] is more than a few lines, I would put its code in a subroutine
and then call that subroutine from this code...

......
......
If TypeOf Z Is Range Then
If Z.Cells.Count > 1 Then
[Block 2]
Else
Call Block1
End If
Else
Call Block1
End If
.......
.......
End Sub|Function

Sub Block1()
<<code>>
End Sub

Include an appropriate argument list if the Block1 subroutine is dependent
on local variables.

Rick


"Dietmar M. Kehrmann" <(E-Mail Removed)> wrote in message
news:480d91dd$(E-Mail Removed)...
>
>
> But I need
>
> If TypeOf Z Is Range Then
> If Z.Cells.Count > 1 Then
> [Block 2]
> Else
> [Block 1]
> End If
> Else
> [Block 1]
> End If
>
> and then [Block 1] appears two times. Ugly ^^
>