On Apr 10, 1:40 pm, "Roger Govier" <r...@technologyNOSPAM4u.co.uk>
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=""not
> sent"")*rng4)"
> I = Evaluate(temp)
>
> --
> Regards
>
> Roger Govier
>
> "gmac" <cug...@gmail.com> wrote in message
>
> news:(E-Mail Removed)...
>
> > On Apr 9, 4:34 pm, "Roger Govier" <r...@technologyNOSPAM4u.co.uk>
> > 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" <cug...@gmail.com> wrote in message
>
> >>news:(E-Mail Removed)...
>
> >> > 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 ?
|
Similar Threads
