Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
distinguish Range and "Single"
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
distinguish Range and "Single"
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
distinguish Range and "Single"
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" wrote in message ... 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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
distinguish Range and "Single"
If (TypeOf Z Is Range) And (Z.Cells.Count1) 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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
distinguish Range and "Single"
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.Count1' 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" wrote in message ... If (TypeOf Z Is Range) And (Z.Cells.Count1) 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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
distinguish Range and "Single"
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" wrote in message ... 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? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
distinguish Range and "Single"
"Jon Peltier" wrote in message ... 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 _______ |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
distinguish Range and "Single"
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 ^^ |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
distinguish Range and "Single"
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" wrote in message ... 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 ^^ |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
distinguish Range and "Single"
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" wrote in message ... 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 ^^ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to distinguish zeros ("0") from blank cells? | Excel Worksheet Functions | |||
Multiple "source" workbooks linked to single "destination" workboo | Excel Worksheet Functions | |||
Change data in a single column from "last, first" to "first last" | Excel Discussion (Misc queries) | |||
Copy column range of "single word" cells with spaces to a single c | Excel Discussion (Misc queries) | |||
use variable in Workbooks("book1").Worksheets("sheet1").Range("a1" | Excel Programming |