Solved

Convert to Number

Posted on 2013-12-20
6
918 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 52

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
[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

 
LVL 52

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

Ready to get started with anonymous questions?

It's easy! Check out this step-by-step guide for asking an anonymous question on Experts Exchange.

Question has a verified solution.

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

My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
Ever wonder what it's like to get hit by ransomware? "Tom" gives you all the dirty details first-hand – and conveys the hard lessons his company learned in the aftermath.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
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…

626 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