Converting a column of text to number with macro

[JohnSeito]

Hi

Could someone show me a code that would convert a column of text to number with macro.

Thanks appreciate it.

 

[jindon]

Then change to

ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value

 

[JohnSeito]

Thanks, I will try it out with my program and will let you know if I have any questions.

 

[Rafiramos]

No need to select individual columns, just drop the code and run it.

Sub Convert_to_number()<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
'<o></o>
' Convert_to_number()<o></o>
'<o></o>
<o> </o>
'<o></o>
Application.ScreenUpdating = False<o></o>
ActiveCell.SpecialCells(xlLastCell).Select<o></o>
Range(Selection, Cells(1)).Select<o></o>
Selection.Value = Selection.Value<o></o>
Range("A1").Select<o></o>
End Sub<o></o>

 

[ronahaim]

what can i do if the number is bigger from 703?
means it's column with 3 letters?

 

[AMAS]

Hi @ronahaim,

Could you please explain further what you are attempting to do and whether or not you want a formula, a user-defined function or a macro. Converting text to numbers can be done is many different ways.

Good luck.

AMAS

 

[ronahaim]

i want to write a function in vba

need to fill the " ?????????"

Public Function ColumnLetters(ByVal ColNum As Integer) As String
If ColNum > 26 And ColNum < 703 Then
ColumnLetters = Chr(Int((ColNum - 1) / 26) + 64) & Chr(((ColNum - 1) Mod 26) + 65)
End If
If ColNum <= 26 Then
ColumnLetters = Chr(ColNum + 64)
End If
If ColNum >= 703 Then
?????????
End If

end function



Thanks !!!!!

 

[shahdelsol]

Highlight the Cells that you wish to convert and run this Macro

Sub Foo()
Set Rng = Selection
For Each c In Rng
With Rng
c.Value = c.Value + 0
End With
Next c
End Sub

What if you want to apply this to all sheets except for a couple of sheets only in the range of A2:A100 ?