Evaluate() is returning an "Error 2007"

[jcg31]

I am using the Evaluate function to return a value from a string. The string being evaluated is "(AC379+AC380+AC381)/AC385"

The cells hold the values of 193, 22703, 312 & 315322 respectively. The first three are comma formated (accounting with commas, no $ signs) the last number in the series is formated Accounting.

When the evaluate function is run it returns an "Error 2007" value. Anyone have any idea why and how to correct?

Thanks for any help.
Jim

 

[Norie]

Jim

The only way I seem to be able to replicate that particular error code is if the value in A385 on the active sheet is 0.

Have you tried adding worksheet references to the cell references?

 

[jcg31]

Yep, that is what is puzzling, it is a divide by 0 error, but all the cells are populated with numbers. This line of code populates a very large sheet using the calculation across 40 different worksheets and 112 different columns. Each calculation has number in each of the cells being evaluated and they are all throwing the same error and entering "-" into the cell.

 

[pgc01]

Hi

Please replace the statement where you have the Evaluate() with

MsgBox Evaluate("AC385")

to confirm the that the Evaluate() is dividing by 0.

 

[Norie]

That's why you should add a worksheet reference(s) so that the values are picked up from the right worksheet.

Without them the values are going to be those from the active worksheet.

I tried your code without a worksheet reference and if the cells referenced in the formula were empty/0 I got the error.

When I added a reference to a worksheet with values in the cell it worked fine no matter what sheet was active.

 

[jcg31]

The odd thing there is if I eliminate the division portion of the string, the balance of the eval returns the correct sum, which suggests it is picking up the correct sheet, but let me give that a shot.

 

[jcg31]

"Hi

Please replace the statement where you have the Evaluate() with

MsgBox Evaluate("AC385")

to confirm the that the Evaluate() is dividing by 0."

When I replace as suggested above it throws an error and doesn't execute the MsgBox

 

[jcg31]

That's why you should add a worksheet reference(s) so that the values are picked up from the right worksheet.

Without them the values are going to be those from the active worksheet.

I tried your code without a worksheet reference and if the cells referenced in the formula were empty/0 I got the error.

When I added a reference to a worksheet with values in the cell it worked fine no matter what sheet was active.

Here is how I am referencing the appropriate worksheet: I am placing all the relevant worksheets in an array and then incrementing through them, with the string in the variable

Worksheets(WorksheetArray(i)).Evaluate(FmlaString)

Still getting "error 2007"

 

[jcg31]

If instead of :
"(AC379+AC380+AC381)/AC385"
I use
"(AC379+AC380+AC381)+AC385"
It calculates and populates each cell correctly.

So it has to be something with the division its not liking.

 

[Norie]

I meant reference the worksheet in the formula.

="(Sheet1!AC379+Sheet1!AC380+Sheet1!AC381)/Sheet1!AC385"