Prev: Pivot table has source data has exceeded 65536 records typemisma
Next: FormulaR1C1 using variables
From: p45cal on 21 Apr 2010 15:01

the likes of:



VBA Code:
--------------------



Range("A1:M200")=Range("A1:M200").value

--------------------



seems to work here.



J
o
e
B
o
y
n
t
o
n
;
7
0
5
3
5
2

W
r
o
t
e
:


>
Hi,
> I need help on how to automate the conversion of a range of Excel cells
from
> text to numbers. I'm using Excel 2007 to test, VB.NET 2005 and Excel
9.0
> Object lib. I've read some rows of data into an array and I set my
Excel
> Range = to the array which copies everything in quickly which is what I
want.
> Cell by cell is very slow with a hi number of records. Everything is
fine,
> except the cells with numbers were copied as text and those cells have
> warning flags in the corner with the first 2 options in the dropdown
being:
> "Number Stored As Text" and Convert To Number". Clicking on Convert to
> Number, or even clicking inside the cell a couple of times and then
outside
> fixes the problem and you can tell cause the warning is gone and
numeric
> formatting is applied. How can I automate this ConvertToNumber in code.
If
> I create thousands of these cells, the user can't be expected to convert
each
> one manually and I need to automate it. All my searches have netted
zero,
> zilcho, nada. Is there some hidden method somewhere that allows me to
> convert a range or even loop thru the range cell by cell and convert
the
> value of each of these cells to Number instead of Text? Your help would
be
> appreciated.


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?u=558
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=197375

http://www.thecodecage.com/forumz

From: Peter T on 21 Apr 2010 15:43
Not here it doesn't -:)

Regards,
Peter T


"p45cal" <p45cal.49s8qt(a)thecodecage.com> wrote in message
news:p45cal.49s8qt(a)thecodecage.com...
>
> the likes of:
>
>
> VBA Code:
> --------------------
>
>
> Range("A1:M200")=Range("A1:M200").value
> --------------------
>
>
>
> seems to work here.
>
>
> JoeBoynton;705352 Wrote:
>>
> Hi,
>> I need help on how to automate the conversion of a range of Excel cells
> from
>> text to numbers. I'm using Excel 2007 to test, VB.NET 2005 and Excel
> 9.0
>> Object lib. I've read some rows of data into an array and I set my
> Excel
>> Range = to the array which copies everything in quickly which is what I
> want.
>> Cell by cell is very slow with a hi number of records. Everything is
> fine,
>> except the cells with numbers were copied as text and those cells have
>> warning flags in the corner with the first 2 options in the dropdown
> being:
>> "Number Stored As Text" and Convert To Number". Clicking on Convert to
>> Number, or even clicking inside the cell a couple of times and then
> outside
>> fixes the problem and you can tell cause the warning is gone and
> numeric
>> formatting is applied. How can I automate this ConvertToNumber in code.
> If
>> I create thousands of these cells, the user can't be expected to convert
> each
>> one manually and I need to automate it. All my searches have netted
> zero,
>> zilcho, nada. Is there some hidden method somewhere that allows me to
>> convert a range or even loop thru the range cell by cell and convert
> the
>> value of each of these cells to Number instead of Text? Your help would
> be
>> appreciated.
>
>
> --
> p45cal
>
> *p45cal*
> ------------------------------------------------------------------------
> p45cal's Profile: http://www.thecodecage.com/forumz/member.php?u=558
> View this thread:
> http://www.thecodecage.com/forumz/showthread.php?t=197375
>
> http://www.thecodecage.com/forumz
>


From: p45cal on 21 Apr 2010 16:13

How are you getting the numbers-stored-as text into cells to test?
I'll do the same and test again..




P
e
t
e
r

T
;
7
0
5
6
0
1

W
r
o
t
e
:


>
Not here it doesn't -:)
>
> Regards,
> Peter T
>
>
> "p45cal" <p45cal.49s8qt(a)thecodecage.com> wrote in message
> news:p45cal.49s8qt(a)thecodecage.com...
> >
> > the likes of:
> >
> >
> > VBA Code:
> > --------------------
> >
> >
> > Range("A1:M200")=Range("A1:M200").value
> > --------------------
> >
> >
> >
> > seems to work here.
> >
> >
> > JoeBoynton;705352 Wrote:[color=green]
> >>
> > Hi,
> >> I need help on how to automate the conversion of a range of Excel


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?u=558
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=197375

http://www.thecodecage.com/forumz

From: Peter T on 21 Apr 2010 16:48
Sub test2()
With Range("A1:A10")
.NumberFormat = "@"
.Value = "0123" ' also try 123 without quotes
.Value = .Value
End With

