Specifying a range within an array

[Guest]

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.

 

[Alan Beban]

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

 

[Guest]

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

 

[Alan Beban]

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

 

[Guest]

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

 

[Alan Beban]

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