convert to number

I have a coulmn of numbers that need converted to numbers. The format cell
won't work... You highlight all the numbers and the little ! box comes on and
you click convert to number. However, it does not record that function in a
macro.. I need to do this as it is part of a vlookup macro...
Thanks,
DJ

Here is some code that I use. It converts the selected numbers to text. You
can highlight entire columns or just a few cells. It leaves formulas alone...

HTH

Public Sub Convert()
Dim rngCurrent As Range
Dim rngToSearch As Range

Set rngToSearch = Intersect(ActiveSheet.UsedRange, Selection)
If Not rngToSearch Is Nothing Then
Application.Calculation = xlCalculationManual
For Each rngCurrent In rngToSearch
If Left(rngCurrent.Value, 1) <> "=" Then
rngCurrent.NumberFormat = "@"
rngCurrent.Value = Trim(CStr(rngCurrent.Value))
End If
Next
Application.Calculation = xlCalculationAutomatic
End If
End Sub

"Duncan_J" wrote:

> I have a coulmn of numbers that need converted to numbers. The format cell
> won't work... You highlight all the numbers and the little ! box comes on and
> you click convert to number. However, it does not record that function in a
> macro.. I need to do this as it is part of a vlookup macro...
> Thanks,
> DJ

Copy an empty cell.
select your column
edit|paste special|check Add

or

Select your column
data|text to columns|finish.

Duncan_J wrote:
>
> I have a coulmn of numbers that need converted to numbers. The format cell
> won't work... You highlight all the numbers and the little ! box comes on and
> you click convert to number. However, it does not record that function in a
> macro.. I need to do this as it is part of a vlookup macro...
> Thanks,
> DJ

--

Dave Peterson

Sorry that is my convert to text. I usually use that one when I need
vlookups. To make this convert to number change

rngCurrent.NumberFormat = "@"
rngCurrent.Value = Trim(CStr(rngCurrent.Value))

to

if isnumeric (rngCurrent.Value ) then
rngCurrent.NumberFormat = "#"
rngCurrent.Value = Cdbl(rngCurrent.Value)
end if

HTH

"Jim Thomlinson" wrote:

> Here is some code that I use. It converts the selected numbers to text. You
> can highlight entire columns or just a few cells. It leaves formulas alone...
>
> HTH
>
> Public Sub Convert()
> Dim rngCurrent As Range
> Dim rngToSearch As Range
>
> Set rngToSearch = Intersect(ActiveSheet.UsedRange, Selection)
> If Not rngToSearch Is Nothing Then
> Application.Calculation = xlCalculationManual
> For Each rngCurrent In rngToSearch
> If Left(rngCurrent.Value, 1) <> "=" Then
> rngCurrent.NumberFormat = "@"
> rngCurrent.Value = Trim(CStr(rngCurrent.Value))
> End If
> Next
> Application.Calculation = xlCalculationAutomatic
> End If
> End Sub
>
> "Duncan_J" wrote:
>
> > I have a coulmn of numbers that need converted to numbers. The format cell
> > won't work... You highlight all the numbers and the little ! box comes on and
> > you click convert to number. However, it does not record that function in a
> > macro.. I need to do this as it is part of a vlookup macro...
> > Thanks,
> > DJ

Thanks for the help!
Both worked...
DJ

"Dave Peterson" wrote:

> Copy an empty cell.
> select your column
> edit|paste special|check Add
>
> or
>
> Select your column
> data|text to columns|finish.
>
> Duncan_J wrote:
> >
> > I have a coulmn of numbers that need converted to numbers. The format cell
> > won't work... You highlight all the numbers and the little ! box comes on and
> > you click convert to number. However, it does not record that function in a
> > macro.. I need to do this as it is part of a vlookup macro...
> > Thanks,
> > DJ
>
> --
>
> Dave Peterson
>