RWayneH
asked on
Convert to Number
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-
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-
HI,
try this macro
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
Regards
ASKER
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-
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-
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Both worked. Thanks for the help. -R-
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