Quick table lookup

[rossmiddleto]

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]

Off the top

[vba]

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
[/vba]

[rossmiddleto]

Thanks, Works perfectly!