What happens if you take the as long off the Dim and run it
Ralph Gregory
Contracts Manager
Skaino Services
West March
DAVENTRY
NN11 4SA
----- Reply message -----
From: "Manny" mgarza@fellowes.com>
To: ExcelVBA@yahoogroups.com>
Subject: [ExcelVBA] convert text to numbers with For Each xCell
Date: Mon, Jan 7, 2013 5:33 pm
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 (1) |
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