agarwaldvk
05-22-2007, 11:01 PM
Hi Everybody
Can something similar to this be done in VBA?
We can write formulas such as the follows in a cell :-
=sumproduct(index($a1:$d500,,1)="test" * index($a1:$d500,,3)="thisNum")
This, as we know will give me the sum of the product of all the rows where the value in the range $a1:$a500 = "test" and the value in the range $c1:$c500 = "thisNum".
Can something similar be done using a 2 dimensional array where I can refer to a column of data in an array.
For example, if I am using a 500 row by 4 column array, can I use something similar to the above sumproduct function in VBA making use of the above array to get the sum of the product of the data in only those rows of the array where the data in the row in say the 3rd column of the array equals "test" and the data in the row in say the 4th column of the array equals "thisNum"?
Any suggestions?
I need to do using arrays because the underlying referred workbook is closed and I intend to work closed workbook. You may presume that the data from the referred closed has been fetched and loaded in a 2 dimensional array for processing.
Best regards
Deepak Agarwal
Can something similar to this be done in VBA?
We can write formulas such as the follows in a cell :-
=sumproduct(index($a1:$d500,,1)="test" * index($a1:$d500,,3)="thisNum")
This, as we know will give me the sum of the product of all the rows where the value in the range $a1:$a500 = "test" and the value in the range $c1:$c500 = "thisNum".
Can something similar be done using a 2 dimensional array where I can refer to a column of data in an array.
For example, if I am using a 500 row by 4 column array, can I use something similar to the above sumproduct function in VBA making use of the above array to get the sum of the product of the data in only those rows of the array where the data in the row in say the 3rd column of the array equals "test" and the data in the row in say the 4th column of the array equals "thisNum"?
Any suggestions?
I need to do using arrays because the underlying referred workbook is closed and I intend to work closed workbook. You may presume that the data from the referred closed has been fetched and loaded in a 2 dimensional array for processing.
Best regards
Deepak Agarwal