Convert text to numbers

[lhtqasonline]

Hi
Can anyone help in how I can write VBA code to simulate the convert text to numbers function when Excel gives you that error/drop-down option to change the cell value to a number?

[Bob Phillips]

[vba]

rng.Value = rng.Value
[/vba]

[lhtqasonline]

is that the same as

[VBA]Sub Convert_to_Values()

Dim rng As Range

For Each rng In Selection
rng = rng.Value
Next rng

Set rng = Nothing
End Sub [/VBA]

If so it just hangs my PC

[nickgill]

lht

I'm not sure what you mean by 'hang', but you might want to try:

[vba]
Option Explicit
Sub Convert_to_Values()
Dim oCell As Object
For Each oCell In Selection
oCell.NumberFormat = "General"
oCell.Value = oCell.Value
Next oCell
Set oCell = Nothing
End Sub
[/vba]

Selection is a range, so you don't want to work through each range in a range. Insted you want to work through each object in the range.

.Value = .Value is equivalent to paste special, if your cells are formatted as text this may not fix the problem hence changing the number format before setting the values. If this doesn't work you may want to try multiplying each cell by 1 (.value = .Value * 1). Although this won'y work if your selection is a mix of text and numbers.

[lhtqasonline]

Hi

That works fine for a single cell selected but it runs forever when i select a column to run it in The column currently has 1467 linesEventually I have to end Excel in Task Manager.

[nickgill]

I see, processing an entire column will take some time.

This version tests each cell and only processes cells with a value.
[vba]
Sub Convert_to_Values()
Dim oCell As Object
For Each oCell In Selection
If Len(oCell.Value) <> 0 Then
oCell.NumberFormat = "General"
oCell.Value = oCell.Value
End If
Next oCell
Set oCell = Nothing
End Sub
[/vba]

If each column doesn't contain any blanks before the end of the data you could try

[vba]
Sub Convert_to_Values()
Dim oCell As Object
For Each oCell In Selection
If Len(oCell.Value) = 0 Then
Exit For
Else
oCell.NumberFormat = "General"
oCell.Value = oCell.Value
End If
Next oCell
Set oCell = Nothing
End Sub
[/vba]

Also, instead of stopping excel in the task manager try using Ctrl + [Break] to stop the code running.

[mdmackillop]

[VBA]

For Each oCell In Intersect(Selection, ActiveSheet.UsedRange)

[/VBA]

[lhtqasonline]

nickgill
I used your last suggestion as all cells contain a value and that worked although it took 30 seconds to complete the action

All this because my userform changes the original cell value to text

Anyway it works

[nickgill]

My comment about not dimensioning oCell as an object instead of a range was wrong. You do want to work through each range in the selection and by dimensioning oCell as a range you'll get the benefit of auto complete etc.

If the user form is causing the problem it would probably be more efficient to fix the problem with the user form.

[Bob Phillips]

is that the same as

[VBA]Sub Convert_to_Values()

Dim rng As Range

For Each rng In Selection
rng = rng.Value
Next rng

Set rng = Nothing
End Sub [/VBA]

If so it just hangs my PC

No, rng would be the entire range of cells, all processed in a single action.

[lucas]

nickgill
All this because my userform changes the original cell value to text

I would look into why this is happening.

[lhtqasonline]

I searched for an fix to the problem and found nothing (even posted it on VBA express) as to how to force the username to save details to the same format the cell is currently in but to no avail that's why I tried fixing the column after the userform closes with the above solution. Any insight into how to specify the format when the userform closes would be appreciated.

I tried Cells(R, 29) = CCur (Me.SuppSpent07) but it gave a error even formatting the original cells as numbers or currency "Run-time error '13':
Type mismatch"

[Aussiebear]

I searched for an fix to the problem and found nothing (even posted it on VBA express) .....

Have you forgotten where you are? If you have posted this issue on other forums please supply the link/s, so that we can see if you have others working on the same issue, where they are up to etc. To have members here working on the same issue when its possible that you might already have a solution will not enthuse others to assist you in the future.