Convert to Number

[pells]

I have an error on my worksheet that advises me that I have a number stored as text. I can convert the cell to number format manually, but was wondering whether this can be done via VBA code as I need to do this on many workbooks?

Many thanks.

 

[VoG]

Try selecting the range and running

Sub ToNos()
With Selection.SpecialCells(xlCellTypeConstants)
    .Value = .Value
End With
End Sub

 

[pells]

Try selecting the range and running

Sub ToNos()
With Selection.SpecialCells(xlCellTypeConstants)
    .Value = .Value
End With
End Sub

Many thanks for this. I have tried this and it doesnt work, as my cell is replaced with various text from within my worksheet.

Any ideas?

 

[VoG]

Sorry, try

Sub ToNos()
Dim c As Range
For Each c In Selection.SpecialCells(xlCellTypeConstants)
    c.Value = c.Value
Next c
End Sub

 

[pells]

Sorry, try

Sub ToNos()
Dim c As Range
For Each c In Selection.SpecialCells(xlCellTypeConstants)
    c.Value = c.Value
Next c
End Sub

Excellent, that seems to have worked, thanks very much.

How can the code be changed to reference only 1 cell range i.e. cell C6?

I will need to run the code against many workbooks.

Once again, many thanks.

 

[VoG]

Like this?

Sub ToNos()
With Range("C6")
    .Value = .Value
End With
End Sub

 

[pells]

Like this?

Sub ToNos()
With Range("C6")
    .Value = .Value
End With
End Sub

Great, thanks!

Just one more thing, how to i put the workbook name in the code ie. if a workbook was called workbook1 for example?

 

[VoG]

Try like this

Sub ToNos()
With Workbooks("Workbook1.xls").Sheets(1).Range("C6")
    .Value = .Value
End With
End Sub

 

[pells]

Try like this

Sub ToNos()
With Workbooks("Workbook1.xls").Sheets(1).Range("C6")
    .Value = .Value
End With
End Sub

Sorry, this doesnt sem to be converting the txt to number?

Any other ideas?

 

[VoG]

Maybe

Sub ToNos()
With Workbooks("Workbook1.xls").Sheets(1).Range("C6")
    .NumberFormat = "General"
    .Value = .Value
End With
End Sub