Macro to Quickly Convert #'s stored as text

[juniperjacobs]

Ok, so I just discovered the multiple by 1 trick to quickly convert numbers stored as text to numbers. I would like to take it one step further and have a Macro that when i highlight the cells I want to convert in an Active Workbook and run the Macro it automatically Copies a 1 and does a Paste Values and Operation Multiple on the area I've selected.

Is this possible? If so, please provide Code.

Appreciate all of you!

[tigeravatar]

juniperjacobs,

If you're using a macro on a highlighted region, you could use the following:

Please Login or Register  to view this content.

Hope this helps,
~tigeravatar

[juniperjacobs]

that Macro doesn't seem to be working...

[tigeravatar]

It may be that numbers were entered with an apostrophe in front of them, which would store them as text. To get around that, try this:

Please Login or Register  to view this content.

If its still not working, upload an example sheet so I can see where its going wrong
~tigeravatar

EDIT: Accidentally posted sample code for an individual cell instead of code for a selection. It has been updated

[juniperjacobs]

I feel like that code is closer, but I want the macro to be determined by the selection range, not necessarily a column. I was thinking the code my need to look more like the below, but the changes I made didn't seem to work:

Please Login or Register  to view this content.

[tigeravatar]

Yeah, i accidentally posted some test code i was using to verify it worked. I updated the code i posted to be for a selection

[juniperjacobs]

Technically it works, so thank you for that, but I don't understand why it takes so much longer than if I do the actual steps for the formula, and the macro seems to stop as soon as there's a number not stored as text within the selection.

Is a macro not the best way? Is the way I want it done not the best way? All I want is a fast way to convert numbers stored as text to numbers in an area I select.

[tigeravatar]

Can you upload the sheet you're working on so I can get a better idea of what's going on? If it needs more than what I've posted, then you're looking for something more specific, and its a lot easier to see what's going on than to guess.

[Colin Legg]

In VBA you don't need to multiply by 1. This should work:

Please Login or Register  to view this content.

[juniperjacobs]

i actually have this macro which works fast, the problem is that i have to define the range in the code rather than having it be defined by what i've selected:

Please Login or Register  to view this content.

[Colin Legg]

Okay, you were trying to multiply by 1 in your previous post (post #5). The code I posted doesn't require the range to be defined in the code:

Please Login or Register  to view this content.

I trust that solves your problem?

[juniperjacobs]

so weird, why did that one work so much faster? either way, thanks so much Colin that solved it perfectly.