Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

excel 201 change format from text no number

Posted on 2013-06-14
11
Medium Priority
?
473 Views
Last Modified: 2013-06-18
I'm using 201 and when I insert a row of numbers some of them change to text. there are over 2000 items in the column. I need some vba code [as this is part of a macro] to change the entire column to number format.

thanks
0
Comment
Question by:Jagwarman
  • 6
  • 5
11 Comments
 

Author Comment

by:Jagwarman
ID: 39247844
that should be 2010 not 201

thanks
0
 
LVL 49

Expert Comment

by:Martin Liss
ID: 39247874
Columns("A:A").Select
    Selection.NumberFormat = "0.00"
0
 
LVL 49

Expert Comment

by:Martin Liss
ID: 39247877
Or better

Columns("A:A").NumberFormat = "0.00"
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 

Author Comment

by:Jagwarman
ID: 39253493
MartinLiss I tried this but it does not work.

What I am doing is copying from another file then stripping out 7 characters from the cell. In so doing the cell becomes text. I get that little green triangle top left that when you click on it, it says "the number in this cell is formatted as text or preceded by an apostrophy. The when I open that I can convert it to a number. When I do it manually that way it works.

So, is there a way to convert it to a number using VBA. As I said your solution [unfortunately] did not work.

thanks
0
 
LVL 49

Expert Comment

by:Martin Liss
ID: 39253528
Here's an article that should help.
0
 

Author Comment

by:Jagwarman
ID: 39253567
Hi MartinLiss.

I found this code but I have to 'manually' highlight the column. Would you be able to make a change to the code so that it works by selecting Column B

Sub ConvertTextNumbers()
 
Dim rUsedRange As Range
 
' Convert all cells with numbers from text to numbers

For Each rUsedRange In Intersect(ActiveSheet.UsedRange, Selection).Areas
rUsedRange.Value = rUsedRange.Value
Next rUsedRange
 
End Sub


Thanks
0
 
LVL 49

Expert Comment

by:Martin Liss
ID: 39253570
Did you look at the article I posted?
0
 

Author Comment

by:Jagwarman
ID: 39254087
I did but unfortunately it didn't help me

Regards
0
 
LVL 49

Accepted Solution

by:
Martin Liss earned 2000 total points
ID: 39254311
Try this


Sub ConvertTextNumbers()
Dim c As Range

Columns("B:B").Select
For Each c In Selection
    c.Value = c.Value
Next

End Sub

Open in new window

0
 

Author Closing Comment

by:Jagwarman
ID: 39255354
Thanks Martin brilliant just what I needed.
0
 
LVL 49

Expert Comment

by:Martin Liss
ID: 39256367
You're welcome and I'm glad I was able to help.

Marty - MVP 2009 to 2013
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Cancel future meetings from user mailboxes in Office 365 using Remove-CalendarEvents
Microsoft has changed the look and feel of Azure AD and Microsoft account sign-in pages so that you will have a more unified look and feel when moving between the two interfaces.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

972 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question