Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Specifying a range within an array
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Specifying a range within an array
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 Bob wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Specifying a range within an array
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" wrote: 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 Bob wrote: 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Specifying a range within an array
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 Bob wrote: 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" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Specifying a range within an array
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" wrote: 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 Bob wrote: 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" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Specifying a range within an array
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 Bob wrote: 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" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to fix an array or range | Excel Discussion (Misc queries) | |||
Range to Array | Excel Programming | |||
copy one array formula to an array range | Excel Programming | |||
Tricky array formula issue - Using array formula on one cell, then autofilling down a range | Excel Programming | |||
Range as array | Excel Programming |