Array formulas + more than 255 chars

[gmac]

HI

Iam executing Array formulas in VB. This is a snippet of the code

Dim temp As String
Dim I as Integer
temp="SUM(IF((('Data'!$CU$7:$CU$4978=2007)* ('Data'!$c$7:$c
$4978="XYZ")),IF('Data'!$K$7:$K$4978="Not Sent",'Data'!$G$7:$G$4978,0),
0))"
I = ActiveSheet.Evaluate(temp)

This temp string is dynamically built based on the user selection. The
code works fine as long as the temp string length is less than 255.
However, if the length exceeds more than 255 I get a Type mismatch
error.

I tried renaming the Data worksheet with 'D' and stuff like that but
my conditions are too lengthy that makes temp more than 255. Can
anyone help me with a workaround ?

Thanks !!

 

[Roger Govier]

Hi

Dim temp As String
Dim rng1 as Range, rng2 as Range, rng3 as Range, rng4 as range
Dim I as Integer
set rng1 = Sheets("Data").Range("CU7:CU4978")
set rng2 = Sheets("Data").Range("C7:C4978")
set rng3 = Sheets("Data").Range("K7:K4978")
set rng4 = Sheets("Data").Range("G7:G4978")
temp="SUM(IF(((rng1=2007)* (rng2="XYZ")),IF(rng3="Not Sent",rng4,0),0))"
I = ActiveSheet.Evaluate(temp)

 

[gmac]

Hi

Dim temp As String
Dim rng1 as Range, rng2 as Range, rng3 as Range, rng4 as range
Dim I as Integer
set rng1 = Sheets("Data").Range("CU7:CU4978")
set rng2 = Sheets("Data").Range("C7:C4978")
set rng3 = Sheets("Data").Range("K7:K4978")
set rng4 = Sheets("Data").Range("G7:G4978")
temp="SUM(IF(((rng1=2007)* (rng2="XYZ")),IF(rng3="Not Sent",rng4,0),0))"
I = ActiveSheet.Evaluate(temp)

Thanks Roger for your help. However Iam running into a problem. When
Iam using the code you have given VB evaulates rng1,rng2 as string
objects.

It is like executing SUM(IF(((rng1=2007)* (rng2="XYZ")),IF(rng3="Not
Sent",rng4,0),0))

However, when I try using the following code
temp="SUM(IF((("+rng1+"=2007)* ("+rng2+"="XYZ")),IF("+rng3+"="Not
Sent",rng4,0),0))"
It gives me a Type mismatch error.. Any suggestions ?

Thanks again for your help

 

[Roger Govier]

Hi

Try using Sumproduct instead.
This works fine for me.

Dim temp As String
Dim rng1 as Range, rng2 as Range, rng3 as Range, rng4 as range
Dim I as Integer
set rng1 = Sheets("Data").Range("CU7:CU4978")
set rng2 = Sheets("Data").Range("C7:C4978")
set rng3 = Sheets("Data").Range("K7:K4978")
set rng4 = Sheets("Data").Range("G7:G4978")

temp = "=SUMPRODUCT((rng1=2007)*(rng2=""xyz"")*(rng3=""not
sent"")*rng4)"
I = Evaluate(temp)

 

[gmac]

Hi

Try using Sumproduct instead.
This works fine for me.

Dim temp As String
Dim rng1 as Range, rng2 as Range, rng3 as Range, rng4 as range
Dim I as Integer
set rng1 = Sheets("Data").Range("CU7:CU4978")
set rng2 = Sheets("Data").Range("C7:C4978")
set rng3 = Sheets("Data").Range("K7:K4978")
set rng4 = Sheets("Data").Range("G7:G4978")

temp = "=SUMPRODUCT((rng1=2007)*(rng2=""xyz"")*(rng3=""not
sent"")*rng4)"
I = Evaluate(temp)

When I implement this code I get an Type mismatch error. Iam using
Office 2000 ? Has it something to do with this error ?