Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using code instead of worksheet functions
I find myself in the position of having to use code instead of functions.
My original function in E7 was as follows: =SUMPRODUCT((rng={"L","CL"})*(LEFT(rng2)="B")*(rng 3="")) I have put the following into the Worksheet Activate event of the new file but it doesn't appear to work: Range("E7").Value = Evaluate("SUMPRODUCT((rng={""L"",""CL""})*(LEFT(rn g2)=""B"")*(rng3=""""))") Thanks in advance. Gareth |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using code instead of worksheet functions
From the immediate window:
? Evaluate( "SUMPRODUCT((rng={""L"",""CL""})*(LEFT(rng2)=""B"" )*(rng3=""""))") 2 With defined names of rng, rng2, rng3, it worked fine for me. Ir rng, rng2 and rng3 are vba object references, then that will not work. In that case, to fix: rng.name = "rng" rng1.name = "rng1" rng2.name = "rng2" ' then use your formula -- Regards, Tom Ogilvy Gareth wrote in message ... I find myself in the position of having to use code instead of functions. My original function in E7 was as follows: =SUMPRODUCT((rng={"L","CL"})*(LEFT(rng2)="B")*(rng 3="")) I have put the following into the Worksheet Activate event of the new file but it doesn't appear to work: Range("E7").Value = Evaluate("SUMPRODUCT((rng={""L"",""CL""})*(LEFT(rn g2)=""B"")*(rng3=""""))") Thanks in advance. Gareth |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using code instead of worksheet functions
U can use simply use
Range("E7").value = "==SUMPRODUCT((rng={"L","CL"})*(LEFT (rng2)="B")*(rng3=""))" or Range("E7").formula = "==SUMPRODUCT((rng={"L","CL"})*(LEFT (rng2)="B")*(rng3=""))" It shoud work if you are only trying to put a formula. -----Original Message----- I find myself in the position of having to use code instead of functions. My original function in E7 was as follows: =SUMPRODUCT((rng={"L","CL"})*(LEFT(rng2)="B")*(rn g3="")) I have put the following into the Worksheet Activate event of the new file but it doesn't appear to work: Range("E7").Value = Evaluate("SUMPRODUCT((rng={""L"",""CL""})*(LEFT(r ng2) =""B"")*(rng3=""""))") Thanks in advance. Gareth . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using code instead of worksheet functions
Neither one of those will work. You have to double double-quotes within
strings. -- Regards, Tom Ogilvy "Sharad Nandwani" wrote in message ... U can use simply use Range("E7").value = "==SUMPRODUCT((rng={"L","CL"})*(LEFT (rng2)="B")*(rng3=""))" or Range("E7").formula = "==SUMPRODUCT((rng={"L","CL"})*(LEFT (rng2)="B")*(rng3=""))" It shoud work if you are only trying to put a formula. -----Original Message----- I find myself in the position of having to use code instead of functions. My original function in E7 was as follows: =SUMPRODUCT((rng={"L","CL"})*(LEFT(rng2)="B")*(rn g3="")) I have put the following into the Worksheet Activate event of the new file but it doesn't appear to work: Range("E7").Value = Evaluate("SUMPRODUCT((rng={""L"",""CL""})*(LEFT(r ng2) =""B"")*(rng3=""""))") Thanks in advance. Gareth . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using code instead of worksheet functions
In VBA
Dim rng as Range set rng = Worksheets("Sheet1").Range("A1:A50") msgbox rng.Address(external:=true) rng is a vba object reference Formulas in the worksheet will have no knowledge of this object reference or what it refers to. Defined Name/ Defined Range If I go into Excel into Insert=Name=define Name: rng Refersto: =Sheet1!$A$1:$A$50 then in a cell =Sum(rng) will get the sum of the values in Sheet1, A1:A50 in VBA if I did rng.Name = "rng" this would created the defined name/range as a I showed manually. If I had a defined name/range "rng", I could refer to it in VBA with Dim varr as Variant varr = Range("rng").Value or if I had defined the variable (object reference) rng as above then varr = rng.value either will create an array (1 to 50, 1 to 1) containing the values held in sheet1, A1:A50 There is no relationship between the defined name rng and the object variable rng except as I make then related through my actions. In the examples, they referred to the same range of cells on sheet1 because I set them up that way. -- Regards, Tom Ogilvy "Gareth" wrote in message ... Tom Whats is the difference between vba object references and defined names? Gareth "Tom Ogilvy" wrote in message ... From the immediate window: ? Evaluate( "SUMPRODUCT((rng={""L"",""CL""})*(LEFT(rng2)=""B"" )*(rng3=""""))") 2 With defined names of rng, rng2, rng3, it worked fine for me. Ir rng, rng2 and rng3 are vba object references, then that will not work. In that case, to fix: rng.name = "rng" rng1.name = "rng1" rng2.name = "rng2" ' then use your formula -- Regards, Tom Ogilvy Gareth wrote in message ... I find myself in the position of having to use code instead of functions. My original function in E7 was as follows: =SUMPRODUCT((rng={"L","CL"})*(LEFT(rng2)="B")*(rng 3="")) I have put the following into the Worksheet Activate event of the new file but it doesn't appear to work: Range("E7").Value = Evaluate("SUMPRODUCT((rng={""L"",""CL""})*(LEFT(rn g2)=""B"")*(rng3=""""))") Thanks in advance. Gareth |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using code instead of worksheet functions
It worked fine for me in my tests - you get a hit only for rows with blank
cells in rng3. (assuming rng, rng2, rng3 point to multirow, single column ranges of equal numbers of cells). make sure your rng3 is referring to the correct range. -- Regards, Tom Ogilvy "Gareth" wrote in message ... Tom Sorted, I am using defined names. But the rng3="""" part doesn't appear to work. Any suggestions? "Tom Ogilvy" wrote in message ... In VBA Dim rng as Range set rng = Worksheets("Sheet1").Range("A1:A50") msgbox rng.Address(external:=true) rng is a vba object reference Formulas in the worksheet will have no knowledge of this object reference or what it refers to. Defined Name/ Defined Range If I go into Excel into Insert=Name=define Name: rng Refersto: =Sheet1!$A$1:$A$50 then in a cell =Sum(rng) will get the sum of the values in Sheet1, A1:A50 in VBA if I did rng.Name = "rng" this would created the defined name/range as a I showed manually. If I had a defined name/range "rng", I could refer to it in VBA with Dim varr as Variant varr = Range("rng").Value or if I had defined the variable (object reference) rng as above then varr = rng.value either will create an array (1 to 50, 1 to 1) containing the values held in sheet1, A1:A50 There is no relationship between the defined name rng and the object variable rng except as I make then related through my actions. In the examples, they referred to the same range of cells on sheet1 because I set them up that way. -- Regards, Tom Ogilvy "Gareth" wrote in message ... Tom Whats is the difference between vba object references and defined names? Gareth "Tom Ogilvy" wrote in message ... From the immediate window: ? Evaluate( "SUMPRODUCT((rng={""L"",""CL""})*(LEFT(rng2)=""B"" )*(rng3=""""))") 2 With defined names of rng, rng2, rng3, it worked fine for me. Ir rng, rng2 and rng3 are vba object references, then that will not work. In that case, to fix: rng.name = "rng" rng1.name = "rng1" rng2.name = "rng2" ' then use your formula -- Regards, Tom Ogilvy Gareth wrote in message ... I find myself in the position of having to use code instead of functions. My original function in E7 was as follows: =SUMPRODUCT((rng={"L","CL"})*(LEFT(rng2)="B")*(rng 3="")) I have put the following into the Worksheet Activate event of the new file but it doesn't appear to work: Range("E7").Value = Evaluate("SUMPRODUCT((rng={""L"",""CL""})*(LEFT(rn g2)=""B"")*(rng3=""""))") Thanks in advance. Gareth |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using code instead of worksheet functions
As Tom mentioned, make sure rng3 is set to the correct range. Also, make
sure it is of the same dimensions as the other ranges. If you have difficulty making sure you have all your double quotes correct (as I often do), then my preference is the following. Again, it's just personal preference, as I find this a little easier to read. [E7] = [SUMPRODUCT((rng={"L","CL"})*(LEFT(rng2)="B")*IsBla nk(rng3))] -- Dana DeLouis Using Windows XP & Office XP = = = = = = = = = = = = = = = = = "Gareth" wrote in message ... Tom Sorted, I am using defined names. But the rng3="""" part doesn't appear to work. Any suggestions? "Tom Ogilvy" wrote in message ... In VBA Dim rng as Range set rng = Worksheets("Sheet1").Range("A1:A50") msgbox rng.Address(external:=true) rng is a vba object reference Formulas in the worksheet will have no knowledge of this object reference or what it refers to. Defined Name/ Defined Range If I go into Excel into Insert=Name=define Name: rng Refersto: =Sheet1!$A$1:$A$50 then in a cell =Sum(rng) will get the sum of the values in Sheet1, A1:A50 in VBA if I did rng.Name = "rng" this would created the defined name/range as a I showed manually. If I had a defined name/range "rng", I could refer to it in VBA with Dim varr as Variant varr = Range("rng").Value or if I had defined the variable (object reference) rng as above then varr = rng.value either will create an array (1 to 50, 1 to 1) containing the values held in sheet1, A1:A50 There is no relationship between the defined name rng and the object variable rng except as I make then related through my actions. In the examples, they referred to the same range of cells on sheet1 because I set them up that way. -- Regards, Tom Ogilvy "Gareth" wrote in message ... Tom Whats is the difference between vba object references and defined names? Gareth "Tom Ogilvy" wrote in message ... From the immediate window: ? Evaluate( "SUMPRODUCT((rng={""L"",""CL""})*(LEFT(rng2)=""B"" )*(rng3=""""))") 2 With defined names of rng, rng2, rng3, it worked fine for me. Ir rng, rng2 and rng3 are vba object references, then that will not work. In that case, to fix: rng.name = "rng" rng1.name = "rng1" rng2.name = "rng2" ' then use your formula -- Regards, Tom Ogilvy Gareth wrote in message ... I find myself in the position of having to use code instead of functions. My original function in E7 was as follows: =SUMPRODUCT((rng={"L","CL"})*(LEFT(rng2)="B")*(rng 3="")) I have put the following into the Worksheet Activate event of the new file but it doesn't appear to work: Range("E7").Value = Evaluate("SUMPRODUCT((rng={""L"",""CL""})*(LEFT(rn g2)=""B"")*(rng3=""""))") Thanks in advance. Gareth |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Getting Excel to return Hiragana using code/char functions | Excel Discussion (Misc queries) | |||
How to convert cell formula functions to code functions | Excel Discussion (Misc queries) | |||
How do I use Excel Built-In Functions in Code? | Excel Worksheet Functions | |||
Where can I see VBA code for financial functions? | Excel Worksheet Functions | |||
Using Built in Functions in VBA Code | Excel Programming |