excel 201 change format from text no number

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
JagwarmanAsked:
Who is Participating?
 
Martin LissConnect With a Mentor Older than dirtCommented:
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
 
JagwarmanAuthor Commented:
that should be 2010 not 201

thanks
0
 
Martin LissOlder than dirtCommented:
Columns("A:A").Select
    Selection.NumberFormat = "0.00"
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Martin LissOlder than dirtCommented:
Or better

Columns("A:A").NumberFormat = "0.00"
0
 
JagwarmanAuthor Commented:
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
 
Martin LissOlder than dirtCommented:
Here's an article that should help.
0
 
JagwarmanAuthor Commented:
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
 
Martin LissOlder than dirtCommented:
Did you look at the article I posted?
0
 
JagwarmanAuthor Commented:
I did but unfortunately it didn't help me

Regards
0
 
JagwarmanAuthor Commented:
Thanks Martin brilliant just what I needed.
0
 
Martin LissOlder than dirtCommented:
You're welcome and I'm glad I was able to help.

Marty - MVP 2009 to 2013
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.