Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
array not working | Excel Discussion (Misc queries) | |||
3d array not working | Excel Worksheet Functions | |||
Array fomrula not working... | Excel Worksheet Functions | |||
CountIf Array not working | Excel Discussion (Misc queries) | |||
Array formula not working | Excel Worksheet Functions |