Dear Manny
Surround the line that does the conversion like this :-
on error resume next
xCell.Value = CDec(xCell.Value)
on error goto 0
Regards
Derek Turner +++
>________________________________
> From: Manny mgarza@fellowes.com>
>To: ExcelVBA@yahoogroups.com
>Sent: Monday, 7 January 2013, 17:33
>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 (3) |
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