[Solved] Error checking for 0 in script

[flebber]

Firstly this post rlates to this threadhttp://www.excelforum.com/excel-gene...-one-cell.html but for clarity am starting a new thread as this will be a long post.

I am using a version of a script posted by Paul in that thread, the prupose of which was to split numbers across cells from the format

Please Login or Register  to view this content.

Please Login or Register  to view this content.

I need to introduce some error checking or similar as I now have found that when a field contains to many zero's and no dollars macro cannot complete. example formats

Please Login or Register  to view this content.

I would need the columns to then contain values so originating colums 0.00 and all other columns 0. maybe could use default values?

This is the script I am currently using.

Please Login or Register  to view this content.

[martindwilson]

what do you mean too many 0?

[flebber]

what do you mean too many 0?

Oh when typing this I realised why it is not working it is not the zero's as such but when there is a "0-0-0-0" type value there is no "$86550.00" value(or similar) and so numbers aren't copied to columns.

[martindwilson]

did you try the code i gave you ?
if you wish to retain the original in a use

Please Login or Register  to view this content.

this also formats the column to general to lose the $

or try the original i posted which removes original and splits between cols a b c d

Please Login or Register  to view this content.

if you remove

Please Login or Register  to view this content.

it will auto format cell as currency

[flebber]

What range is b:b referring to?

Please Login or Register  to view this content.

treying a range of different values but can't get it to work.

Please Login or Register  to view this content.

[flebber]

I have got it to work, but the output format output is

Please Login or Register  to view this content.

I am trying to get the output as so the original cell no longer has the delimited values but does retain the $ value.

Please Login or Register  to view this content.

This is my modified version of your code - I only changed column and cell refs to match.

Please Login or Register  to view this content.

[martindwilson]

Please Login or Register  to view this content.

[flebber]

Thanks your help is very appreciated , during the script when it goes to copy the cells I receive a message "Do you want to replace the contents of the destination cells". Is there a way to auto accept this because I have a couple more columns to apply your script to and would prefer not to need to accept prompt.

Where there is a cell with no dollar value should I run a loop on the column at the end to fill in bank cells with $0. I will be running calculation of various cells later and don't want a blank cell to error out the math.

[martindwilson]

youll get that message if the cells in the adjacent columns have stuff in them ,just insert enough columns
perhaps add at beginning of code

Please Login or Register  to view this content.

not the prettiest but that will insert 4 columns after AH

[flebber]

I have already inserted 4 columns in after AH. But when taking screenupdating off I notice your script copies all of AH to AI (unless my cell referencing is wrong) and then prompts as above before copying data.

My code remains the same as posted above.

[martindwilson]

sorry which exact version are you using?
you have

Please Login or Register  to view this content.

thats whats giving the warning

[flebber]

Please Login or Register  to view this content.

[martindwilson]

works fine when i run it
try this work book

[flebber]

I sort of know what is wrong, I have attempted to change it but recevie a small error. essentially I am thinking that because my AH1 cell contains a table name the pop up relates to name copying not number copying.

However when I changed it to AH2 it errored that "data was longer than the worksheet". What do I need to change to fix it?

Please Login or Register  to view this content.

Changed it to AH2 but that hasn't fixed it.

Please Login or Register  to view this content.

[martindwilson]

Please Login or Register  to view this content.

i cant remember how to dynamically set the range grrr but 1000 should cover what you have

[flebber]

Yes thats working Awesome thanks so much.