Please Help - "SOS"

[Guest]

Can someone guide me to proper code???

This gives me #NAME?

Dim Cr1 As Date, Cr2 As String, Cr3 As String
Dim RngP1 As Range, RngP2 As Range, RngP3 As Range

eRowP = Worksheets("Production Log").Cells(Rows.Count, 1).End(xlUp).Row
Set RngP1 = Range(Cells(5, 8), Cells(eRowP, 8))
Set RngP2 = Range(Cells(5, 10), Cells(eRowP, 10))
Set RngP3 = Range(Cells(5, 11), Cells(eRowP, 11))

Cr1 = Range("C11").Value
Cr2 = Range("E9").Value
Cr3 = Range("E8").Value

Cells(R, C).Value = Evaluate("SUMPRODUCT(--(" & RngP1.Address & "=" & Cr1 &
"), --(" & RngP2.Address & "=" & Cr2 & "), --(" & RngP3.Address & "=" & Cr3 &
"))")

Thanks a ton

 

[Tom Ogilvy]

You need to enclose the values produced by the cr variables that are strings
within quotes.

instead of

A1:A10=aa

it would be

A1:A10="aa"

Likewise you will have to alter the Date (CR1) to produce a valid formula.

 

[Dave Peterson]

My guess is that Cr1 is not being interpreted as a date in your =sumproduct()
formula.

I'd use:

Option Explicit
Sub testme02()

Dim Cr1 As Range, Cr2 As Range, Cr3 As Range
Dim RngP1 As Range, RngP2 As Range, RngP3 As Range
Dim eRowP As Long
Dim R As Long
Dim C As Long
Dim myFormula As String

R = 1
C = 1

eRowP = Worksheets("Production Log").Cells(Rows.Count, 1).End(xlUp).Row
Set RngP1 = Range(Cells(5, 8), Cells(eRowP, 8))
Set RngP2 = Range(Cells(5, 10), Cells(eRowP, 10))
Set RngP3 = Range(Cells(5, 11), Cells(eRowP, 11))

Set Cr1 = Range("C11")
Set Cr2 = Range("E9")
Set Cr3 = Range("E8")

myFormula = "SUMPRODUCT(--(" & RngP1.Address & "=" & Cr1.Address & ")," & _
"--(" & RngP2.Address & "=" & Cr2.Address & ")," & _
"--(" & RngP3.Address & "=" & Cr3.Address & "))"

Debug.Print myFormula

Cells(R, C).Value = Evaluate(myFormula)

End Sub

My formula looks like:
SUMPRODUCT(--($H$5:$H$34=$C$11),--($J$5:$J$34=$E$9),--($K$5:$K$34=$E$8))

With your code, I'd get something that looks like:
SUMPRODUCT(--($H$5:$H$34=09/26/2005),--($J$5:$J$34=3),--($K$5:$K$34=2))

And 9/26/2005 isn't a date here. It's an arithmetic expression. 9 divided by
26 divided by 2005.

(And my bet that the reason you're getting Name errors is that that cell $C$11
contains a date that's formatted in a very pretty way: September 26, 2005
(maybe???) and excel can't figure out what September means in your formula:

Kind of like this error:
=if(a1=September 26, 2005, "yes","no")

You could make it look like a date:

SUMPRODUCT(--($H$5:$H$34=datevalue("09/26/2005")),
--($J$5:$J$34=3), --($K$5:$K$34=2))

or even:
SUMPRODUCT(--($H$5:$H$34=date(2005,09,26)), --($J$5:$J$34=3), --($K$5:$K$34=2))

But that seems like too much work to me.

 

[Dave Peterson]

I tested with Cr2 and Cr3 being numbers. But if they were text (and I bet they
were), that would cause the Name error.

(just to to be thorough (anal-retentive???).)

 

[Guest]

I think this is fine, But with one problem. This is giving me a 0 result.

Raeson being, as you might know, RngP1, RngP2, RngP3 are set per Production
Log
and this formula is being used in a different Sheet. Though the address is
captured right, it doesnot capture the name of the sheet.

thus,
Instead of calculating based in the range in the sheet production log it
calculates based on the activesheet.

I think one option is to add the name of the sheet just prior to the range
variable, like for example,
SUMPRODUCT(--('Production Log'!" & RngP1.Address & and so on

is there a better option??

--
Baapi

I tested with Cr2 and Cr3 being numbers. But if they were text (and I bet they
were), that would cause the Name error.

(just to to be thorough (anal-retentive???).)

 

[Dave Peterson]

I like to let excel do the work.

I'd change all the .address to .address(external:=true)

That'll include everything--drive/folder/workbook name/worksheet name and cell
address.

I think this is fine, But with one problem. This is giving me a 0 result.

Raeson being, as you might know, RngP1, RngP2, RngP3 are set per Production
Log
and this formula is being used in a different Sheet. Though the address is
captured right, it doesnot capture the name of the sheet.

thus,
Instead of calculating based in the range in the sheet production log it
calculates based on the activesheet.

I think one option is to add the name of the sheet just prior to the range
variable, like for example,
SUMPRODUCT(--('Production Log'!" & RngP1.Address & and so on

is there a better option??

--
Baapi

I tested with Cr2 and Cr3 being numbers. But if they were text (and I bet they
were), that would cause the Name error.

(just to to be thorough (anal-retentive???).)

 

[Guest]

Working!!!!! Thats great... thanks a ton Dave
--
Baapi

I like to let excel do the work.

I'd change all the .address to .address(external:=true)

That'll include everything--drive/folder/workbook name/worksheet name and cell
address.

I think this is fine, But with one problem. This is giving me a 0 result.

Raeson being, as you might know, RngP1, RngP2, RngP3 are set per Production
Log
and this formula is being used in a different Sheet. Though the address is
captured right, it doesnot capture the name of the sheet.

thus,
Instead of calculating based in the range in the sheet production log it
calculates based on the activesheet.

I think one option is to add the name of the sheet just prior to the range
variable, like for example,
SUMPRODUCT(--('Production Log'!" & RngP1.Address & and so on

is there a better option??