I actually followed you all the way through the reply below without getting confused because this is exactly the type of environment I am working with in Excel (one spreadsheet is numbers stored as text, the other file is numbers as integer). But I did not realize I was getting the #NA error due to mismatch. I have become a big fan of RIGHT, LEFT and LEN as in =RIGHT(A1,LEN(A1)-8) when trying to eliminate string values that I want to drop and where I want to keep everything else after the first (or last) 8 string values. Sounds like you are well versed in programming which I find fascinating as a hobby (never took the courses to make it a part of a vocational trade). Nevertheless thanks for all the background and technical info, it really sheds light on why things work the way they do.
--- In ExcelVBA@yahoogroups.com, Paul Schreiner wrote:
>
> 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
> 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 (14) |
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