Hi,
I currently have a set of data that is "stored as text" in a number of cells. As a result, my vlookup formula is not reading the numbers since they are text and not numbers.
Rather than manually clicking each number and pressing "convert to number"... Could someone help me with a VBA macro that automatically converts all the items in a specific column to number so that the Vlookup formula works?
I've attached an excel sheet with this message as an example.
Thanks for all your help!!!!
Put this into your workbook and let me know how it goes:
Please Login or Register to view this content.
If you're happy with someone's help, click that little star at the bottom left of their post to give them Reps.
---Keep on Coding in the Free World---
You do not a code for this task. I have converted them in to values only.
Just copy an empty cell, select those cells, Paste > Paste Special, tick Add
Entia non sunt multiplicanda sine necessitate
I seem to remember running into this before and having trouble with "normal" means of handling the problem. Try this. To execute the code, select the top member (like the "Data" header) and then run the code. Should work on the numbers underneath your selection.Please Login or Register to view this content.
Last edited by GeneralDisarray; 06-17-2013 at 04:50 PM.
Remember, saying thanks only takes a second or two. Click the star icon(*) below the post you liked, to give some Rep if you think an answer deserves it.
Please,mark your thread [SOLVED] if you received your answer.
You dont need to select each number individually and convert it to a number. Select the range you need to convert into numbers. Against the first cell in the range and click on convert to number. The entire selection will get converted. This would probably be faster than writing code and executing it.
kinda hard to know with no further comments from the OP.... is this solved?
Well, either way there are a gambit of solutions to choose from.
That macro works great!Originally Posted by Mordred
Put this into your workbook and let me know how it goes:Please Login or Register to view this content.
Thanks!! Solved!!
Glad to help Pho6, others contributed here too and have equally provided solutions. You could literally go on a rep clicking rampage in this thread.
If you have long list of entries that you need to convert the looping macros are very slow.
The best and quick way I find is to use 'text to column' approach.
NOTE: you need to select your range before running this code and it works only on one column at a time.
You can define your range within your sub if it is known, eg range(range("a1").range("a1").end(xldown)).select
Very quick though!
Please Login or Register to view this content.
Last edited by arlu1201; 12-13-2013 at 02:03 AM.
trustodium,
Welcome to the forum.
I have added code tags to your post. As per forum rule 3, you need to use them whenever you put any code in your post. Please add them in future. In order to put code tags, either type [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] at the end of it, OR you can highlight your code and click the # icon at the top of your post window.
If I have helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks