Enter Array formula with VBA

Hello all,

I would like to enter the below formula as part of a macro:

{=SUM(SUMIF('ORIGINAL DEBTORS'!D2:D30649,Gulf!A2:A213,'ORIGINAL DEBTORS'!BF2:BF30649))/1000}

I have a list of clients, and a larger list of projects related to those clients (and others), the formula sums the debts for each client in the project list.

The problem I have is that both ranges will be different each time I run it. I have variables within the code to refer to these ranges but am not sure how to use these within the array.

Does anyone know if this is possible?

Many thanks

Rich

Please Login or Register  to view this content.

Where range1, range2 and range3 are your variables

Thank you yudlugar, I will have a go at incorporating that.

Hi,

I'm getting a type mismatch on the array line, I can't find any error values within the data, and can't see why this would be, have I made an error somewhere?

Please Login or Register  to view this content.

Thanks
Rich

You've set your Rng variables as a range, you need to use it as a string in the formulaarray.
Basically, the variable needs to return:
"'ORIGINAL DEBTORS'!D2:D30649"
As it's always on the same sheet though you shoudl be able to use:

Please Login or Register  to view this content.

Another thing to note, if you define several variables on the same line like this:

Please Login or Register  to view this content.

I'm pretty sure Rng1 and Rng2 will be variants and only Rng3 is a range, you need:

Please Login or Register  to view this content.

I'm not 100% on that though, I could be wrong.

I'm struggling to get this to work within the context of my macro - still learning. I will keep this as something to work on in my spare time, and move on in the meantime.

I appreciate your help, thank you very much.

Rich

I'm pretty sure Rng1 and Rng2 will be variants and only Rng3 is a range

That is correct.