VBA cannot resolve type mismatch error with vlookup

[DeanP]

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:


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:
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.

[Fluff]

Cross posted

[大灰狼1976]

what is the inputed contents？

[Paul_Hossler]

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



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

[DeanP]

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.