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)*(Range("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=EBnt code for this ?

Thanks for your help.
Herman

Function OddOne(rng As Range, rng2 As Range)

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


--

HTH

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


<[email protected]> wrote in message
news:[email protected]...
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)*(Range("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

Bob Phillips schreef:

> Function OddOne(rng As Range, rng2 As Range)
>
> OddOne =3D Evaluate("=3DSUMPRODUCT(--(LEFT(" & rng.Address & ",4)+0>1000)=
," & _
> "--(LEFT(" & rng.Address & ",4)+0<2000)," & rng2.Address & ")=
")
> End Function
>
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> <[email protected]> wrote in message
> news:[email protected]...
> 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)*(Range("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=EBnt code for this ?
>=20
> Thanks for your help.
> Herman