Specifying a range within an array

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bob - 12 Sep 2007 18:36 GMT

Can someone kindly tell me how to specify a range within a 2-dimensional
array (e.g., row 1, column 1 through row 1, column 14)? I'm trying to do so
in the following VBA line:

RowHourCellsFound = WorksheetFunction.CountIf(Range(HoursArray(1, 1) & _
":" & HoursArray(1, 14)), ">0")

Thanks in advance for any help.

Reply to this Message

Alan Beban - 12 Sep 2007 19:21 GMT

k = 0
For i = 1 To 1: For j = 1 To 14
If HoursArray(i, j) > 0 Then k = k + 1
Next: Next
RowHourCellsFound = k

Or, assuming that the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook

RowHourCellsFound = ArrayCountIf(SubArray(HoursArray,1,14,1,1),0,">")

Alan Beban

> Can someone kindly tell me how to specify a range within a 2-dimensional
> array (e.g., row 1, column 1 through row 1, column 14)? I'm trying to do so
[quoted text clipped - 4 lines]
>
> Thanks in advance for any help.

Reply to this Message

Bob - 12 Sep 2007 19:38 GMT

Alan,
Thanks for your help! Although your response is specific to my current
problem, for my own edification and for future reference, is there a
"generic" way to specify a range of "cells" in an array?
Thanks again,
Bob

> k = 0
> For i = 1 To 1: For j = 1 To 14
[quoted text clipped - 17 lines]
> >
> > Thanks in advance for any help.

Reply to this Message

Alan Beban - 12 Sep 2007 20:30 GMT

For a "range of cells", as you call it, in e.g., myArray

For i = startRow to endRow:For j = startColumn to endColumn
RangeOfCells(i, j) = myArray(i, j)
Next:Next

Using the downloaded functions it's implicit in the response below
(i.e., SubArray(HoursArray,1,14,1,1)). Review the description of the
downloaded SubArray function.

SubArray(myArray, startColumn, endColumn, startRow, endRow)

to return a 1-based "range of cells" (i.e., subarray).

The built-in COUNTIF function doesn't operate on VBA arrays, hence the
use of the downloaded ArrayCountIf function below.

Alan Beban

> Alan,
> Thanks for your help! Although your response is specific to my current
[quoted text clipped - 15 lines]
>>
>>Alan Beban

Reply to this Message

Bob - 12 Sep 2007 21:40 GMT

Alan,
As always, thanks!
I will definitely take a closer look at your SubArray function. In
hindsight, if there was a simpler or more direct way to address a range
within an array, you wouldn't have needed to write a UDF. Silly me.
Thanks again,
Bob

> For a "range of cells", as you call it, in e.g., myArray
>
[quoted text clipped - 34 lines]
> >>
> >>Alan Beban

Reply to this Message

Alan Beban - 12 Sep 2007 22:04 GMT

Well, I suppose it's fair to say that some might think

For i = startRow to endRow:For j = startColumn to endColumn
RangeOfCells(i, j) = myArray(i, j)
Next:Next

is simpler and more direct. For someone like me, who always has the
downloaded functions available, perhaps not.

Alan Beban

> Alan,
> As always, thanks!
[quoted text clipped - 22 lines]
>>
>>Alan Beban

Reply to this Message