PDA

View Full Version : Convert text to numbers



lhtqasonline
09-04-2009, 01:11 AM
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
09-04-2009, 04:36 AM
rng.Value = rng.Value

lhtqasonline
09-04-2009, 04:55 AM
is that the same as

Sub Convert_to_Values()

Dim rng As Range

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

Set rng = Nothing
End Sub

If so it just hangs my PC

nickgill
09-04-2009, 05:15 AM
lht

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


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


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
09-04-2009, 05:43 AM
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
09-04-2009, 05:53 AM
I see, processing an entire column will take some time.

This version tests each cell and only processes cells with a value.

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


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


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


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

mdmackillop
09-04-2009, 05:55 AM
For Each oCell In Intersect(Selection, ActiveSheet.UsedRange)

lhtqasonline
09-04-2009, 06:08 AM
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
09-05-2009, 03:35 PM
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
09-06-2009, 09:10 AM
is that the same as

Sub Convert_to_Values()

Dim rng As Range

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

Set rng = Nothing
End Sub

If so it just hangs my PC

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

lucas
09-06-2009, 09:49 AM
nickgill
All this because my userform changes the original cell value to text



I would look into why this is happening.

lhtqasonline
09-10-2009, 01:57 AM
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
09-10-2009, 04:51 AM
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.