Solved

Convert to Number

Posted on 2013-12-20
6
912 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
[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
  • 2
  • 2
  • 2
6 Comments
 
LVL 33

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 51

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
Office 365 Training for Admins - 7 Day Trial

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.

 
LVL 51

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 33

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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

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…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

728 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