| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel screen capture to capture cells and row and column headings | Excel Discussion (Misc queries) | |||
| In a range of months can I capture the most current month entry? | Excel Discussion (Misc queries) | |||
| Macro to capture cell value then use it for a relative range selec | Excel Programming | |||
| Capture Dynamic Range address into Modeless Form | Excel Programming | |||
| Delete Rows & Capture Range | Excel Programming | |||
Linear Mode
