Excel Convert text to Number
in [Programming]
|
Prev: Pivot table has source data has exceeded 65536 records typemisma
Next: FormulaR1C1 using variables
From: JoeBoynton on 21 Apr 2010 17:34 I'll try your suggestion later, couldnt just now btw i converted the cells from text to number 1 by 1 as follows: Dim oRange = oWs.Range("A1").Resize(reszRow, rs.Fields.Count) oRange.Value = TheArray ' rs is from legacy Acucobol vision files read with odbc/dao off of Linux server For myOrRow = 1 To ArrayRowCount + 1 ' Extra row count above because I loaded headers into row 1 For myOrColumn = 1 To rs.Fields.Count If IsNumeric(oRange(myOrRow, myOrColumn).Value) Then oRange(myOrRow, myOrColumn).Value = oRange(myOrRow, myOrColumn).Value.ToString * 1 End If Next Next This seems to fix the problem, but its a time killer cause it's cell by cell Thx for suggestions "p45cal" wrote: > > 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: Peter T on 21 Apr 2010 18:09 That's just about the slowest way you could contrive to do it. You could try doing as p45cal suggests (depends, it might not always work) with rng ..numberformat = "General" ..value = .value arr = .value end with or pastevalues with multiply 1 as I suggested (I also posted code sample) If you don't want to change cells at all, read the entire range to an array, then loop the array to change items as required (that'd be very significantly faster than looping cells though probably not as quick as the pastespecial multiply approach). Regards, Peter T "JoeBoynton" <JoeBoynton(a)discussions.microsoft.com> wrote in message news:9221B23A-45B9-4596-BBB5-E39D0B1D0759(a)microsoft.com... > I'll try your suggestion later, couldnt just now > btw i converted the cells from text to number 1 by 1 as follows: > > Dim oRange = oWs.Range("A1").Resize(reszRow, rs.Fields.Count) > oRange.Value = TheArray > > ' rs is from legacy Acucobol vision files read with odbc/dao off of > Linux server > > For myOrRow = 1 To ArrayRowCount + 1 > > ' Extra row count above because I loaded headers into row 1 > > For myOrColumn = 1 To rs.Fields.Count > If IsNumeric(oRange(myOrRow, myOrColumn).Value) Then > oRange(myOrRow, myOrColumn).Value = oRange(myOrRow, > myOrColumn).Value.ToString * 1 > End If > Next > Next > > This seems to fix the problem, but its a time killer cause it's cell by > cell > > Thx for suggestions > > "p45cal" wrote: > >> >> 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: Peter T on 21 Apr 2010 18:11 Yeah I know, but you didn't mention that originally. Also I'm almost sure from memory that approach doesn't work in some scenarios (I might be wrong) Regards, Peter T "p45cal" <p45cal.49sfg1(a)thecodecage.com> wrote in message news:p45cal.49sfg1(a)thecodecage.com... > > 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 > > -------------------- > > > > > > Peter T;705675 Wrote: >> > 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 >
From: p45cal on 21 Apr 2010 19:21 so after: VBA Code: -------------------- oRange.Value = TheArray -------------------- just have: VBA Code: -------------------- Orange.Value = Orange.Value -------------------- you could also temporarily put the line: VBA Code: -------------------- Orange.numberformat = "General" -------------------- before the -orange.value = orange.value- line but I'd expect you wouldn't need that after the first run, especially if there are special formats you want to keep in that range? Anyway, try it and see, fingers crossed. At least it should be quick. Afterthought: you don't have to do this for the whole range, you could do it for just a few columns: VBA Code: -------------------- Orange.columns(2).value = Orange.columns(2).value -------------------- or for more: VBA Code: -------------------- Set rngToMakeNos = Union(orange.Columns(3), orange.Columns(5), orange.Columns(7)) rngToMakeNos.Value = rngToMakeNos.Value -------------------- J o e B o y n t o n ; 7 0 5 7 2 3 W r o t e : > I'll try your suggestion later, couldnt just now > btw i converted the cells from text to number 1 by 1 as follows: > > Dim oRange = oWs.Range("A1").Resize(reszRow, rs.Fields.Count) > oRange.Value = TheArray > > ' rs is from legacy Acucobol vision files read with odbc/dao off of > Linux server > > For myOrRow = 1 To ArrayRowCount + 1 > > ' Extra row count above because I loaded headers into row 1 > > For myOrColumn = 1 To rs.Fields.Count > If IsNumeric(oRange(myOrRow, myOrColumn).Value) Then > oRange(myOrRow, myOrColumn).Value = oRange(myOrRow, > myOrColumn).Value.ToString * 1 > End If > Next > Next > > This seems to fix the problem, but its a time killer cause it's cell by cell > > Thx for suggestions > > "p45cal" wrote: > > > > > 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
|
Pages: 1 2 3 Prev: Pivot table has source data has exceeded 65536 records typemisma Next: FormulaR1C1 using variables |