Urgent Help Required! Double string macro to remove the 1st & last part of Forumla

[Chrisbaberd]

Hello!

Help urgently required!

I need to amend about 3000 cells on on worksheet to remove the first part of the formula and also the last part of the formula!

I cant just amend it and drag it down as its pulling from 20 different tabs!!!!!

Because I will be removing the (())'s Excel's automatic formula checking keeps kicking in on the normal find and replace (with nothing) and trying to auto-fix it for me.

Is there a way to build a basic macro to remove the desired text from the beginning and end of a formula without it being interrupted by the Excel auto-correction which we have been unable to disable?

Each cell has different contend in the middle as it is an oracle based application to load data which each cells loads with different dimensions which are being pulled from all over the sheet.

The formula is posted below and the part I need to remove are coloured in red!

=IF(G$5="CurFcstBudRate",IF('Fixed Assets'!G99=0,"",HsSetValue('Fixed Assets'!G99,$B$3,"Scenario#"&G$5&";Year#"&$B$5&";Period#"&G$6&";View#<Scenario View>;Entity#"&$B$2&";Value#<Entity Currency>;Account#"&$A100&";ICP#[ICP None];Custom1#"&$B100&";Custom2#[None];Custom3#[None];Custom4#BUManInput")),"")

Please help today if possible as we have a deadline pending!

Eternally Great-full!

Chris!

[arlu1201]

If you remove it and drag down, even if it contains 20 different tabs, wont it work?

Also, should this 1 formula be edited for all 3000 cells? Are those 3000 cells all in 1 column?

[Chrisbaberd]

Hi Arlette,

There are about 900 rows and 12 columns each.

The tabs that they are pulling from are not in order so the middle part of the formula is correct to go to each specific location on that tab but my dragging down it wont pick up the correct data from the desired tab.Spreadsheet example.jpg

[arlu1201]

Instead of a .jpg, please attach a sample excel file. It will help us to work out a faster solution for you.

[Chrisbaberd]

Hello,

Have attached my template,

it connects to Oracle to retrieve and submit data but you will still be able to see the formula without this connection!

Thank you

[Chrisbaberd]

BTW G20 on the BS_Fcst_Load has been manually amended so we can see what needs to happen ideally to the rest of the cells!

[abousetta]

Try this on a copy of your workbook as the results CANNOT be undone.

Please Login or Register  to view this content.

[abousetta]

Just tested on your sample and seems to work fine even though it takes a few minutes to run. I have added a few extra lines above to speed things up a bit. Will check back in a few hours to see if it worked for you.

[Chrisbaberd]

Hello! Not sure what I am doing wrong to get the "Compile Error, Invalid Outside Procedure"Macro Error.jpg

[abousetta]

My mistake. I forgot to paste the first line. Add the line:

Please Login or Register  to view this content.

at the beginning to the code.

[Chrisbaberd]

Thanks for the updated script! I cant seem to get it working! I have amended the macro and ran it which froze up after a short while then after it unfreezes and the bits we wanted to remove are still there Have you still got the file that worked?

[Chrisbaberd]

Just tried on another machine also and it just timed out! Any chance we can pinch your test file to copy paste formula?

[abousetta]

I am rerunning it from work with a few tweaks. Hopefully will get back to you soon.

abousetta

[Chrisbaberd]

Thanks you! Your help is really appriciated!

[abousetta]

Hi Chris,

OK, I think this should work now. I tested on a small sample because it keeps asking me for the location of the add-in you have on your system. So I didn't get a chance to run it on the whole workbook, but let me know if still is not working.

abousetta

Please Login or Register  to view this content.

[Chrisbaberd]

Still doesnt do it! It doesnt time out but leaves the undesired data still! Did your version work? I am still in the office at 9pm trying to get this stubborn template working

[abousetta]

Have a look at the attachment. Maybe I didn't understand what you were trying to remove correctly.

abousetta

[bstubbs]

One thing that might help is that the formula checking would be turned off if you changed the Recalc to manual. At least, my quick testing indicated that... Good Luck.

[abousetta]

Thanks bstubbs. I turned off link updating and that allowed the formulas to be updated. Thanks for the headsup and I will keep this in mind.

[Chrisbaberd]

Thank you so much! it worked! You have really really helped me out

[abousetta]

Glad it all worked out.

Good luck.

abousetta