Assistance with Copy Down Macro

[Terri Earley]

I always have problems with macros that I want to copy a formula down, but don't want to limit the copy down to specific row numbers - because each spreadsheet might be a different size.

On the attached simplified document, which starts as a download file, column A is all in Text, which I need converted to a number. So I start by copying down and then 'convert to number' and then I do a '=right(A1,4) formula in cell B2 and copy that down. When I run this macro in other similar workbooks, First, Column A isn't converting to a number and Column B is inputting the formula but only as far as the macro instructs it to and not down the entire column. Can someone please help?

[JBeaucaire]

This will do it. You can remove the line in red if you really want the formulas left behind, they aren't really needed after they've done their work:

Please Login or Register  to view this content.

[Terri Earley]

Thank you so much. I haven't inserted it into my macro yet, but I have a question - so the meat of the macro you just created is actually being inserted in the middle of a much larger macro. I can take out the beginning and ending 'sub' ... but what about the instruction Dim LR as Long? I don't recognize that code and not sure where it goes if I remove the beginning Sub title.

[JBeaucaire]

You can keep all these lines of code together as you see them now, if you wish. DIMS are declaration of variables that area used throughout the macro, they are typically found all at the top of the macro for easy reference, but they can be anywhere as long as they come BEFORE you try to use them.

[Terri Earley]

Thank you. That worked well and I was able to modify your code to work in other pertinent applications where I had that similar problem. Thanks.