Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using SUMPRODUCT where one of the columns can be either TEST or aNUMBER
I am executing the following SUMPRODUCT string in VBA, which works
fine: SumProduct((TUESDAY!$D$2:$D$35=7659)*(TUESDAY!$E$2 :$E$35="Yes")* (TUESDAY!$M$2:$M$35)) My problem is that column "D" (being tested in this case for 7659 (which changes as it is a variable used to build the above string) in this case is a number. This can sometimes start with a letter (i.e. W####), in which case I need to put quotes into my formula around the first variable (which is "7659" in the example above). However, if I want the variable to be a number (as in the example above), the SumProduct function will return 0 if I put quotes around it since it is evaluated as a number (even if I have the Type as Text for column "D"). My question: Is there a way to have this function work either way, or do I need to test my variable to see if it is a Number or a String and conditionally form my SumProduct string accordingly? Thanks! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using SUMPRODUCT where one of the columns can be either TEST or aNUMBER
I think I'd test for a real number...
Dim myVar As Variant Dim QtMark As String myVar = "1234" ' or "a1234" If TypeName(myVar) = "String" Then 'it's a string, add the double quotes QtMark = """" Else 'it's not a string QtMark = "" End If Then build your formula with the variable surrounded by QtMark. jtertin wrote: I am executing the following SUMPRODUCT string in VBA, which works fine: SumProduct((TUESDAY!$D$2:$D$35=7659)*(TUESDAY!$E$2 :$E$35="Yes")* (TUESDAY!$M$2:$M$35)) My problem is that column "D" (being tested in this case for 7659 (which changes as it is a variable used to build the above string) in this case is a number. This can sometimes start with a letter (i.e. W####), in which case I need to put quotes into my formula around the first variable (which is "7659" in the example above). However, if I want the variable to be a number (as in the example above), the SumProduct function will return 0 if I put quotes around it since it is evaluated as a number (even if I have the Type as Text for column "D"). My question: Is there a way to have this function work either way, or do I need to test my variable to see if it is a Number or a String and conditionally form my SumProduct string accordingly? Thanks! -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to prevent a number stored as text from converting to anumber | Excel Programming | |||
Sumproduct with weekend day test | Excel Worksheet Functions | |||
SumProduct but sometimes don't test some criteria | Excel Worksheet Functions | |||
Function for finding a cell with Anumber in it | Excel Worksheet Functions | |||
Using a formula on anumber of cells in a column that the user inpu | Excel Discussion (Misc queries) |