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]
>
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (9) |
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