Dawn you are awesome! This works fine and hopefully I will be able to incorporate it into other macros where I have the same issue.
One question, actually for anybody that has the time and expertise in VBA: I am a novice in VBA and I'm trying to understand the code below instead of just copying it for my use. Some of the lines I understand fine, here are the lines that I don't understand:
i = ActiveCell.Value
Debug.Print i
i = CDate(i)
Debug.Print i
In the four lines above i is set twice (??)
What does "Debug.Print i" do?
ActiveCell.FormulaR1C1 = Trim(i)
I understand the ActiveCell reference but not the Trim(i) part, actually in general I probably don't understand Trim correctly.
And finally
ActiveCell.Offset(1, 0).Range("A1").Select
I understand the concept of Offset but not the ".Range("A1").Select" part of this line.
If anybody can help me understand the above questions it would be greatly appreciated and again I've never taken VBA classes so I'm sorry if these are annoying questions.
Thanks in advance,
Manny
--- In ExcelVBA@yahoogroups.com, Dawn Bleuel <dlbleuel@...> wrote:
>
> 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@...> 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]
>
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (4) |
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