Vlookup with VBA

**tsl** · 09-07-2007, 11:01 AM

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:

Please Login or Register  to view this content.

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

Any help will be really appreciated!!!

Thank you,
Steve

**royUK** · 09-07-2007, 11:44 AM

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

**tsl** · 09-08-2007, 03:01 AM

Thank you RoyUK for your reply!

I'll try to better explain what I'm trying to do with a couple of screenshots:

Let's say that I want to display in the cell B4 (attachment "file1.jpg") the exact copy of the content of the cells on the B column on the second attachment (file2.jpg - external file) depending on the day.

So, if today were 17/09/2007 I would like to have in B4 the value 10-7pm

Possibly not important, but please note that the date's values are in the format d/m/yyyy

What I'm receiving is just #N/A

Do you think that .find is the solution?

Thank you very much!
Steve

**royUK** · 09-08-2007, 01:17 PM

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

**tsl** · 09-10-2007, 03:41 AM

Hi RoyUK!

You are right, I'm attaching two examples (basically these are the worksheets I'm using, just without most of the data to make them smaller)...

Basically, the file "shifts.xls" will have to find and read info from calendar.xls using vlookup...

Calendar.xls is a password protected file, and the pwd is "test"...

RoyUK, thank you for your help!!!
Steve

**tsl** · 09-10-2007, 03:44 AM

Ooops...

here you have the attachments...

:-)

**rylo** · 09-10-2007, 04:52 AM

Hi

See if this helps

Please Login or Register  to view this content.

rylo

**tsl** · 09-18-2007, 08:53 AM

Hi RoyUK,

I've tested your code, but it will report "subscript out of range"...

I was thinking that maybe is related to the cell format of the calendar.xls file, and I've tried to change it with no success at all...

Do you have any other idea???

Anyway... many thanks for your help and your time!!!

Stefano

Vlookup with VBA

LinkBack

Thread Tools

Rate This Thread

Display

Vlookup with VBA

Example

'Real' Example

Attachments

No way...

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions