Solved

excel 201 change format from text no number

Posted on 2013-06-14
11
465 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 48

Expert Comment

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

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 48

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 48

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 48

Accepted Solution

by:
Martin Liss earned 500 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 48

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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article helps those who get the 0xc004d307 error when trying to rearm (reset the license) Office 2013 in a Virtual Desktop Infrastructure (VDI) and/or those trying to prep the master image for Microsoft Key Management (KMS) activation. (i.e.- C…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

627 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