VBA cannot resolve type mismatch error with vlookup

I am using vlookup to return a date from my spreadsheet. I have tried to convert the date to Long by using CLng & CDate in
my code, but I am still getting a type mismatch error.


Here is my code:

Code:

---

Private Sub TextBox9_Change()
Dim wsJournal As Worksheet
Dim MyDate As Long

    Set wsJournal = Worksheets("Journal")
    MyDate = CLng(CDate(Me.tbDate.Value))
   
    MyDate = Application.VLookup(Me.tbPeriod, [YrDte], 1, False)
   
End Sub

---

Debugger shows that the error is in this line:

Code:

---

MyDate = CLng(DateValue(Me.tbDate.Value))

---

I've also tried: MyDate = CLng(CDate(Me.tbDate.Text)), Dim As Date, Dim As Double - but still the same error!


Any advice would be appreciated.

Cross posted

what is the inputed contents？

1. Cross posting without providing a link(s) is a no-no

2. tbDate from the UserForm is a string, and CDate makes that into as string and puts in a String variable called myDate

3. The CLng is in the VLookup

4. You didn't say what the Range YrDte was , but are you sure you want to use VLookup to get column 1??

For testing, I used two columns, with the date in the first and something else in the second

Code:

---

Option Explicit

Private Sub TextBox9_Change()
'    Dim wsJournal As Worksheet
'    Set wsJournal = Worksheets("Journal")
    Dim MyDate As Date
    Dim tbDate As String    '  for testing
    tbDate = "2/3/2019"    '  for testing
    MyDate = CDate(tbDate)
   
    Worksheets("Sheet1").Range("A:B").Name = "YrDte"
   
    MsgBox Application.WorksheetFunction.VLookup(CLng(MyDate), [YrDte], 2, False)
   
End Sub

---

I have another text box in my userform that is populated by user input with a values 1 - 12 representing the months of the
year. However, the date that I need to retrive from my worksheet changes from week to week. So if the userinput is 02,
it can represent any date from 1-28 Feb. The actual date is in the worksheet and that is what I'm bringing into my userform.
Eg In col E = 02 Col D = 15/02/2019. Next week E = 02 but D = 07/02/2019 etc.