Debug.Print VarType(Range("A1").Value) ' 8 or vbString

With Range("B1")
.Value = 1
.Copy
End With

Range("A1:A10").PasteSpecial Operation:=xlMultiply

Debug.Print VarType(Range("A1").Value) ' 5 or vbDouble

End Sub

Regards,
Peter T

"p45cal" <p45cal.49sc7m(a)thecodecage.com> wrote in message
news:p45cal.49sc7m(a)thecodecage.com...
>
> How are you getting the numbers-stored-as text into cells to test?
> I'll do the same and test again..
>
>
>
> Peter T;705601 Wrote:
>>
> Not here it doesn't -:)
>>
>> Regards,
>> Peter T
>>
>>
>> "p45cal" <p45cal.49s8qt(a)thecodecage.com> wrote in message
>> news:p45cal.49s8qt(a)thecodecage.com...
>> >
>> > the likes of:
>> >
>> >
>> > VBA Code:
>> > --------------------
>> >
>> >
>> > Range("A1:M200")=Range("A1:M200").value
>> > --------------------
>> >
>> >
>> >
>> > seems to work here.
>> >
>> >
>> > JoeBoynton;705352 Wrote:[color=green]
>> >>
>> > Hi,
>> >> I need help on how to automate the conversion of a range of Excel
>
>
> --
> p45cal
>
> *p45cal*
> ------------------------------------------------------------------------
> p45cal's Profile: http://www.thecodecage.com/forumz/member.php?u=558
> View this thread:
> http://www.thecodecage.com/forumz/showthread.php?t=197375
>
> http://www.thecodecage.com/forumz
>


From: p45cal on 21 Apr 2010 17:27

I suspect that could be because you're changing the format of the cells
and not changing it back. Try this on a virgin sheet:



VBA Code:
--------------------



Sub test2()
Debug.Print Range("A1").NumberFormat 'to establish pre-existing format i General
With Range("A1:A10")
.NumberFormat = "@"
.Value = "0123" ' also try 123 without quotes
.NumberFormat = "General" 'reset to default format, now you have numbers stored as text.
Debug.Print VarType(Range("A1").Value) ' 8 or vbString
.Value = .Value
Debug.Print VarType(Range("A1").Value) ' 5
End With
End Sub


--------------------





P
e
t
e
r

T
;
7
0
5
6
7
5

W
r
o
t
e
:


>
Sub test2()
> With Range("A1:A10")
> .NumberFormat = "@"
> .Value = "0123" ' also try 123 without quotes
> .Value = .Value
> End With
>
> Debug.Print VarType(Range("A1").Value) ' 8 or vbString
>
> With Range("B1")
> .Value = 1
> .Copy
> End With
>
> Range("A1:A10").PasteSpecial Operation:=xlMultiply
>
> Debug.Print VarType(Range("A1").Value) ' 5 or vbDouble
>
> End Sub
>
> Regards,
> Peter T
>
> "p45cal" <p45cal.49sc7m(a)thecodecage.com> wrote in message
> news:p45cal.49sc7m(a)thecodecage.com...
> >
> > How are you getting the numbers-stored-as text into cells to test?
> > I'll do the same and test again..
> >
> >
> >
> > Peter T;705601 Wrote:
> >>
> > Not here it doesn't -:)
> >>
> >> Regards,
> >> Peter T
> >>
> >>
> >> "p45cal" <p45cal.49s8qt(a)thecodecage.com> wrote in message
> >> news:p45cal.49s8qt(a)thecodecage.com...
> >> >
> >> > the likes of:
> >> >
> >> >
> >> > VBA Code:
> >> > --------------------
> >> >
> >> >
> >> > Range("A1:M200")=Range("A1:M200").value
> >> > --------------------
> >> >
> >> >
> >> >
> >> > seems to work here.
> >> >
> >> >
> >> > JoeBoynton;705352 Wrote:[color=green]
> >> >>
> >> > Hi,
> >> >> I need help on how to automate the conversion of a range of Excel
> >
> >
> > --
> > p45cal
> >
> > *p45cal*
> >
------------------------------------------------------------------------
> > p45cal's Profile: http://www.thecodecage.com/forumz/member.php?u=558
> > View this thread:
> > 'Excel Convert text to Number - The Code Cage Forums'
(http://www.thecodecage.com/forumz/showthread.php?t=197375)
> >
> > 'Microsoft Office Help - Microsoft Office Discussion - Excel VBA
Programming - Access Programming' (http://www.thecodecage.com/forumz)
> >


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?u=558
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=197375

http://www.thecodecage.com/forumz

First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: Pivot table has source data has exceeded 65536 records typemisma
Next: FormulaR1C1 using variables