Capture te first n digits of a range
 

Hello,
I'm writing a UDF that defines R as range(A1:A10)which contains either
nothing or integer numbers 10000.
The UDF uses a multi-condition sum that should look like :

Application.Sumproduct
((Range("A1:A10")1000)*(Range("A1:A10")<2000)*(Ra nge("B1:B10")))
But the problem is that instead of range("A1:A10") I want the left 4
digits of A1:A10 to compare with.
What is the most efficiënt code for this ?

Thanks for your help.
Herman

Capture te first n digits of a range
 

Function OddOne(rng As Range, rng2 As Range)

OddOne = Evaluate("=SUMPRODUCT(--(LEFT(" & rng.Address & ",4)+01000)," & _
"--(LEFT(" & rng.Address & ",4)+0<2000)," & rng2.Address & ")")
End Function


--

HTH

RP
(remove nothere from the email address if mailing direct)


wrote in message
ups.com...
Hello,
I'm writing a UDF that defines R as range(A1:A10)which contains either
nothing or integer numbers 10000.
The UDF uses a multi-condition sum that should look like :

Application.Sumproduct
((Range("A1:A10")1000)*(Range("A1:A10")<2000)*(Ra nge("B1:B10")))
But the problem is that instead of range("A1:A10") I want the left 4
digits of A1:A10 to compare with.
What is the most efficiënt code for this ?

Thanks for your help.
Herman

Capture te first n digits of a range
 

Bob Phillips schreef:

Function OddOne(rng As Range, rng2 As Range)

OddOne = Evaluate("=SUMPRODUCT(--(LEFT(" & rng.Address & ",4)+01000)," & _
"--(LEFT(" & rng.Address & ",4)+0<2000)," & rng2.Address & ")")
End Function


--

HTH

RP
(remove nothere from the email address if mailing direct)


wrote in message
ups.com...
Hello,
I'm writing a UDF that defines R as range(A1:A10)which contains either
nothing or integer numbers 10000.
The UDF uses a multi-condition sum that should look like :

Application.Sumproduct
((Range("A1:A10")1000)*(Range("A1:A10")<2000)*(Ra nge("B1:B10")))
But the problem is that instead of range("A1:A10") I want the left 4
digits of A1:A10 to compare with.
What is the most efficiënt code for this ?

Thanks for your help.
Herman