MyCode - converting text to numeric

[Tracey]

i have an excel spreadsheet that is populated from another
application. The users may be cutting and pasting data
into the spreadsheet. the data seems to be formatted
automatically as text. If some data should be currency the
data is actually stored with a "$", which would make it
text. I wrote some code that eliminated the "$" for every
record. Now I am having trouble converting the text to a
numeric, currency or double data type. It also doesn't
seem to like zeros. zero is stored as 0.00, when it should
be 0. Any comments on what I should do?

Here is my code.
Public Sub CheckForDollarSign()

Dim intRow As Integer
Dim intCol As Integer
Dim strCursor As String
Dim CellVal As Variant
Dim intCellval As Currency

intRow = 1
intCol = 1
strCursor = "1,1"

Do Until strCursor = "2000,26"

If Left(ActiveSheet.Cells(intRow, intCol), 1) = "$"
Then
Debug.Print ActiveSheet.Cells(intRow, intCol).Value
& " " & strCursor
If Len(ActiveSheet.Cells(intRow, intCol)) < 11
Then

CellVal = Right(ActiveSheet.Cells(intRow,
intCol).Value, Len(ActiveSheet.Cells(intRow,
intCol).Value) - 1)
If Left(CellVal, 1) = 0 Then
ActiveSheet.Cells(intRow,
intCol).Value = 0
Else

ActiveSheet.Cells(intRow,
intCol).Value = ""
ActiveSheet.Cells(intRow,
intCol).NumberFormat = "Accounting" ' code breaks here and
ActiveSheet.Cells(intRow,
intCol).Value = CCur(CellVal) 'code breaks here too
ActiveSheet.Cells(intRow,
intCol).Font.ColorIndex = 5

Worksheets(ActiveSheet).Range
(Cells(intRow, intCol)).NumberFormat = "currency"
End If
End If
Else
End If

If intRow = 2000 Then
intCol = intCol + 1
intRow = 1
Else
intRow = intRow + 1
End If

strCursor = intRow & "," & intCol

Loop

End Sub

 

[Ken Wright]

Are you just looking to convert *all* numeric looking data on the sheet to
numeric? If so then perhaps something like:-

Sub MakeNumeric()
With ActiveSheet.UsedRange
.NumberFormat = "General"
.Replace What:="$", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
On Error Resume Next
For Each cel In .SpecialCells(xlCellTypeConstants, 2)
If cel.HasFormula = False Then
cel.Value = cel.Value
End If
Next cel
End With
End Sub

 

[Frank Kabel]

Hi
without looking at your code you may just change the format for this
column to get your desired result