DavidLee91
06-24-2015, 09:10 PM
Hi Guys!
My sheet1 Cell B1 Contains the following text: "Report 20JUN2015".
How Can I extract the date and stores them in variables dteFromDate? I would like to maintain the format of "20JUN2015" while storing this into dteFromDate.
I have the following VBA codes, however, it only work if my text is: "Report 19/6/2015". How can I edit the following code to suit my need?
Any helps would be appreciated guys! PS: The following codes wasnt coded by me. I got this from MREXCEL.com.
: pray2:
THANKS folks!
Option Explicit
Sub CallIt()
Dim MyDates() As Date
Dim dteFromDate As Date
If DateRet(Range("B1").Text, MyDates) Then
dteFromDate = MyDates(0)
MsgBox (dteFromDate)
Else
'... something to handle if the string is glitched in some manner.
End If
End Sub
Function DateRet(ByVal CellText As String, Dates() As Date) As Boolean
Static REX As Object ' RegExp
Dim rexMC As Object ' MatchCollection
Dim aryTemp(0 To 1) As Date
Dim ValsSplit As Variant
If REX Is Nothing Then
Set REX = CreateObject("VBScript.RegExp")
With REX
.Global = True
.Pattern = "\b[0-9]{1,2}\/[0-9]{1,2}\/[0-9]{4}\b"
End With
End If
With REX
If .Test(CellText) Then
Set rexMC = .Execute(CellText)
'// Since we needed Global = True to return both dates in the string, //
If rexMC.Count = 1 Then
ValsSplit = Split(.Execute(CellText)(0), "/")
aryTemp(0) = DateSerial(ValsSplit(2), ValsSplit(0), ValsSplit(1))
Dates() = aryTemp
DateRet = True
Else
DateRet = False
End If
Else
DateRet = False
End If
End With
End Function
My sheet1 Cell B1 Contains the following text: "Report 20JUN2015".
How Can I extract the date and stores them in variables dteFromDate? I would like to maintain the format of "20JUN2015" while storing this into dteFromDate.
I have the following VBA codes, however, it only work if my text is: "Report 19/6/2015". How can I edit the following code to suit my need?
Any helps would be appreciated guys! PS: The following codes wasnt coded by me. I got this from MREXCEL.com.
: pray2:
THANKS folks!
Option Explicit
Sub CallIt()
Dim MyDates() As Date
Dim dteFromDate As Date
If DateRet(Range("B1").Text, MyDates) Then
dteFromDate = MyDates(0)
MsgBox (dteFromDate)
Else
'... something to handle if the string is glitched in some manner.
End If
End Sub
Function DateRet(ByVal CellText As String, Dates() As Date) As Boolean
Static REX As Object ' RegExp
Dim rexMC As Object ' MatchCollection
Dim aryTemp(0 To 1) As Date
Dim ValsSplit As Variant
If REX Is Nothing Then
Set REX = CreateObject("VBScript.RegExp")
With REX
.Global = True
.Pattern = "\b[0-9]{1,2}\/[0-9]{1,2}\/[0-9]{4}\b"
End With
End If
With REX
If .Test(CellText) Then
Set rexMC = .Execute(CellText)
'// Since we needed Global = True to return both dates in the string, //
If rexMC.Count = 1 Then
ValsSplit = Split(.Execute(CellText)(0), "/")
aryTemp(0) = DateSerial(ValsSplit(2), ValsSplit(0), ValsSplit(1))
Dates() = aryTemp
DateRet = True
Else
DateRet = False
End If
Else
DateRet = False
End If
End With
End Function