Go to specific cell based on user input

gr8trthanu

Board Regular
Joined
Nov 14, 2008
Messages
103
I am creating a schedule where I can schedule my clients.

Instead of scrolling down 10,000+ lines to the date I need to make the appointment on I would like to:

#1 enter the date in cell D2
#2 hit a command button called cmdsearch date
#3 excel would look down column "D" for the value I placed in "D2" and select the cell holding the exact date.

All the dates are in column D as well.

I have tried to search for similar code and have failed.

thank you in advance.
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

FDibbins

Well-known Member
Joined
Feb 16, 2013
Messages
6,723
Try this...
G​
H​
I​
J​
1​
1/1/2016​
1/5/2016​
1/5/2016​
2​
1/2/2016​
3​
1/3/2016​
4​
1/4/2016​
5​
1/5/2016​
6​
1/6/2016​
7​
1/7/2016​
8​
1/8/2016​
9​
1/9/2016​
10​
1/10/2016​
11​
1/11/2016​
12​
1/12/2016​
Selected date is in I1
Hyperlink is in J1
J1=HYPERLINK("#"&"G"&MATCH(I1,$G$1:$G$29,0),I1)
 

gr8trthanu

Board Regular
Joined
Nov 14, 2008
Messages
103
ThanKs Redbeard...that pointed me to a thread which I understand the code.

Sub Button18_Click()
Dim rngFindRange As Range
Dim strRefNum As String
strRefNum = Sheets("Main Schedule").Range("A1")
Set rngFindRange = Sheets("Main Schedule").Range("D:D").Find(strRefNum, LookIn:=xlValues, LookAt:=xlWhole)
If rngFindRange Is Nothing Then
MsgBox strRefNum & " not found.", vbExclamation, "Reference Number No Found"
Else
Sheets("Main Schedule").Activate
rngFindRange.Select
End If
End Sub

However, when I use dates the message comes back the value can not be found.

However if I type the work cat in the A1 cell and then place the work cat in D560 it is found.

Is there something specific I am not doing with the formatting of the dates?

they are both formatted the same...
 

FDibbins

Well-known Member
Joined
Feb 16, 2013
Messages
6,723

ADVERTISEMENT

Did you see my suggestion?
 

gr8trthanu

Board Regular
Joined
Nov 14, 2008
Messages
103
Thanks FDibbins.
is that a formula I place in the cell or create a command button with code?
 

gr8trthanu

Board Regular
Joined
Nov 14, 2008
Messages
103

ADVERTISEMENT

that actually works but I would have to then click the hyperlink.
Is there a way for the cell with the right date be selected?
 

FDibbins

Well-known Member
Joined
Feb 16, 2013
Messages
6,723
not with my method. It is a formula and is dependent on a date being entered in another cell.

You could change the last part to instead, say something like "click here"

J1=HYPERLINK("#"&"G"&MATCH(I1,$G$1:$G$29,0),"click here")
 

Watch MrExcel Video

Forum statistics

Threads
1,122,564
Messages
5,596,875
Members
414,106
Latest member
Tigretto

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
Top