Derek,
Success! Thanks again for the assist, I wasn't sure how to surround the line that performs the conversion but with a little trial and error I got it.
--- In ExcelVBA@yahoogroups.com, Derek Turner wrote:
>
> 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
> >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 (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