Solved

Convert to Number

Posted on 2013-12-20
6
835 Views
Last Modified: 2013-12-20
Green triangle
Error indicator in cell      A green triangle in the upper-left corner of a cell indicates an error in the formula in the cell. If you select the cell, the Trace Error Button image button appears. Click the arrow next to the button for a list of options.

I have some cell that will not chg formatting... unless I use the little arrow off to the left of the cells.

Can I force the "Convert to Number with a macro on the column of where the active cell is?

Please advise and thanks. -R-
0
Comment
Question by:RWayneH
  • 2
  • 2
  • 2
6 Comments
 
LVL 31

Expert Comment

by:Rob Henson
ID: 39731514
Just changing the format will not change the contents.

If the content is

'12345

It will be recognised as text because of the apostrophe even if formatted in a number style.

As you rightly say, using the Error dropdown option "Convert to Number" corrects it.

An equivalent VBA command would be:

Cell.Value = Cell.Value

Seems bizarre but by doing that Excel effectively copies the value out and back in and when going back in it recognises it as a number so forces it as a number. It would help if the cell format was General to start with.

Thanks
Rob H
0
 
LVL 48

Expert Comment

by:Rgonzo1971
ID: 39731522
HI,

try this macro

Sub ConvertTextToNumber()
    'choose an empty cell
    Set rCellWithOne = Cells(Rows.Count, Columns.Count)
    rCellWithOne.Value = 1

    Set Rng = Selection.EntireColumn.SpecialCells(xlCellTypeConstants)
    Rng.NumberFormat = "General"
    rCellWithOne.Copy
    Rng.PasteSpecial xlPasteValues, xlPasteSpecialOperationMultiply

    rCellWithOne.Clear
End Sub

Open in new window

Regards
0
 

Author Comment

by:RWayneH
ID: 39731587
Rgonzo1971
How would I edit your code to convert everything that is in column F? instead of selecting a cell?

 robhenson

Columns("F:F").Select
Cell.Value = Cell.Value   failed      -R-
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 48

Accepted Solution

by:
Rgonzo1971 earned 250 total points
ID: 39731596
Hi,

pls try

Sub ConvertTextToNumber()
    'choose an empty cell
    Set rCellWithOne = Cells(Rows.Count, Columns.Count)
    rCellWithOne.Value = 1

    Set Rng = Range("F:F").SpecialCells(xlCellTypeConstants)
    Rng.NumberFormat = "General"
    rCellWithOne.Copy
    Rng.PasteSpecial xlPasteValues, xlPasteSpecialOperationMultiply

    rCellWithOne.Clear
End Sub

Open in new window

regards
0
 
LVL 31

Assisted Solution

by:Rob Henson
Rob Henson earned 250 total points
ID: 39731614
Range("A1").Select
Do Until Cell.Value = ""
Cell.Value = Cell.Value
Activecell.Offset(1,0)
Loop
0
 

Author Closing Comment

by:RWayneH
ID: 39731708
Both worked.  Thanks for the help. -R-
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

707 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now