Consulting

Results 1 to 5 of 5

Thread: VBA cannot resolve type mismatch error with vlookup

  1. #1
    VBAX Regular
    Joined
    Nov 2018
    Location
    London, U.K.
    Posts
    99
    Location

    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:

    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.

  2. #2
    Cross posted

  3. #3
    VBAX Mentor 大灰狼1976's Avatar
    Joined
    Dec 2018
    Location
    SuZhou China
    Posts
    479
    Location
    what is the inputed contents?

  4. #4
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    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
    Attached Files Attached Files
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  5. #5
    VBAX Regular
    Joined
    Nov 2018
    Location
    London, U.K.
    Posts
    99
    Location
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •