Hi Noemi,
You have to select the range first. That is what the following code is
about. It either sets the range to a single selected cell or a selected range.
If Selection.Cells.Count = 1 Then
Set DtRange = ActiveCell
Else
Set DtRange = Selection
End If
--
Regards,
OssieMac
"Noemi" wrote:
> Thanks OssieMac
>
> However I am not sure how oCell is getting its data from, it keeps refering
> to cell A1 which is not what I want.
>
> Can you help clarify.
>
> Thanks
> Noemi
>
> "OssieMac" wrote:
>
> > Hi Noemi,
> >
> > The following was a recent reply. I have left the contributors name on the
> > bottom to give credit where credit is due.
> >
> > The following macro converts date strings and values in the selected range
> > between mm/dd/yyyy and dd/mm/yyyy. The output is in the
> > form of date values that you can format as dates via Format|Cells|Number|Date.
> >
> > Sub ConvertDateFormat()
> > Dim DtRange As Range
> > Dim oCell As Range
> > Dim oTxt As String
> > If Selection.Cells.Count = 1 Then
> > Set DtRange = ActiveCell
> > Else
> > Set DtRange = Selection
> > End If
> > With Application
> > On Error Resume Next ' In case there are no xlConstants or convertible
> > dates in DtRange
> > For Each oCell In DtRange.SpecialCells(xlConstants)
> > oTxt = oCell.Text
> > If UBound(Split(oTxt, "/")) = 2 Then _
> > oCell.Value = CDate(Split(oTxt, "/")(1) & "/" & Split(oTxt, "/")(0) & "/"
> > & Split(oTxt, "/")(2))
> > Next oCell
> > End With
> > End Sub
> >
> > Note that no conversion is performed if the date is embedded in a formula or
> > the output would be an invalid date.
> >
> > --
> > Cheers
> > macropod
> > [MVP - Microsoft Word]
> >
> >
> > --
> > Regards,
> >
> > OssieMac
> >
> >
> > "Noemi" wrote:
> >
> > > Hi
> > > Does anyone know how to convert american dates into australian dates using
> > > code or if not code then in excel.
> > >
> > > My main reason for wanting this is to run a comparison between the 2 dates
> > > to see if the match however if the american date has a lower number then the
> > > program assumes it is an australian date.
> > >
> > > Thanks
> > > Noemi
|
Similar Threads
