Convert text to number in different columns across three worksheets via macro?

[poetstorm]

Hi! Hoping you can help a newbie macro padawan here.

I have three sheets in one file. Each sheet has some number columns that come through as text and need to be converted to number. Bu the location of the columns is different on each sheet.

So I basically want to tell it go to the sheet called "Summary" and convert columns O through Q to number. Then go to the sheet called "Invoice Detail" and convert columns A & B to number. etc..

I cobbled together this bit of code. But it's not doing it for all three sheets, only one. Could someone tell me where I am going wring? Many thanks.

Please Login or Register  to view this content.

Update - I did a step through debug. It's converting the first section just fine. Then on the next two it seems to get the selection right, but when it gets to the line converting the numbers it seems to lose the selection and not actually convert anything. Ugh, it has to be just staring me in the face. Thanks!

[poetstorm]

Thanks for the reminder. It has been a while. Done.

[bakerman2]

Did you try setting the range AFTER you Select the sheet ?

Please Login or Register  to view this content.

[jindon]

No need to Select/Activate.

Please Login or Register  to view this content.

[poetstorm]

Did you try setting the range AFTER you Select the sheet ?

Please Login or Register  to view this content.

That worked! I kinda had a feeling that was putting the cart before the horse, but that's how I saw the code online. Should have trusted my gut on that. Thank you so much!

[poetstorm]

No need to Select/Activate.

Please Login or Register  to view this content.

Ooh, that is an interesting way to do it. I haven't really learned arrays yet but I like how this is written. I'm going to hold on to this for future.

[jindon]

poetstorm,

Your code is selecting entire column(s) and changing the cell format.
That make the file unnecessarily big.

[poetstorm]

Yep. I have noticed that actually with a lot of the macros I create, but I don't know how to tell the macro to only go down to filled out cells, and the row length on my files varies wildly. Still learning. I may try to use your code instead if that solves the issue.

Thanks everyone!