Problems with Variables

H

Helen

Why would the result for TotalR = 0 (when it should return 226,550.00)
whereas TotalO returns the correct amount.

TotalR = Sheets("Imported Requisition Data").Evaluate("SUMPRODUCT(--(L9:L" &
myRowsR + 8 & "=" & OrderR & "),(G9:G" & myRowsR + 8 & "))")

TotalO = Sheets("JDE Data").Evaluate("SUMPRODUCT(--(B6:B" & myRowsO + 10

I've also noticed that results for other variables related to the ("Imported
Requisition Data") sheet appear as "725", whereas the results from ("JDE
Data") sheet comes through as 725.

I think this is part of the problem, but I've checked that the data in both
sheets is formatted the same way, and now I don't know what else to try.
 
J

JLatham

First thing I see in the first formula is that you're not coercing the second
half of the formula, try replacing
),(G9:G"...
with
),--(G9:G"...
although that really shouldn't affect the results the way you're using this.

As you noted, the format of the imported data appears to be a factor also -
the fact that it's showing up as "725" instead of just 725 indicates it's
stored as text instead of as a numeric value.

Try selecting all of the cells in that have the values shown with " "
surrounding them, and use Edit | Replace and select " as the character to
find and leave the replace with portion empty, click [Replace All]. I think
this is the one that's going to fix the problem.
 
B

bj

it is not needed to coerce the second half of the formula, it is not a
logical statement unless it would help the equation recognise the value (I
don't know, but don't believe it does)

I agree it is a text versus number issue

try maybe

TotalR = Sheets("Imported Requisition Data").Evaluate("SUMPRODUCT(--(L9:L" &
myRowsR + 8 & "=" & OrderR & "),Value(Trim(G9:G" & myRowsR + 8 & "))")


JLatham said:
First thing I see in the first formula is that you're not coercing the second
half of the formula, try replacing
),(G9:G"...
with
),--(G9:G"...
although that really shouldn't affect the results the way you're using this.

As you noted, the format of the imported data appears to be a factor also -
the fact that it's showing up as "725" instead of just 725 indicates it's
stored as text instead of as a numeric value.

Try selecting all of the cells in that have the values shown with " "
surrounding them, and use Edit | Replace and select " as the character to
find and leave the replace with portion empty, click [Replace All]. I think
this is the one that's going to fix the problem.

Helen said:
Why would the result for TotalR = 0 (when it should return 226,550.00)
whereas TotalO returns the correct amount.

TotalR = Sheets("Imported Requisition Data").Evaluate("SUMPRODUCT(--(L9:L" &
myRowsR + 8 & "=" & OrderR & "),(G9:G" & myRowsR + 8 & "))")

TotalO = Sheets("JDE Data").Evaluate("SUMPRODUCT(--(B6:B" & myRowsO + 10

I've also noticed that results for other variables related to the ("Imported
Requisition Data") sheet appear as "725", whereas the results from ("JDE
Data") sheet comes through as 725.

I think this is part of the problem, but I've checked that the data in both
sheets is formatted the same way, and now I don't know what else to try.
Click to expand...
Click to expand...
 
D

Dave Peterson

If the other suggestions don't help, you may want to develop the formula that
works in that worksheet.

Then share that formula in your followup post.

I was gonna guess that OrderR is a number, but column L is text that looks like
a number.

.... + 8 & "=" & chr(34) & OrderR & chr(34) & ")....

(surround the OrderR in double quotes)

or maybe something like this:

.... + 8 & "=" & chr(34) & format(OrderR, "0000") & chr(34) & ")....
 
Top