Excel Macro to find a given date

[mhanaan]

Hi, Im new to VB Macro's Im trying to creat a macro that finds a date from a list in excel and then counts down 14 cell from the given date and then display the date that is 14 cells down.

There Are three Issues Im Facing:

Firstly my current macro requires me to edit the find criteria for every new search. would it be possible to have some sort of input box where user can enter the date, and have the macro use this value for its search criteria

secondly the result has to be displayed seperatly in some form, like in a different cell, my current macro simply highlights the cell that is 14 cells down from the given date.

Thanks in Advance..

 

[jmthompson]

Please post your current code and preferably a sample of your sheet

 

[VoG]

Try like this

Sub FindDate()
Dim D As Date, F As Range
D = DateValue(InputBox("Enter date"))
Set F = Columns("A").Find(what:=D, LookIn:=xlValues, lookat:=xlWhole)
If Not F Is Nothing Then MsgBox F.Offset(14).Value
End Sub

 

[mhanaan]

This Is the code i have recorded:

Range("A1").Select
Cells.Find(What:="01/08/2011", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(14, 0).Range("A1").Select
Application.CutCopyMode = False
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Range("A1").Select
End Sub

The Date list Is as follows and is in column D
<table border="0" cellpadding="0" cellspacing="0" width="75"><col style="mso-width-source:userset;mso-width-alt:2742;width:56pt" width="75"> <tbody><tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt; width:56pt" align="right" height="20" width="75">01/08/2011</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" align="right" height="20">02/08/2011</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" align="right" height="20">03/08/2011</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" align="right" height="20">04/08/2011</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" align="right" height="20">05/08/2011</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" align="right" height="20">08/08/2011</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" align="right" height="20">09/08/2011</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" align="right" height="20">10/08/2011</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" align="right" height="20">11/08/2011</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" align="right" height="20">12/08/2011</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" align="right" height="20">15/08/2011</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" align="right" height="20">16/08/2011</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" align="right" height="20">17/08/2011</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" align="right" height="20">18/08/2011</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt" align="right" height="20">19/08/2011</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" align="right" height="20">22/08/2011</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" align="right" height="20">23/08/2011</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" align="right" height="20">24/08/2011</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" align="right" height="20">25/08/2011</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" align="right" height="20">26/08/2011</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" align="right" height="20">29/08/2011</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" align="right" height="20">30/08/2011</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" align="right" height="20">31/08/2011</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" align="right" height="20">01/09/2011</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" align="right" height="20">02/09/2011</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" align="right" height="20">03/09/2011</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" align="right" height="20">04/09/2011</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" align="right" height="20">05/09/2011</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" align="right" height="20">08/09/2011</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" align="right" height="20">09/09/2011</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" align="right" height="20">10/09/2011</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" align="right" height="20">11/09/2011</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt" align="right" height="20">12/09/2011</td> </tr> </tbody></table>

 

[mhanaan]

Thanks Vog your Code Worked Fine, Would also like to know if this code would work if i save the excel file as a web page, and only have a command button to run your code. if not how would it be possible to get around this.