|
Array formulas + more than 255 chars
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 !! |
Array formulas + more than 255 chars
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 !! |
Array formulas + more than 255 chars
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 |
Array formulas + more than 255 chars
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 |
Array formulas + more than 255 chars
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 ? |
| All times are GMT +1. The time now is 12:05 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com