Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Convert to Number

Posted on 2013-12-20
6
Medium Priority
?
947 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 34

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 54

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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 54

Accepted Solution

by:
Rgonzo1971 earned 1000 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 34

Assisted Solution

by:Rob Henson
Rob Henson earned 1000 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Quickbooks hosting can do wonders to your enterprise but considering the points elaborated in the article which will help you to better analyze the outcomes. So scan your business, its needs and then move to the new world of limitless benefits.
In this article, I will demonstrate that how to do a PST migration from Exchange Server to Office 365. This method allows importing one single PST, or multiple PST's at once.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

572 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