Runtype & Type Mismatch error : Change Text String to Date

[rubikscube991]

I was writing a code to change date in string format to date format while transferring from userform to cell.
for example 12.2.2017 would change to 12-02-2017 or
12/02.2017 would change to 12-02-2017

Code as below, but it is giving runtype error & Type mismatch errors in substitute formula. pls help. thanks a lot in advance..!!

Private Sub CommandButton1_Click()

Dim a As String
Dim b As Integer

With Application.WorksheetFunction

'a = TextBox1.Value
Sheet1.Range("R1").Value = TextBox1.Value

'MsgBox a

'##Error in this line##
b = DateValue(.Substitute(.Substitute(.Substitute(Sheet1.Range("R1").Value, Chr(46), Chr(45)), Chr(47), Chr(45)), Chr(92), Chr(45)))

MsgBox b

If IsNumeric(b) Then

    Sheet1.Range("Q32000").End(xlUp).Value = b
    Activecell.NumberFormat = "[$-en-IN,1]dd/mm/yy;@"
Else
    MsgBox "Please enter date in dd-mm-yyyy format"
End If

End With

End Sub

 

[Kenneth Hobson]

I would use Replace().

Private Sub CommandButton1_Click()
  Dim a As String, b As Date
 
  With Application.WorksheetFunction
    'a = TextBox1.Value
    Sheet1.Range("R1").Value = TextBox1.Value
   
    'MsgBox a
    'b = DateValue(.Substitute(.Substitute(.Substitute(Sheet1.Range("R1").Value, Chr(46), Chr(45)), Chr(47), Chr(45)), Chr(92), Chr(45)))
    b = DateValue(Replace(Replace(Replace(Sheet1.Range("R1").Value, ".", "/"), "\", "/"), "-", "/"))
    MsgBox b
   
    If IsNumeric(b) Then
      MsgBox b
      Sheet1.Range("Q32000").End(xlUp).Value = b
      ActiveCell.NumberFormat = "[$-en-IN,1]dd/mm/yy;@"
    Else
        MsgBox "Please enter date in dd-mm-yyyy format"
    End If
  End With
End Sub

 

[rubikscube991]

thanks a lot for your attention Mr.Kenneth. Well I made changes as you suggested. But I see something strange here

1) instead of replacing 12.4.2017 as 12/04/2017, It replaces it as 12-4-2017 only. And also
2) datevalue doesn't work and Msgbox displayes "Please enter date in dd-mm-yyyy format".

Pls help. thanks

 

[Kenneth Hobson]

The first MsgBox(b) only returns your own regional date formatted date string. If you want some other date string, use Format().

As for the 2nd MsgBox(), b is a date, not a number per se. So, IF IsNumeric(b) will always jump to Else if b is a date. Use this if you like:

If IsDate(b) Then
  MsgBox Format(b, "mm/dd/yyyy") '12.4.2017 = 12/04/2017

Dates can be tricky. A user may enter a date string one way but the regional settings might use another method so DateValue() can lead one astray then.

 

[rubikscube991]

thanks a lot for your attention Mr.Kenneth. IsDate was the missing thing. I didn't know about it. I made changes as per my needs. Its working now. thanks a lot for all your help.