PDA

View Full Version : Quick table lookup



rossmiddleto
12-14-2010, 09:45 AM
Hi Everyone,

I just need a quick bit of code that opens a workbook takes the current date and works out what the coressponding week number would be. If someone could help me out it will be more than appreciated.

Lookup table looks like the following

Week - Friday - Max - Min
WK49_10 - 10Nov10 - 16NOV10 - 10NOV10

So if the current date is the 14th december the VBA would use this table to return a string variable that equals Wk49_10

Thank you in advance

Ross

Bob Phillips
12-14-2010, 10:15 AM
Off the top



Sub MatchDate()
Dim wb As Workbook
Dim MatchRow As Long

Set wb = Workbooks.Open("C:\somedirectory\somefile.xls")
On Error Resume Next
MatchRow = Application.Match(CLng(Date), wb.Worksheets(1).Columns(2), 0)
If MatchRow > 0 Then

MsgBox wb.Worksheets(1).Cells(MatchRow, "A")
End If

End Sub

rossmiddleto
12-16-2010, 06:05 AM
Thanks, Works perfectly!