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!

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