Go to specific cell based on user input

[gr8trthanu]

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.

 

[RedBeard]

This might be of interest: How to search a string in a single column (A) in excel using VBA - Stack Overflow

 

[FDibbins]

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]

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]

Did you see my suggestion?

 

[gr8trthanu]

Thanks FDibbins.
is that a formula I place in the cell or create a command button with code?

 

[gr8trthanu]

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]

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")

 

[Macropod]

Cross-posted at: http://www.excelforum.com/showthread.php?t=1167359

Please read our policy on Cross-Posting in rule #13: http://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html