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