Dear ◄◁◂◃ D e v ▹▸▷►
Yes I would certainly like to see how you did it.
Derek Turner
+++
>________________________________
> From: ◄◁◂◃ D e v ▹▸▷► tryingtokool@gmail.com>
>To: ExcelVBA@yahoogroups.com
>Sent: Thursday, 10 January 2013, 16:39
>Subject: Re: [ExcelVBA] Re: convert text to numbers with For Each xCell
>
>Hi all,
>
>I have an extra utility to convert number into text
>
>But it conver only. Rupees
>
>
>I.E 100
>
>Then it will say one hundred only
>
>If anyone need this pls let me know will send this
>
>
>-----Original Message-----
>From: "Manny" mgarza@fellowes.com>
>Sender: ExcelVBA@yahoogroups.com
>Date: Thu, 10 Jan 2013 15:40:52
>To: ExcelVBA@yahoogroups.com>
>Reply-To: ExcelVBA@yahoogroups.com
>Subject: [ExcelVBA] Re: convert text to numbers with For Each xCell
>
>Thanks Paul, that was a cool excercise and now I do know a little more about nothing ;-)
>
>I have not yet come across a situation where I might need to compare cell values with a null string character but this is good to know for future reference.
>
>I mostly use my spreadsheets to do Vlookup and correct me if I'm wrong but text strings return an invalid value when being called on in Vlookup. Thus I am always converting my values into number (I'm guessing our database stores a lot of fields as string values instead of numerical because they want it to be an alphanumeric field when really it's used as numerical 99% of the time, but I digress).
>
>By the way is text string the same as string? Everything gets so literal in programmming so I'm just curious to know if there is more than 1 type of string as far as computers are concerned.
>
>Thanks and have a good one!
>
>--- In ExcelVBA@yahoogroups.com, Paul Schreiner wrote:
>>
>> The fact that the "X" is in quotes, means that it is taken literally.
>> just about any time you see something in quotes, it means that what is in
>> between the quotes is a text string.
>>
>> Normally (unless you're an experinced programmer), when you see an "equate"
>> expression:
>>
>> if (x = y)
>> then your mind interprets this to mean "is x EQUAL TO y"
>> but to a computer, it's actually much more than that.
>> Intellectually, 123 is the same as "123"
>> but to a computer, it is not. One is a numeric value and one is a text string.
>> the same thing applies to the test: if () = ""
>> Intellectually, you see nothing between the ( ), and nothing between the quotes,
>> but to a computer () is nothing, but "" is a null string.. which isn't rally the
>> same thing!
>>
>> I know, it's complicated and confusing.
>> Since I don't know HOW your sheet is created, I made some assumptions.
>>
>> What I've encountered before is that depending on HOW the values get put into
>> excel, even though a cell may LOOK empty (or blank), there MAY actually be a
>> null string there!
>> ------------------------------------
>> Try this experiment:
>> in Cell A2 and B2, put =RANDBETWEEN(1,10)
>> in Cell C2, put: =if(A2=B2,"",B2-A2)
>> copy all 3 cells down 20 rows or so.
>>
>> Now hit F9 until you see some "blanks" in column C.
>> Then select all your cells and Copy/Paste Special->Values.
>>
>> Select the first value in column C.
>> "normally" you should be able to hit the "end" key and down arrow and it would
>> move to the last non-blank cell before a blank cell.
>> But instead, it moves to the last value!
>>
>> If you turn on your filters, and elect to show only "blanks", it will show these
>> seemingly "empty" rows.
>> Yet using the end-down keystrokes, it acts as if there's something there.
>> Well, actually there is... "" is a null string character.
>> ---------------------------------------
>>
>> In the past, I've encountered cases where I've tested the value in a cell and
>> compared it to ""
>> and it said it DIDN'T match, even though I THOUGHT I was comparing it to a
>> "blank".
>>
>> So, to avoid this, I routinely takes steps to convert the value to a string by
>> combining the value with a string value (in this case "X"), you could use
>> xCell.Value & "Manny"
>> and fulfill the same purpose, but require more keystrokes!
>>
>> I then compare it to the simple string value (in this case "X")
>> By converting the cellvalue to a string, any null/blank/nothing character gets
>> dropped and I get:
>>
>> IF ("X" = "X")
>>
>> hope this helps your understanding about nothing (/null/blank) ;o)
>>
>> Paul
>> -----------------------------------------
>> "Do all the good you can,
>> By all the means you can,
>> In all the ways you can,
>> In all the places you can,
>> At all the times you can,
>> To all the people you can,
>> As long as ever you can." - John Wesley
>> -----------------------------------------
>>
>>
>>
>>
>> ________________________________
>> From: Manny
>> To: ExcelVBA@yahoogroups.com
>> Sent: Wed, January 9, 2013 9:41:38 AM
>> Subject: [ExcelVBA] Re: convert text to numbers with For Each xCell
>>
>>
>> Paul,
>> One last question, in your example below:
>> If (xCell.Value & "X" <> "X") Then
>> What does the "X" represent? Should I interpret the "X" literally or as a
>> references to a variable? Keep in mind I am a novice VBA'er, I'm learning as I
>> go.
>>
>> Thanks,
>>
>> --- In ExcelVBA@yahoogroups.com, Paul Schreiner wrote:
>> >
>> > I suspect then, that the value in the cell isn't really "blank".
>> > It is probably either a "null" value, or a space..
>> > which excel does sometimes, depending on how the values were generated.
>> >
>> > I have reports that process nearly 150,000 lines of data daily.
>> > There have been times that I've encountered this in multiple records every day
>>
>> > for a week, then not again for the rest of the month.
>> >
>> > To get around it, I (almost always) use something like:
>> >
>> > If (xCell.Value & "X" <> "X") then
>> >
>> > but
>> > On Error Resume Next
>> > works too.
>> >
>> > It's just that sometimes, the error is significant, and I want to know about
>> >it.
>> > For instance,
>> > You're wanting to skip "blank" values.
>> > What if the has an alpha character?
>> > using On Error will simply skip over it instead of alerting you to the need to
>>
>> > correct a value.
>> >
>> > It really depends on what you're trying to accomplish, and the nature of your
>> > data.
>> >
>> > good luck,
>> >
>> > Paul
>> > -----------------------------------------
>> > "Do all the good you can,
>> > By all the means you can,
>> > In all the ways you can,
>> > In all the places you can,
>> > At all the times you can,
>> > To all the people you can,
>> > As long as ever you can." - John Wesley
>> > -----------------------------------------
>> >
>> >
>> >
>> >
>> > ________________________________
>> > From: Manny
>> > To: ExcelVBA@yahoogroups.com
>> > Sent: Mon, January 7, 2013 5:37:08 PM
>> > Subject: [ExcelVBA] Re: convert text to numbers with For Each xCell
>> >
>> >
>> > Thanks Paul I tried your suggestion but got the same runtime error. It did
>> >cycle
>> >
>> > through the first cell in Col D and returned a number but the blank cell in Col
>> >
>> > E caused the error again.
>> >
>> > --- In ExcelVBA@yahoogroups.com, Paul Schreiner wrote:
>> > >
>> > > Try using:
>> > >
>> > > For Each xCell In Selection
>> > > if (xCell.Value <> "") then xCell.Value = CDec(xCell.Value)
>> > > Next xCell
>> > >
>> > >
>> > >
>> > > Paul
>> > > -----------------------------------------
>> > > "Do all the good you can,
>> > > By all the means you can,
>> > > In all the ways you can,
>> > > In all the places you can,
>> > > At all the times you can,
>> > > To all the people you can,
>> > > As long as ever you can." - John Wesley
>> > > -----------------------------------------
>> > >
>> > >
>> > >
>> > >
>> > > ________________________________
>> > > From: Manny
>> > > To: ExcelVBA@yahoogroups.com
>> > > Sent: Mon, January 7, 2013 1:09:07 PM
>> > > Subject: [ExcelVBA] convert text to numbers with For Each xCell
>> > >
>> > >
>> > > Good day Wizards!
>> > > Your assistance has been extremely valuable and now I seek your expertise
>> >once
>> >
>> >
>> > > again.
>> > >
>> > > I am using the following code after defining the Dim RVS as Long:
>> > >
>> > > RVS = Worksheets("Standard_1").Range("D2:E2").End(xlDown).Row
>> > > Range("D2:E" & RVS&).Select
>> > > For Each xCell In Selection
>> > > xCell.Value = CDec(xCell.Value)
>> > > Next xCell
>> > >
>> > > There is always a value in Col D but there are cells in Col E that will
>> > > sometimes be blank. When the routine hits one of these blank cells I get:
>> > > runtime error '13' Type mismatch.
>> > > How do I get this code to ignore the blank cells in Col E and return either a
>> >
>> > > zero or nothing at all.
>> > >
>> > > If it makes any difference I am using Excel 2010 but the file is imported
>> >into
>> >
>> >
>> > > 2010 as file type Excel 2007. Previously this code worked fine when imported
>> >as
>> >
>> > >
>> > > file type Excel 97-2003 and if the cell in Col E was blank a zero was
>> >returned
>> >
>> >
>> > > and life was good.
>> > >
>> > > I have no choice on the file type to be imported as this is the result of IT
>>
>> > > changes in database report applications.
>> > >
>> > > Thanks,
>> > >
>> > >
>> > >
>> > >
>> > > [Non-text portions of this message have been removed]
>> > >
>> >
>> >
>> >
>> >
>> > [Non-text portions of this message have been removed]
>> >
>>
>>
>>
>>
>> [Non-text portions of this message have been removed]
>>
>
>
>
>
>
>[Non-text portions of this message have been removed]
>
>
>
>------------------------------------
>
>----------------------------------
>Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.
>
>----------------------------------
>Visit our ExcelVBA group home page for more info and support files:
>http://groups.yahoo.com/group/ExcelVBA
>
>----------------------------------
>More free tutorials and resources available at:
>http://www.mousetrax.com
>
>----------------------------------Yahoo! Groups Links
>
>
>
>
>
>
[Non-text portions of this message have been removed]
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (13) |
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------
Tidak ada komentar:
Posting Komentar