cells have existing values - xls downloaded from a site- numbers come in
as text so I can't SUM(x:x) etc.

first guess to solve: loop thru entries, save value-convert string to
float, reformat cell, replace value?

easier idea?

Thanks
Mark

mp formulated on Wednesday :
cells have existing values - xls downloaded from a site- numbers come in as
text so I can't SUM(x:x) etc.

first guess to solve: loop thru entries, save value-convert string to float,
reformat cell, replace value?

easier idea?

Thanks
Mark

See your original post in the other forum, where I added instructions
for how to use the formula I posted.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

On 4/25/2012 6:07 PM, GS wrote:
mp formulated on Wednesday :
cells have existing values - xls downloaded from a site- numbers come
in as text so I can't SUM(x:x) etc.

first guess to solve: loop thru entries, save value-convert string to
float, reformat cell, replace value?

easier idea?

Thanks
Mark

See your original post in the other forum, where I added instructions
for how to use the formula I posted.


I saw that GS, thanks. I took that in the context of that forum on how
to do it manually. This similar query was in the context of automating
it via vb(a).

I will be doing various calcs on the values so would rather convert the
data type from string to real, rather than use (function x) rather than
(function(value x)) each time.

Thanks
Mark

mp pretended :
On 4/25/2012 6:07 PM, GS wrote:
mp formulated on Wednesday :
cells have existing values - xls downloaded from a site- numbers come
in as text so I can't SUM(x:x) etc.

first guess to solve: loop thru entries, save value-convert string to
float, reformat cell, replace value?

easier idea?

Thanks
Mark

See your original post in the other forum, where I added instructions
for how to use the formula I posted.


I saw that GS, thanks. I took that in the context of that forum on how to do
it manually. This similar query was in the context of automating it via
vb(a).

I will be doing various calcs on the values so would rather convert the data
type from string to real, rather than use (function x) rather than
(function(value x)) each time.

Thanks
Mark

Ok, Mark! I was going to offer a VBA solution but I'll hold off on that
for now. If you're interested to go that way then post back...

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

On Wed, 25 Apr 2012 17:23:49 -0500, mp wrote:


cells have existing values - xls downloaded from a site- numbers come in
as text so I can't SUM(x:x) etc.

first guess to solve: loop thru entries, save value-convert string to
float, reformat cell, replace value?

easier idea?

Thanks
Mark

for a VBA solution, assuming the cell's format is still "General", (the cell format shouldn't be changed by an import) you can simply do use this macro.

To enter this Macro (Sub), <alt-F11 opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this Macro (Sub), first select the range of cells that need to be processed or define them some other way (and change the macro accordingly).

Then <alt-F8 opens the macro dialog box. Select the macro by name, and <RUN.

======================
Option Explicit
Sub MakeNumeric()
Dim c As Range
For Each c In Selection
c.Value = c.Value
Next c
End Sub
=========================

On 4/26/2012 5:12 AM, Ron Rosenfeld wrote:
On Wed, 25 Apr 2012 17:23:49 -0500, wrote:


cells have existing values - xls downloaded from a site- numbers come in
as text so I can't SUM(x:x) etc.

first guess to solve: loop thru entries, save value-convert string to
float, reformat cell, replace value?

easier idea?

Thanks
Mark

for a VBA solution, assuming the cell's format is still "General", (the cell format shouldn't be changed by an import) you can simply do use this macro.

To enter this Macro (Sub),<alt-F11 opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this Macro (Sub), first select the range of cells that need to be processed or define them some other way (and change the macro accordingly).

Then<alt-F8 opens the macro dialog box. Select the macro by name, and<RUN.

======================
Option Explicit
Sub MakeNumeric()
Dim c As Range
For Each c In Selection
c.Value = c.Value
Next c
End Sub
=========================

I'll be darned, it's that easy? :-) I wouldn't have intuitively arrived
at that!! :-) thanks a bunch
Mark

hi Ron,

why make a loop ?

Sub MakeNumeric()
Selection = Selection.Value
End Sub

--
isabelle



Le 2012-04-26 06:12, Ron Rosenfeld a écrit :
On Wed, 25 Apr 2012 17:23:49 -0500, wrote:


cells have existing values - xls downloaded from a site- numbers come in
as text so I can't SUM(x:x) etc.

first guess to solve: loop thru entries, save value-convert string to
float, reformat cell, replace value?

easier idea?

Thanks
Mark

for a VBA solution, assuming the cell's format is still "General", (the cell format shouldn't be changed by an import) you can simply do use this macro.

To enter this Macro (Sub),<alt-F11 opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this Macro (Sub), first select the range of cells that need to be processed or define them some other way (and change the macro accordingly).

Then<alt-F8 opens the macro dialog box. Select the macro by name, and<RUN.

======================
Option Explicit
Sub MakeNumeric()
Dim c As Range
For Each c In Selection
c.Value = c.Value
Next c
End Sub
=========================

On Fri, 27 Apr 2012 19:47:46 -0400, isabelle wrote:

hi Ron,

why make a loop ?

To ensure one is only converting dates.

While selecting everything might work if I were absolutely certain of the data being converted, one might not want to convert, for example, text-numbers with leading zero's, or formulas that result in a numeric result, to their numeric values.

On Fri, 27 Apr 2012 19:47:46 -0400, isabelle wrote:

hi Ron,

why make a loop ?

Sub MakeNumeric()
Selection = Selection.Value
End Sub

And, of course, I neglected to put that test in in the macro I posted <sound of hand slapping forehead. I had it in something else I wrote, but neglected to copy it over.

On Fri, 27 Apr 2012 18:12:25 -0500, mp wrote:

I'll be darned, it's that easy? :-) I wouldn't have intuitively arrived
at that!! :-) thanks a bunch
Mark

Mark,

For safety sake, I would suggest a little more complexity:

For Each c In Selection
If IsDate(c) Then c.Value = c.Value
Next c

Isabelle asked why not just:
Selection = selection.value

And the answer was to avoid converting non-date entries (including, for example, numbers with leading zero's that are in a text format; and also formulas). But I had omitted that test from what I posted earlier.

On Fri, 27 Apr 2012 19:47:46 -0400, isabelle wrote:

hi Ron,

why make a loop ?

Sub MakeNumeric()
Selection = Selection.Value
End Sub

--
isabelle

I should have added that what your version does what I posted without the loop; but does not do what I meant to post :-(

phew, i was afraid for a moment that this solution was no longer possible with xl2007 +
thanks Ron, and have a good night

--
isabelle



Le 2012-04-27 20:15, Ron Rosenfeld a écrit :
On Fri, 27 Apr 2012 19:47:46 -0400, wrote:

hi Ron,

why make a loop ?

Sub MakeNumeric()
Selection = Selection.Value
End Sub

--
isabelle

I should have added that what your version does what I posted without the loop; but does not do what I meant to post :-(