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

 

[jcg31]

Hmmm that will take some rework to append that to the string, but will give it a shot tonight. Thanks,
Jim

 

[Norie]

Jim

Why are you using Evaluate?
Just wondering if there's some other approach that would give the same results.

 

[jcg31]

Jim

Why are you using Evaluate?
Just wondering if there's some other approach that would give the same results.

I manage a division of businesses that is one of five other divisions. Each month I receive a report that contains a full P&L and other performance metrics. Each of the businesses in all the divisions is placed on a single spreadsheet in adjacent columns with the rows containing the various accounts or metrics. The application allows me to review the data in interesting and unlimited ways by selecting any line item (also via dropdown) and then using operands (also via dropdown) to construct a formula that is pressed across companies, months and years to return a ranked (if needed) presentation of any measure I can construct. So if I want to see how each business ranks according to the greatest ratio of product sold per salesperson divided by the number of complaints per CSR multiplied by the square root of the number of department heads employed as of June, I can, or will once I fix this division problem I am currently experiencing.

Thanks,
Jim

 

[jcg31]

Another oddity to this that may provide a clue to someone. The numbers on these spreadsheets are populated from a database (that I don't have access to). If I go into the cells and type in the numbers (over the existing ones), then the script works as it should.

Worksheets("SummarySheet").cells(1,1) = Worksheets(Worksheets("sheet1").Evaluate("(A1+A2+A3)/A4")

 

[konew1]

try using /(a4+0)

 

[JWilder]

Try "(AC379+AC380+AC381)/Value(AC385)"
Jim

 

[Norie]

Sounds like the data from the database isn't being imported properly so the 'numbers' aren't being recognised as such.

The two formula based solutions should work but it might be worth seeing if it's possible to 'fix' the data itself.