| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Values and Formulas - Problem - High Importance
The following line of code gives me a #VALUE!
Cells(R, C).Value = Evaluate("SUMPRODUCT(--(" & RngP5.Address(External:=True) & "=" & Cr1.Address & "), --(" & RngP1.Address(External:=True) & "=" & Cr7.Address & "), --(" & RngP1.Address(External:=True) & "<=" & Cr8.Address & _ "), --(" & RngP3.Address(External:=True) & "=" & Cr3.Address & "), --(" & RngP2.Address(External:=True) & "=" & Cr2.Address & "))") But if I change this from (just the first few char and rest as same) Cells(R, C).Value = Evaluate("SUMPRODUCT to Cells(R, C).Formula = ("=SUMPRODUCT it puts the formula in the worksheet and I get the desired result. What is this??? -- Baapi |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Values and Formulas - Problem - High Importance
Just curious...
If you use .address(external:=true) to all the range variables (including CR1, Cr7, cr8, cr3, cr2) does it help? Baapi wrote: The following line of code gives me a #VALUE! Cells(R, C).Value = Evaluate("SUMPRODUCT(--(" & RngP5.Address(External:=True) & "=" & Cr1.Address & "), --(" & RngP1.Address(External:=True) & "=" & Cr7.Address & "), --(" & RngP1.Address(External:=True) & "<=" & Cr8.Address & _ "), --(" & RngP3.Address(External:=True) & "=" & Cr3.Address & "), --(" & RngP2.Address(External:=True) & "=" & Cr2.Address & "))") But if I change this from (just the first few char and rest as same) Cells(R, C).Value = Evaluate("SUMPRODUCT to Cells(R, C).Formula = ("=SUMPRODUCT it puts the formula in the worksheet and I get the desired result. What is this??? -- Baapi -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Values and Formulas - Problem - High Importance
Baapi
App.evaluate uses array evaluation rules, maybe your formula will not evaluate like that try entering into a cell via range(..).formulaarray = ... If this fails then the formula probably does not calc as an array cheers Simon "Baapi" wrote in message ... The following line of code gives me a #VALUE! Cells(R, C).Value = Evaluate("SUMPRODUCT(--(" & RngP5.Address(External:=True) & "=" & Cr1.Address & "), --(" & RngP1.Address(External:=True) & "=" & Cr7.Address & "), --(" & RngP1.Address(External:=True) & "<=" & Cr8.Address & _ "), --(" & RngP3.Address(External:=True) & "=" & Cr3.Address & "), --(" & RngP2.Address(External:=True) & "=" & Cr2.Address & "))") But if I change this from (just the first few char and rest as same) Cells(R, C).Value = Evaluate("SUMPRODUCT to Cells(R, C).Formula = ("=SUMPRODUCT it puts the formula in the worksheet and I get the desired result. What is this??? -- Baapi |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Values and Formulas - Problem - High Importance
But I have this line below
Cells(R, C + 7).Value = Evaluate("SUMPRODUCT(--(" & RngP1.Address(External:=True) & "=" & Cr1.Address & "),--(" & RngP4.Address(External:=True) & "=" & Cr2.Address & "))") Working in another part of my code. -- Baapi "Dave Peterson" wrote: Just curious... If you use .address(external:=true) to all the range variables (including CR1, Cr7, cr8, cr3, cr2) does it help? Baapi wrote: The following line of code gives me a #VALUE! Cells(R, C).Value = Evaluate("SUMPRODUCT(--(" & RngP5.Address(External:=True) & "=" & Cr1.Address & "), --(" & RngP1.Address(External:=True) & "=" & Cr7.Address & "), --(" & RngP1.Address(External:=True) & "<=" & Cr8.Address & _ "), --(" & RngP3.Address(External:=True) & "=" & Cr3.Address & "), --(" & RngP2.Address(External:=True) & "=" & Cr2.Address & "))") But if I change this from (just the first few char and rest as same) Cells(R, C).Value = Evaluate("SUMPRODUCT to Cells(R, C).Formula = ("=SUMPRODUCT it puts the formula in the worksheet and I get the desired result. What is this??? -- Baapi -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Values and Formulas - Problem - High Importance
Maybe you're activating different worksheets in your code.
What happens when you added (external:=true) to those other variables (and now, in every spot!). Baapi wrote: But I have this line below Cells(R, C + 7).Value = Evaluate("SUMPRODUCT(--(" & RngP1.Address(External:=True) & "=" & Cr1.Address & "),--(" & RngP4.Address(External:=True) & "=" & Cr2.Address & "))") Working in another part of my code. -- Baapi "Dave Peterson" wrote: Just curious... If you use .address(external:=true) to all the range variables (including CR1, Cr7, cr8, cr3, cr2) does it help? Baapi wrote: The following line of code gives me a #VALUE! Cells(R, C).Value = Evaluate("SUMPRODUCT(--(" & RngP5.Address(External:=True) & "=" & Cr1.Address & "), --(" & RngP1.Address(External:=True) & "=" & Cr7.Address & "), --(" & RngP1.Address(External:=True) & "<=" & Cr8.Address & _ "), --(" & RngP3.Address(External:=True) & "=" & Cr3.Address & "), --(" & RngP2.Address(External:=True) & "=" & Cr2.Address & "))") But if I change this from (just the first few char and rest as same) Cells(R, C).Value = Evaluate("SUMPRODUCT to Cells(R, C).Formula = ("=SUMPRODUCT it puts the formula in the worksheet and I get the desired result. What is this??? -- Baapi -- Dave Peterson -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Values and Formulas - Problem - High Importance
I didn't try as I had to deliver this module. But, I deleted all the
(External:= True) and added absolute sheet references and it worked. But surely at some point I want to change this back to External:= True. -- Baapi "Dave Peterson" wrote: Maybe you're activating different worksheets in your code. What happens when you added (external:=true) to those other variables (and now, in every spot!). Baapi wrote: But I have this line below Cells(R, C + 7).Value = Evaluate("SUMPRODUCT(--(" & RngP1.Address(External:=True) & "=" & Cr1.Address & "),--(" & RngP4.Address(External:=True) & "=" & Cr2.Address & "))") Working in another part of my code. -- Baapi "Dave Peterson" wrote: Just curious... If you use .address(external:=true) to all the range variables (including CR1, Cr7, cr8, cr3, cr2) does it help? Baapi wrote: The following line of code gives me a #VALUE! Cells(R, C).Value = Evaluate("SUMPRODUCT(--(" & RngP5.Address(External:=True) & "=" & Cr1.Address & "), --(" & RngP1.Address(External:=True) & "=" & Cr7.Address & "), --(" & RngP1.Address(External:=True) & "<=" & Cr8.Address & _ "), --(" & RngP3.Address(External:=True) & "=" & Cr3.Address & "), --(" & RngP2.Address(External:=True) & "=" & Cr2.Address & "))") But if I change this from (just the first few char and rest as same) Cells(R, C).Value = Evaluate("SUMPRODUCT to Cells(R, C).Formula = ("=SUMPRODUCT it puts the formula in the worksheet and I get the desired result. What is this??? -- Baapi -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Eliminating the high 4 values | Excel Worksheet Functions | |||
| High and Low values in column | New Users to Excel | |||
| ID High Values | Excel Discussion (Misc queries) | |||
| sumif remove high/low values | Excel Discussion (Misc queries) | |||
| Set an Excel generated CDO email's priority or importance to 'high' or'highest'... | Excel Programming | |||
Linear Mode
