need help correcting a macro-run-time erro 6 - overflow

**JMJ123** · 04-25-2013, 05:11 PM

Dear Excel Expert,

I created the following macro to run on a selected range, but if I select for example five cells in a column the macro does not stop at the fifth selection but continues to the very end of the list. I arbitrarily set the do until to .1 because I did not want the "do loop" to stop the macro but rather the other loop to stop it. I think this might be the problem though. And sometimes the macro doesn't run at all but gives me a Run-Time error - 6 - overflow.

Please help. Thanks Andrew

Please Login or Register  to view this content.

**rcm** · 04-25-2013, 05:15 PM

the best calculation for the last row used is

Please Login or Register  to view this content.

selecting a column that is continuously filled up until the end.

**JMJ123** · 04-25-2013, 07:45 PM

Thank you rcm for the reply. Where in the macro do I insert your suggested correction. And do I need to delete any of my lines?

**rcm** · 04-26-2013, 12:46 AM

your problem is deeper than I thought... my original reply is not part of the solution...

if the range is entered like "A1:a5" then the starting row and ending rows can be established...otherwise

**JMJ123** · 04-26-2013, 12:41 PM

Hi rcm,

Thanks for the reply. I figured out the error. I changed the loop to a for next loop and now it works well.

Can I ask you a separate question is it possible to copy color coding of text from one list of names to another, ie one column of names has color formatting and the other doesn't so I need to copy from the color coded list to the non-color coded list.

Thanks again,

Andrew

**rcm** · 04-26-2013, 02:03 PM

in VBA yes let assume you want to copy the font color of cells(1,"A") into cells(1,"D").

cells(1,"A").font.color=cells(1,"D").font.color

**JMJ123** · 04-29-2013, 12:05 PM

Hi rcm,

I tried that line of code but could not get it to work. Could you please put it in a short macro so that I know for sure I am using it correctly?

Thanks,

Andrew