Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: userform textbox using vlookup date could not give correct answer (40850)
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Nov 2009
    Location
    Iowa City, IA
    Posts
    42
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default userform textbox using vlookup date could not give correct answer (40850)

    Hi,

    I created an UserForm that looks for Student Name his/her Conferenece Date through a VLookup.

    The following is my code:

    Private Sub cmdFind_Click()
    With frmConference
    .txtTranslator.Value = Application.WorksheetFunction.VLookup(txtStudentName.Value, Sheets("SPANISH").Range("A2:F113"), 6, 0)
    .txtDate.Value = Application.WorksheetFunction.VLookup(txtStudentName.Value, Sheets("SPANISH").Range("A2:F113"), 5, 0)
    .txtTime.Value = Application.WorksheetFunction.VLookup(txtStudentName.Value, Sheets("SPANISH").Range("A2:F113"), 4, 0)
    End With
    End Sub

    The txtTranslator and txtTime works correctly, however txtDate give me a number '40850' not a Date?

    How can I change this number for the actual Date?

    Thank you in advance for your help!

  2. #2
    Board Regular Worf's Avatar
    Join Date
    Oct 2011
    Location
    Rio, Brazil
    Posts
    3,729
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    2 Thread(s)

    Post Re: userform textbox using vlookup date could not give correct answer (40850)

    Hi

    40850 is actually a date (3/11/2011). It's the number of days since 1/1/1900.
    This is the way Excel stores dates.

    What are you going to do with this value? If you write it to a sheet cell formatted as date, it will display correctly.

  3. #3
    New Member
    Join Date
    Nov 2009
    Location
    Iowa City, IA
    Posts
    42
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: userform textbox using vlookup date could not give correct answer (40850)

    The textbox did display it as 40850 not as November 3, 2011, as the worksheet has it.

    How can I have this format to display in the text box?

  4. #4
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: userform textbox using vlookup date could not give correct answer (40850)

    Hello, try formatting the number with Format$(), e.g.,

    Code:
    MsgBox Format$(40850, "mmmm d, yyyy")
    MsgBox Format$(40850 + 20, "mmmm d, yyyy")

  5. #5
    New Member
    Join Date
    Nov 2009
    Location
    Iowa City, IA
    Posts
    42
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: userform textbox using vlookup date could not give correct answer (40850)

    Thank you guys for your response!

    Why isn't it displaying the Date that I already have in the worksheet cell F2, which is November 3, 2011 on the txtDate? It only displays as excel number 40850?

    The VLookup is working correctly!

  6. #6
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: userform textbox using vlookup date could not give correct answer (40850)

    Just format it. Excel stores dates and times as numbers, the Worksheet Cell is simply a formatted mask. So make your own mask for the UserForm, with Format$(), as shown.

  7. #7
    New Member
    Join Date
    Nov 2009
    Location
    Iowa City, IA
    Posts
    42
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: userform textbox using vlookup date could not give correct answer (40850)

    I already have the cell formatted as date.

    The problem is not on the cell, it's on the Text Box on the UserForm, it's not Displaying the Date as Date only as Number!

  8. #8
    Board Regular Worf's Avatar
    Join Date
    Oct 2011
    Location
    Rio, Brazil
    Posts
    3,729
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    2 Thread(s)

    Thumbs up Re: userform textbox using vlookup date could not give correct answer (40850)

    Ola Oscar
    See this code, just an example...

    Code:
    Private Sub UserForm_Click()
    Dim num&, datestring$
    num = 40850
    datestring = Format(num, "mmm d yyyy")
    TextBox1.Text = datestring
    End Sub

  9. #9
    New Member
    Join Date
    Nov 2009
    Location
    Iowa City, IA
    Posts
    42
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: userform textbox using vlookup date could not give correct answer (40850)

    Thanks Worf!

    It works, but if the Date changes, e.g. November 11, 2011 or for next year, how I make it to follow the actual date? It would not always be 40850!

    Gracias!

  10. #10
    Board Regular Worf's Avatar
    Join Date
    Oct 2011
    Location
    Rio, Brazil
    Posts
    3,729
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    2 Thread(s)

    Lightbulb Re: userform textbox using vlookup date could not give correct answer (40850)

    The num variable can receive a value from another variable like in
    num=mydate
    or a value from a sheet like in this example:
    Code:
    Private Sub UserForm_Click()
        Dim num&, datestring$
        num = Sheets("Spanish").Range("b2").Value
        datestring = Format(num, "mmm d yyyy")
        TextBox1.Text = datestring
    End Sub

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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