Sum not working on Array
 

I have several checkboxes. I pass the value of the checks
to an array called ChkbxArray. The array has 4 colums.
Columns 1 to 3 have Integers in them. I just added the
following to column 4 of the array

Set ChkbxArray(0, 4) = ActiveSheet.Comments
Set ChkbxArray(1, 4) = ActiveSheet.UsedRange
Set ChkbxArray(2, 4) = ActiveSheet.UsedRange
Set ChkbxArray(3, 4) = ActiveSheet.UsedRange
Set ChkbxArray(4, 4) = ActiveSheet.UsedRange
Set ChkbxArray(5, 4) = ActiveSheet.UsedRange

Upon doing this, the following line of code failed. I am
assuming it failed as the entire array has been put into
the sum. Even though I only want column 1 summed. What
can I do ensure that I can sum column 1 in this array?

ChkbxArraySum = Application.WorksheetFunction.Sum
(Application.Index(ChkbxArray, 0, 1))

Thanks

Sum not working on Array
 

That would be the way to do it. However, since you are using a
worksheetfunction, possibly the index function can't deal with array items
that have objects stored in them. That certainly isn't something that would
be encountered in a worksheet.

Looks like you will need to roll your own

Dim Tot as Double
for i = lbound(ChkbxArray,1) to ubound(ChkbxArray,1)
if isnumeric(ChkbxArray(i,lbound(ChkbxArray,2)) then
tot = tot + ChkbxArray(i,lbound(ChkbxArray,2))
end if
Next

--
Regards,
Tom Ogilvy

"ExcelMonkey" wrote in message
...
I have several checkboxes. I pass the value of the checks
to an array called ChkbxArray. The array has 4 colums.
Columns 1 to 3 have Integers in them. I just added the
following to column 4 of the array

Set ChkbxArray(0, 4) = ActiveSheet.Comments
Set ChkbxArray(1, 4) = ActiveSheet.UsedRange
Set ChkbxArray(2, 4) = ActiveSheet.UsedRange
Set ChkbxArray(3, 4) = ActiveSheet.UsedRange
Set ChkbxArray(4, 4) = ActiveSheet.UsedRange
Set ChkbxArray(5, 4) = ActiveSheet.UsedRange

Upon doing this, the following line of code failed. I am
assuming it failed as the entire array has been put into
the sum. Even though I only want column 1 summed. What
can I do ensure that I can sum column 1 in this array?

ChkbxArraySum = Application.WorksheetFunction.Sum
(Application.Index(ChkbxArray, 0, 1))

Thanks