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]
>
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (5) |
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