Solved

Convert to Number

Posted on 2013-12-20
6
874 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 32

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 49

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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 49

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 32

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

825 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