Hi

I have a variably sized range with numbers in text format.
I want to select them, and convert to numbers.

Thanks

Sub numerify()
Dim r As Range
Count = 0
For Each r In Selection
If Application.IsText(r.Value) Then
If IsNumeric(r.Value) Then
r.Value = 1# * r.Value
r.NumberFormat = "General"
Count = Count + 1
End If
End If
Next
MsgBox (Count & " cells changed")
End Sub

--
Gary''s Student - gsnu200754


"Faboboren" wrote:

> Hi
>
> I have a variably sized range with numbers in text format.
> I want to select them, and convert to numbers.
>
> Thanks

> I have a variably sized range with numbers in text format.
> I want to select them, and convert to numbers.

Are you needing this in (macro) code or do you just have a range of data
that you need to convert one time? If the latter, just enter 1 into a cell
(as a number), press Ctrl+C, select your range of cells that have the
text-numbers in it, click Edit/PasteSpecial in Excel's menu, select Multiply
from the Operation section of the dialog box that appears and then click OK.

Rick

Hi Rick,

I need in macro code

"Rick Rothstein (MVP - VB)" wrote:

> > I have a variably sized range with numbers in text format.
> > I want to select them, and convert to numbers.
>
> Are you needing this in (macro) code or do you just have a range of data
> that you need to convert one time? If the latter, just enter 1 into a cell
> (as a number), press Ctrl+C, select your range of cells that have the
> text-numbers in it, click Edit/PasteSpecial in Excel's menu, select Multiply
> from the Operation section of the dialog box that appears and then click OK.
>
> Rick
>
>

Gary,

it is working for 1 cell, but how do I select the whole range of
text-numbers to convert in numbers

"Gary''s Student" wrote:

> Sub numerify()
> Dim r As Range
> Count = 0
> For Each r In Selection
> If Application.IsText(r.Value) Then
> If IsNumeric(r.Value) Then
> r.Value = 1# * r.Value
> r.NumberFormat = "General"
> Count = Count + 1
> End If
> End If
> Next
> MsgBox (Count & " cells changed")
> End Sub
>
> --
> Gary''s Student - gsnu200754
>
>
> "Faboboren" wrote:
>
> > Hi
> >
> > I have a variably sized range with numbers in text format.
> > I want to select them, and convert to numbers.
> >
> > Thanks

Just select them in the worksheet before running the macro. You can select a
row or a column or a pile of cells.
--
Gary''s Student - gsnu200754


"Faboboren" wrote:

> Gary,
>
> it is working for 1 cell, but how do I select the whole range of
> text-numbers to convert in numbers
>
> "Gary''s Student" wrote:
>
> > Sub numerify()
> > Dim r As Range
> > Count = 0
> > For Each r In Selection
> > If Application.IsText(r.Value) Then
> > If IsNumeric(r.Value) Then
> > r.Value = 1# * r.Value
> > r.NumberFormat = "General"
> > Count = Count + 1
> > End If
> > End If
> > Next
> > MsgBox (Count & " cells changed")
> > End Sub
> >
> > --
> > Gary''s Student - gsnu200754
> >
> >
> > "Faboboren" wrote:
> >
> > > Hi
> > >
> > > I have a variably sized range with numbers in text format.
> > > I want to select them, and convert to numbers.
> > >
> > > Thanks

Gary,

But how can I include in this macro a selection for variably sized range
Because this is part of a bigger macro, I do not want select the range
manually

Thanks



"Gary''s Student" wrote:

> Just select them in the worksheet before running the macro. You can select a
> row or a column or a pile of cells.
> --
> Gary''s Student - gsnu200754
>
>
> "Faboboren" wrote:
>
> > Gary,
> >
> > it is working for 1 cell, but how do I select the whole range of
> > text-numbers to convert in numbers
> >
> > "Gary''s Student" wrote:
> >
> > > Sub numerify()
> > > Dim r As Range
> > > Count = 0
> > > For Each r In Selection
> > > If Application.IsText(r.Value) Then
> > > If IsNumeric(r.Value) Then
> > > r.Value = 1# * r.Value
> > > r.NumberFormat = "General"
> > > Count = Count + 1
> > > End If
> > > End If
> > > Next
> > > MsgBox (Count & " cells changed")
> > > End Sub
> > >
> > > --
> > > Gary''s Student - gsnu200754
> > >
> > >
> > > "Faboboren" wrote:
> > >
> > > > Hi
> > > >
> > > > I have a variably sized range with numbers in text format.
> > > > I want to select them, and convert to numbers.
> > > >
> > > > Thanks

Try this code where you specify your range in place of the range I show...

Dim MyRange As String
Dim R As Range
MyRange = "A11,A2:B2,A3:C3"
For Each R In Range(MyRange)
R.NumberFormat = "General"
R.Value = CDbl(R.Value)
Next

Rick


