You said:
"... correct me if I'm wrong ..."
So.. you ASKED for it !!
with regard to VLookup:
No, text strings do NOT (normally) return invalid when used in VLookup!
Vlookup is OFTEN use to look up values from a table in which the key field it
text.
(like: you have a list of names with phone numbers, in another sheet, you have a
list of names and you want to look up the phone numbers from the previous sheet)
The problem you often run into is:
if you've created a report from some source and opened it in Excel,
Excel will sometimes import a numeric column as "general", which is interpreted
as "text".
So.. basically, Excel interprets the value "123" as a string of characters.
If you tried to have it show two place decimals, expecting to see 123.00
it will NOT, because text doesn't HAVE decimals.
Now, if you have another sheet and you want to look up something using the
numeric column.
And, in this sheet you enter 123.
Vlookup will NOT match 123 with "123", and you'll get #N/A.
But if you edit the text cell, and exit the cell without changing anything,
Excel will decide that since the characters are all numeric, it will convert the
numeric TEXT to numbers.
----------------------------------------------------------
If you want to get REAL technical, here's what's going on:
An INTEGER data type is a number that can be from -32,768 to 32,768
But a STRING data type can be variable length and contain up to 2 BILLION
characters!
behind-the-scenes, a STRING is stored as an array of characters.
So, let's say that you "declare" your variables as:
Dim Val_1 as Integer
Dim Val_2 as String
Then use:
Val_1 = 123
Val_2 = "123"
On the surface, they look the same, but what's happening is that Val_2 is an
array of characters:
Val_2(0) = "1"
Val_2(1) = "2"
Val_2(2) = "3"
So.., is it really logical to expect the computer to "assume" that Val_1 is the
same as Val_2(0) & Val_2(1) & Val_2(2) ??
If you wanted to get the second character in Val_2, VBA uses the MID() function.
MID(Val_2,2,1)
(the second character from the left, retrieve (1) character.)
Other programming languages would allow you to use Val_2(1), but not in VBA.
(Arrays begin with index of 0)
----------------------------------------------------------
confused yet?
Most likely, your database is storing the numbers as numeric fields WITHIN THE
DATABASE.
(otherwise the database cannot do any arithmetic with the field, or sort by
number!)
But in REPORTING from the database, the field is being output as text.
(because in a report, TEXT can have fonts, NUMBERS do not... ??)
---------
"text string" = "string"
Actually, there are many types of "strings" in programming.
binary strings
hexadecimal strings
text strings
character strings
and yes, "numeric" strings
The term "string" by itself is NORMALLY used to refer to a text or
"alpha-numeric character" string.
simply because that's the most common usage.
Computers are VERY literal.
In the distant past, EVERYTHING needed to be explicitly defined.
----------------------------------------------------------
In my FIRST programming class, we actually had to write a program using binary
switches on a VAX PDP1134
that would accept signals from a keyboard (interrupts) and convert the signal
to a character number, then send the character to the dot-matrix printer (there
was no monitor).
LUCKILY, the entire character set was "stored" on the memory chip so we didn't
have to load the character definitions each time!
so basically, the lab exercise was to write a program to be able to write
programs using a keyboard instead of binary switches!
----------------------------------------------------------
Believe it or not, it's easier now because many things are "assumed".
(They call them "defaults")
But that's a double-edged sword.
If you don't know what is being "assumed", you can sometimes get unexpected
results.
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: Thu, January 10, 2013 10:48:44 AM
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]
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (10) |
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