Problem using formulas within macro + problem with defined name.

**rain4u** · 03-11-2011, 03:44 AM

Hi
Im having 2 problems with this following formula

Please Login or Register  to view this content.

First problem is that for some reason it wont let me use defined name for the last IF statement. At the moment its like this

Please Login or Register  to view this content.

But it should look loke this.

Please Login or Register  to view this content.

So all in all it would look like this:

Please Login or Register  to view this content.

Second problem is that Im trying to use macro to populate this formula in column AM. So far I have not had any success. I have tried the 2 following macros with no luck.

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

I have attached the xls ofcourse. Any help woud be greatly appreciated.

**DonkeyOte** · 03-11-2011, 03:57 AM

In your first attempt you're using FormulaR1C1 but A1 referencing.

The second attempt would work if you were using a standard UK regional setting - ie comma as delimiter rather than semi-colon.

If you're using a semi-colon delimiter it will not as FormulaLocal utilises the Regional syntax rather than US-centric syntax per Formula

Given the above I'd suggest you adopt an A1 reference style with US-centric syntax:

Please Login or Register  to view this content.

Note: I've not reviewed the performance of the formula itself.

**rain4u** · 03-11-2011, 04:39 AM

Thank you!
Works like a charm! You are a star!

Problem using formulas within macro + problem with defined name.

LinkBack

Thread Tools

Rate This Thread

Display

Problem using formulas within macro + problem with defined name.

Re: Problem using formulas within macro + problem with defined name.

Re: Problem using formulas within macro + problem with defined name.

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions