ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Please Help - "SOS" (https://www.excelbanter.com/excel-programming/341169-please-help-sos.html)

Baapi
Please Help - "SOS"
 
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
--
Baapi

Tom Ogilvy
Please Help - "SOS"
 
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.

--
Regards,
Tom Ogilvy

"Baapi" wrote in message
...
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
--
Baapi

Dave Peterson
Please Help - "SOS"
 
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.



Baapi wrote:

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
--
Baapi

--

Dave Peterson

Dave Peterson
Please Help - "SOS"
 
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 wrote:

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.

Baapi wrote:

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
--
Baapi

--

Dave Peterson

--

Dave Peterson

Baapi
Please Help - "SOS"
 
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


"Dave Peterson" wrote:

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 wrote:

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.

Baapi wrote:

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
--
Baapi

--

Dave Peterson

--

Dave Peterson

Dave Peterson
Please Help - "SOS"
 
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.

Baapi wrote:

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

"Dave Peterson" wrote:

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 wrote:

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.

Baapi wrote:

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
--
Baapi

--

Dave Peterson

--

Dave Peterson


--

Dave Peterson

Baapi
Please Help - "SOS"
 
Working!!!!! Thats great... thanks a ton Dave
--
Baapi


"Dave Peterson" wrote:

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.

Baapi wrote:

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

"Dave Peterson" wrote:

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 wrote:

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.

Baapi wrote:

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
--
Baapi

--

Dave Peterson

--

Dave Peterson


--

Dave Peterson


All times are GMT +1. The time now is 07:24 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com