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

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/ExcelVBA/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/ExcelVBA/join
(Yahoo! ID required)

<*> To change settings via email:
ExcelVBA-digest@yahoogroups.com
ExcelVBA-fullfeatured@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
ExcelVBA-unsubscribe@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/