Solved: the number in this cell is formatted as text

[white_flag]

good morning

I have an VBA code that take data from an text and inserted in an excel file. Then another code that you can edit the previous data inserted. Wen I edit data and there is an number with "," the code is going but I have this problem ('the number in this cell is formatted as text'). Then off course the excel fails to calculate the worksheet
It is an solution to convert text in numbers? because an substitution of "," in "." is to complicated for me.

thx

[macropod]

Hi white flag,

Commas used as thousands separators don't affect whether a cell is treated as text.

Of course, you can insert numbers as text strings. You can either convert those to values or use formulae that work with numeric text strings. The following macro will convert numeric text strings to values:

Sub TrimRange()
On Error Resume Next
Dim Cell As Range, I As Long
I = 0
For Each Cell In Selection.SpecialCells(xlConstants)
Cell.Formula = Cell.Value
I = I + 1
Next Cell
MsgBox "Finished trimming " & CellCount & " cells.", 64
End Sub
You can reference numeric text strings in formulae like:
=--A1
to process a text number in A1.

[white_flag]

thx macropod for your solution

meanwill I found this on forum and adapted for my needs

Sub dot()
'http://www.vbaexpress.com/forum/showthread.php?t=25480&highlight=regional
Dim cel As Range
Dim D As String, T As String
Application.ScreenUpdating = False

T = Application.ThousandsSeparator
D = Application.DecimalSeparator

For Each cel In Selection
On Error Resume Next
If InStr(1, cel, D) > 0 Then
cel.Replace What:=",", Replacement:=D, LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
If InStr(1, cel, ",") > 0 And InStr(1, cel, D) > 0 Then
cel.Replace What:=",", Replacement:=D, LookAt:=xlPart, SearchOrder:=xlByRows, _
MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
cel = cel * 1
End If
End If
On Error GoTo 0
Next
End Sub


it is going realy good:

[macropod]

Hi white flag,

OK, what you were after was a conversion between formats that use commas & periods for thousand & decimal separators and formats that use periods & commas for thousand & decimal separators. That wasn't apparent from your initial post.

[white_flag]

I know but I was not sure how to put the problem in correct words. Then after some search the problem was with regional settings (and VBA has to change "," with "."). The first "error" was this one "the number in this cell is formatted as text ..." from there start the confusion.

ps. send some sun here.. I know that in Australia is plenty of sun

[macropod]

Hi white flag,

Try the following macro. Essentially, it deletes whatever the original thousands separator was and replaces the decimal separator with the former thousands separator. If your cells are formatted to show the correct thousands separator for your region, they will appear in the correct positions in the output. I think you'll find it works more reliably than the one you're now using.
Sub DecimalTransform()
Dim RngCel As Range
Application.ScreenUpdating = False
For Each RngCel In Selection.SpecialCells(xlConstants)
If IsNumeric(RngCel.Value) Then RngCel.Value = _
Replace(Replace(Replace(RngCel.Value, ",", "¶"), ".", ","), "¶", ".")
Next
Application.ScreenUpdating = True
End Sub

[white_flag]

this is true :) thank you very much ..it is working really nice (in my case, better then the other one).