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 mgarza@fellowes.com>
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]
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (6) |
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