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 mgarza@fellowes.com>
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 (8) |
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