[ExcelVBA] Re: convert text to numbers with For Each xCell

 

Paul,
One last question, in your example below:
If (xCell.Value & "X" <> "X") Then
What does the "X" represent? Should I interpret the "X" literally or as a references to a variable? Keep in mind I am a novice VBA'er, I'm learning as I go.

Thanks,

--- In ExcelVBA@yahoogroups.com, Paul Schreiner wrote:
>
> I suspect then, that the value in the cell isn't really "blank".
> It is probably either a "null" value, or a space..
> which excel does sometimes, depending on how the values were generated.
>
> I have reports that process nearly 150,000 lines of data daily.
> There have been times that I've encountered this in multiple records every day
> for a week, then not again for the rest of the month.
>
> To get around it, I (almost always) use something like:
>
> If (xCell.Value & "X" <> "X") then
>  
> but
> On Error Resume Next
> works too.
>
> It's just that sometimes, the error is significant, and I want to know about it.
> For instance,
> You're wanting to skip "blank" values.
> What if the has an alpha character?
> using On Error will simply skip over it instead of alerting you to the need to
> correct a value.
>
> It really depends on what you're trying to accomplish, and the nature of your
> data.
>
> good luck,
>
> 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 5:37:08 PM
> Subject: [ExcelVBA] Re: convert text to numbers with For Each xCell
>
>  
> 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]
> >
>
>
>
>
> [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 (7)

Recent Activity:

• New Members 3

Visit Your Group

----------------------------------
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

----------------------------------

Switch to: Text-Only, Daily Digest • Unsubscribe • Terms of Use • Send us Feedback

.

__,_._,___