selecting activecell using a vlookup formula

mummbles74

Board Regular
Joined
Nov 14, 2009
Messages
120
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
the worksheet is called "Daily Input Sheet" and the dates are in column B (B3:B541)
 
Upvote 0
See if you can build on this:

Code:
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
 
Upvote 0
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
 
Upvote 0
I used cell.select and that seemed to work is that the best way?
 
Upvote 0
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
 
Upvote 0
Thanks for that all working now. did realise that the calendar thing would be so easily done. Worked perfectly.

thanks again, another lesson learned
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top