Jump to content

VBA simple question


Recommended Posts

I write VBA and VBS but it takes me a long time to write anything substantial and I can read much better than I can write it haha.

Basically my problem is this: I need to convert certain entire columns with thousands of entries to text in VBA because there is a bug in excel which converts all but certain random cells to text, requiring it to be done manually. They are in number format and I want to ensure that every cell in that column is a number. It is something that I do over and over again in the same columns for about a hundred times, so it might save me a ton of time...

Can someone write the few lines of code I would need to do this? I understand the concept of what I need to do and the steps I just cant get code it to work right and I know its a simple task for a good programmer. =)



Link to comment
Share on other sites

there is a "record macro" button in excela and other m$ office products. simply do what you nee to do by hand once for one item, and then look at the code it generates for you (or use said generated macro to simply finish everything off).

Link to comment
Share on other sites

Maybe you don't even need a macro...

If you're faced with a problem like ZIP codes or SSNs with leading Zeros being DISPLAYED without the zeros (but the Zeros really are there), you can try creating another column with a formula like this...

If your data is in col A, starting in A2, and your formula is in column B starting at B2... use the formula =A2&""

This will make 01234 display as 01234 instead of 1234 by forcing it to be interpreted as a string.

You can then copy the column with the formulas and use Paste Special... Values to get rid of the formula.

If the data in the column has actually been stripped of it's leading zero, and you need those zeros back... for a ZIP you can change the formula to this:

=Right("00000"&A2,5) which will take 5 zeros, tack on your data, then take the right 5 chars.

For a SSN, it's the same but use 9 zeros and ,9 instead of ,5.... yadda yadda.

Also, if you're pasting this data into Excel, format the columns as Text before you paste.

If none of this helps, print this advice out and burn it.

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Recently Browsing   0 members

    • No registered users viewing this page.
  • Create New...