Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

excel 201 change format from text no number

Posted on 2013-06-14
11
Medium Priority
?
468 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 

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

Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
Outlook for dependable use in a very small business   This article is about using the Outlook application (part of Microsoft Office) in a very small business, or for homeowners where dependability and reliability are critical requirements. This …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

721 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