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 !!

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)


--
Regards

Roger Govier


"gmac" wrote in message
oups.com...
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 !!

On Apr 9, 4:34 pm, "Roger Govier"
wrote:
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)

--
Regards

Roger Govier

"gmac" wrote in message

oups.com...

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 !!

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

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=""no t
sent"")*rng4)"
I = Evaluate(temp)


--
Regards

Roger Govier


"gmac" wrote in message
ups.com...
On Apr 9, 4:34 pm, "Roger Govier"
wrote:
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)

--
Regards

Roger Govier

"gmac" wrote in message

oups.com...

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 !!

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

On Apr 10, 1:40 pm, "Roger Govier"
wrote:
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=""no t
sent"")*rng4)"
I = Evaluate(temp)

--
Regards

Roger Govier

"gmac" wrote in message

ups.com...

On Apr 9, 4:34 pm, "Roger Govier"
wrote:
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)

--
Regards

Roger Govier

"gmac" wrote in message

groups.com...

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 !!

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

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