"Faboboren" <(E-Mail Removed)> wrote in message
news:740B7430-7003-4C1C-B05E-(E-Mail Removed)...
> Gary,
>
> But how can I include in this macro a selection for variably sized range
> Because this is part of a bigger macro, I do not want select the range
> manually
>
> Thanks
>
>
>
> "Gary''s Student" wrote:
>
>> Just select them in the worksheet before running the macro. You can
>> select a
>> row or a column or a pile of cells.
>> --
>> Gary''s Student - gsnu200754
>>
>>
>> "Faboboren" wrote:
>>
>> > Gary,
>> >
>> > it is working for 1 cell, but how do I select the whole range of
>> > text-numbers to convert in numbers
>> >
>> > "Gary''s Student" wrote:
>> >
>> > > Sub numerify()
>> > > Dim r As Range
>> > > Count = 0
>> > > For Each r In Selection
>> > > If Application.IsText(r.Value) Then
>> > > If IsNumeric(r.Value) Then
>> > > r.Value = 1# * r.Value
>> > > r.NumberFormat = "General"
>> > > Count = Count + 1
>> > > End If
>> > > End If
>> > > Next
>> > > MsgBox (Count & " cells changed")
>> > > End Sub
>> > >
>> > > --
>> > > Gary''s Student - gsnu200754
>> > >
>> > >
>> > > "Faboboren" wrote:
>> > >
>> > > > Hi
>> > > >
>> > > > I have a variably sized range with numbers in text format.
>> > > > I want to select them, and convert to numbers.
>> > > >
>> > > > Thanks

Here is a typical approach:

Dim rrGlobal As Range

Sub main()
Set rrGlobal = Range("A:A")
Call numerify
End Sub

Sub numerify()
Dim r As Range
Count = 0
For Each r In rrGlobal
If Application.IsText(r.Value) Then
If IsNumeric(r.Value) Then
r.Value = 1# * r.Value
r.NumberFormat = "General"
Count = Count + 1
End If
End If
Next
MsgBox (Count & " cells changed")
End Sub

1. we DIM a static, global, variable as a range (before the subs)
2. the main sub set the global and calls numerify
3. numerify uses the supplied range


Naturally you would set rrGlobal to your desired range not Range("A:A")
--
Gary''s Student - gsnu200754


"Faboboren" wrote:

> Gary,
>
> But how can I include in this macro a selection for variably sized range
> Because this is part of a bigger macro, I do not want select the range
> manually
>
> Thanks
>
>
>
> "Gary''s Student" wrote:
>
> > Just select them in the worksheet before running the macro. You can select a
> > row or a column or a pile of cells.
> > --
> > Gary''s Student - gsnu200754
> >
> >
> > "Faboboren" wrote:
> >
> > > Gary,
> > >
> > > it is working for 1 cell, but how do I select the whole range of
> > > text-numbers to convert in numbers
> > >
> > > "Gary''s Student" wrote:
> > >
> > > > Sub numerify()
> > > > Dim r As Range
> > > > Count = 0
> > > > For Each r In Selection
> > > > If Application.IsText(r.Value) Then
> > > > If IsNumeric(r.Value) Then
> > > > r.Value = 1# * r.Value
> > > > r.NumberFormat = "General"
> > > > Count = Count + 1
> > > > End If
> > > > End If
> > > > Next
> > > > MsgBox (Count & " cells changed")
> > > > End Sub
> > > >
> > > > --
> > > > Gary''s Student - gsnu200754
> > > >
> > > >
> > > > "Faboboren" wrote:
> > > >
> > > > > Hi
> > > > >
> > > > > I have a variably sized range with numbers in text format.
> > > > > I want to select them, and convert to numbers.
> > > > >
> > > > > Thanks

It works
But how can I specify the range when is always different
Thanks

"Rick Rothstein (MVP - VB)" wrote:

> Try this code where you specify your range in place of the range I show...
>
> Dim MyRange As String
> Dim R As Range
> MyRange = "A11,A2:B2,A3:C3"
> For Each R In Range(MyRange)
> R.NumberFormat = "General"
> R.Value = CDbl(R.Value)
> Next
>
> Rick
>
>
> "Faboboren" <(E-Mail Removed)> wrote in message
> news:740B7430-7003-4C1C-B05E-(E-Mail Removed)...
> > Gary,
> >
> > But how can I include in this macro a selection for variably sized range
> > Because this is part of a bigger macro, I do not want select the range
> > manually
> >
> > Thanks
> >
> >
> >
> > "Gary''s Student" wrote:
> >
> >> Just select them in the worksheet before running the macro. You can
> >> select a
> >> row or a column or a pile of cells.
> >> --
> >> Gary''s Student - gsnu200754
> >>
> >>
> >> "Faboboren" wrote:
> >>
> >> > Gary,
> >> >
> >> > it is working for 1 cell, but how do I select the whole range of
> >> > text-numbers to convert in numbers
> >> >
> >> > "Gary''s Student" wrote:
> >> >
> >> > > Sub numerify()
> >> > > Dim r As Range
> >> > > Count = 0
> >> > > For Each r In Selection
> >> > > If Application.IsText(r.Value) Then
> >> > > If IsNumeric(r.Value) Then
> >> > > r.Value = 1# * r.Value
> >> > > r.NumberFormat = "General"
> >> > > Count = Count + 1
> >> > > End If
> >> > > End If
> >> > > Next
> >> > > MsgBox (Count & " cells changed")
> >> > > End Sub
> >> > >
> >> > > --
> >> > > Gary''s Student - gsnu200754
> >> > >
> >> > >
> >> > > "Faboboren" wrote:
> >> > >
> >> > > > Hi
> >> > > >
> >> > > > I have a variably sized range with numbers in text format.
> >> > > > I want to select them, and convert to numbers.
> >> > > >
> >> > > > Thanks
>
>