Using code instead of worksheet functions

[Gareth]

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")*(rng3=""))

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(rng2)=""B"")*(rng3=""""))")

Thanks in advance.

Gareth

 

[Tom Ogilvy]

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

 

[Sharad Nandwani]

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.

 

[Tom Ogilvy]

Neither one of those will work. You have to double double-quotes within
strings.

 

[Gareth]

Tom

Whats is the difference between vba object references and defined names?

Gareth

 

[Tom Ogilvy]

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.

 

[Tom Ogilvy]

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.

 

[Dana DeLouis]

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")*IsBlank(rng3))]