Re: [ExcelVBA] Off Topic: TRIM or a different function as part of loop

Hi -

I would use something like the following:

Sub ConvertString()
Dim i As Variant
Do While ActiveCell.Value <> ""
i = ActiveCell.Value
Debug.Print i
i = CDate(i) 'the CDate can change to CStr, CVar, CInt, etc depending on
what you would like to do.
Debug.Print i
ActiveCell.FormulaR1C1 = Trim(i)
ActiveCell.Offset(1, 0).Range("A1").Select
Loop

End Sub

Hopefully that helps.

Dawn Bleuel


On Thu, Apr 18, 2013 at 10:21 AM, Manny <mgarza@fellowes.com> wrote:

> **
>
>
> I have a column in Excel that is formatted by default as a date with the
> following format m/d/yyyy h:mm:ss AM/PM
> The problem I'm having is sometimes the value getting imported does not
> have the h:mm:ss part of the date so Excel displays it as number as text.
> When I check the actual value in the cell I notice there are 3 null values
> after the date number.
>
> If I click in the cell with a date number and hit enter Excel recognizes
> the date number and converts it into a date and life is sweet again.
>
> What kind of loop can I use to have VBA check the cell and if needed
> convert the date number as text into date while leaving the cells that are
> already displayed correctly as is?
>
> Thanks,
>
>
>



--
Dawn Bleuel
"*Dedicated to Living Healthier"*
Live Total Wellness
www.LiveTotalWellness.com\dbleuel <http://www.livetotalwellness.com/dbleuel>


[Non-text portions of this message have been removed]



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

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

----------------------------------Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/ExcelVBA/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/ExcelVBA/join
(Yahoo! ID required)

<*> To change settings via email:
ExcelVBA-digest@yahoogroups.com
ExcelVBA-fullfeatured@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
ExcelVBA-unsubscribe@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/