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.
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

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")
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,866
Messages
5,834,073
Members
430,260
Latest member
MANICX100

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