Vlookup with VBA

Hi All,

I'm trying to find out a way to solve this problem:

I have an external password-protect xls file in which there is the second worksheet that contains a list of elements like the following one:

Column A Column B
10-Sep-2007 10am-19am
11-Sep-2007 10am-19am
12-Sep-2007 09am-18-am

I need to locate a specific cell in the Column B depending on the actual day and paste the content into my Excel Sheet.

I was able to open the external file, but I was only able to retrieve the content of a specified cell.

This is the code I'm using:

 
Sub GetDataFromClosedWorkbook()
Dim wb As Workbook
Dim myNumberFormat As String
    myNumberFormat = today
    Application.ScreenUpdating = False ' turn off the screen updating
    Set wb = Workbooks.Open("N:\Calendar.xls", True, True)
    ' open the source workbook, read only
    With ThisWorkbook.Worksheets(2)
        ' read data from the source workbook
        Set RNG = wb.Worksheets(2).Range("C13:AM1000")
        .Range("B4").Value = wb.Worksheets(2).VLookup(today(), RNG, 2, True)
        ' trying to retrieve the value
        .Range("A22").Formula = wb.Worksheets(2).Range("D6").Formula
        'this works
    End With
    wb.Close False ' close the source workbook without saving any changes
    Set wb = Nothing ' free memory
    Application.ScreenUpdating = True ' turn on the screen updating
End Sub

Anyone can suggest me how to correct that Vlookup function?????

Any help will be really appreciated!!!

Thank you,
Steve

I wouldn't use VLOOKUP, but use the .Find method. I'm not sure exactly what you want to do, attach an example workbook.

I can help you better if you attach a workbook example.