Excel VBA unable to get numberformat

Question

Hello all,

I've built a spreadsheet which has 3 sheets, with all content pretty much fitting on one screen (so you see,  the spreadsheet isnt very big).

Unfortunately, when attempting  to do a goal seek on the named range, the error comes up for no clear reason.  If i get rid of the goal seek, everything works fine, its only when i try to goal seek something via VBA that i get the "unable to get the number format"

Please advice what thw problem could be?

Answer 1

As far as removal of sensitive/confidential data is concerned, you have done a very right thing.

In fact I forgot to mention that in my earlier communication.

However, you have also removed some function (not sure if that function could be playing any role in the error).

Give me a day or so to have a look at the file you have shared.

In the meantime, just give this a try -

in the screen-shot which you have shared, it can be seen that you are writing

sheets.range().goalseek goal:=0, ...=sheets("").range()

instead of just giving sheets dot range dot ...              try to write it complete  as            workbook.sheets().range().....

See if this helps..

Secondly, have a look at the following site, a similar problem is being discussed - see if that helps.

http://stackoverflow.com/questions/10801537/unable-to-set-the-numberformat-property-of-the-range-class

Answer 2

Need more information to exactly understand your query.

Pls share your .xlsm on OneDrive and share the link here.

Answer 3

Hi Rajesh, 

Thanks for your prompt response.  Unfortunately i've got some sensitive material in the file so i've uploaded an anonymised / simplified version.  Annoyingly the error doesn't happen anymore on this anonymised version so i can't really share the exact problem, but you'll atleast be able to see the entire workbook.

Note that i've also removed a functoin I have created in VB as again this was confidential.  I've attached some screenshots of what the error looks like and where the debugger takes me if i break the running of the macro.  

If i keep pressing ok, it eventually gets me the result that i want, it's just annoying to keep seeing this error pop up.  If i can't fix the issue, is there a way i can get around it and tell it to ignore these errors?  

The problem appears to be with the goal seek in my version as when i remove the goal seek code, it works fine.  Otherwise it comes up with hundreds of the same error message as the goal seek runs through different iterations.

Any help would be much appreciated!

Many thanks

rb_daman

https://1drv.ms/x/s!AnLYSsjCJufXhS25KdC3W4kWyxe6

Answer 4

Hi Rajesh, 

I came back to my spreadhseet again today and it magically seemed to work.  Thanks for sharing the link as well.  I had seen that and so tried to mess around with the formatting and get rid of some of it, but i guess i just needed to restart excel / machine for it to fix itself after making those changes.  

If i do come across this problem again, i will let you know, but in the meantime i think it's safe to say that this issue is solved (on it's own it seems?)

Thanks again

rb_daman