selecting activecell using a vlookup formula

[mummbles74]

I am trying to select a cell to output the data from my userform to. I have a text box on the form where you enter a date. on the work sheet I have a list of dates for time keeping. I want the userform to output its data in the cells next to the date that is entered in the text box. I have tried using activecell.formula = (=vlookup etc but can't seem to get this to work is there a way of doing this?

Please help.

 

[Andrew Poulsom]

What's the name of the worksheet and in what range are the dates?

 

[mummbles74]

the worksheet is called "Daily Input Sheet" and the dates are in column B (B3:B541)

 

[Andrew Poulsom]

See if you can build on this:

Private Sub CommandButton1_Click()
    Dim Cell As Range
    If Len(TextBox1.Text) = 0 Then Exit Sub
    With Worksheets("Daily Input Sheet").Range("B3:B541")
        Set Cell = .Find(What:=DateValue(TextBox1.Text), After:=.Cells(.Cells.Count), LookIn:=xlFormulas, _
                        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext)
        If Not Cell Is Nothing Then
            MsgBox "Found at " & Cell.Address(False, False) & "."
        Else
            MsgBox "Date not found."
        End If
    End With
End Sub

 

[mummbles74]

thanks for that I am one step closer. It tells me that todays date is in cell B129. how do I make cell b129 the activecell?

Sorry if this is simple just never had to do it this way before, always just added to the bottom of a list in the past

 

[mummbles74]

I used cell.select and that seemed to work is that the best way?

 

[Andrew Poulsom]

You don't need to Select anything. Just use Cell as you would ActiveCell.

 

[mummbles74]

Thank you very much for the help. really appreciate it. With out taking up to much of your time. Is there a way to have the date in the textbox entered using some sort of pop up calendar? I have tried in the past creating a serperate userform and tring to make it look like a calendar but didn't really get to far with it as i assumed there was an easier way.

Again really appreciate the help with the active cell problem

 

[Andrew Poulsom]

You can use the Calendar Control:

http://www.fontstuff.com/vba/vbatut07.htm

http://www.mrexcel.com/forum/showthread.php?t=394370

 

[mummbles74]

Thanks for that all working now. did realise that the calendar thing would be so easily done. Worked perfectly.

thanks again, another lesson learned