VLOOKUP through code?

[Pozzi]

Hi,

I have two workbooks, the first is used to run a report, the other I want to use to archive the report just run.

The report allows the user to select a week commencing date, and they click run and the report is generated. Once generated I want to copy the report into the other workbook, this bit I think I should be fine with (he hopes).

In the archive workbook is a sheet named 'Calendar' and it contains a range showing the week commencing date and applicable week number for the year.

Example: -

Week Commencing	Week No
09/05/2005	1
16/05/2005	2
23/05/2005	3
30/05/2005	4
06/06/2005	5
13/06/2005	6

Through the code in the 'Report' workbook I want to take the previously supplied date e.g. 06/06/2005 and find out the week number (5).

Can I use VLOOKUP through the code?

If not how could I look up the week number?

I hope I've explained what I'm want to do clearly.

Regards

[DKenny]

Check out Application.WorksheetFunction in the help files....

[Webtest]

Pozzi ...

Is the Week Date/Week # table pre-loaded for the entire duration, or does it get updated periodically. The reason I ask is because VLOOKUP references an array of cells, and it is a little tricky if the array keeps changing size.

With this consideration, YES, you can easily use VLOOKUP to do what you want to do.

You need to consider if you will be looking for an exact date, or if you are allowing a date between the lookup table entries. There is a parameter in the VLOOKUP function that controls this.

The easiest way to set up the code is to record a macro and do an Insert > Function and fill in the blanks in the wizard.

Let us know if this solves your problem.

[DKenny]

The reason I ask is because VLOOKUP references an array of cells, and it is a little tricky if the array keeps changing size.

If the array size is changing you can always use a "Dynamic Named Range" that will always reflect the full array size.
Range names in Excel are really formulas that refer to a range and you can write these in such a way that the dynamically expand as your dataset increases.

In the Inset\Name\Define dialogbox
Basic Range Name Formula: =Calendar!$A$1:$B$2
Dynamic Range Name Formula: =OFFSET('Calendar'!$A$1,0,0,COUNTA('Calendar'!$A:$A),COUNTA('Calendar'!$1:$1))


The dynamic range name creates a range using the offset function, offsetting from A1 to the cell that is CountA(A:A) down and CountA(1:1) across. (Remember that the range reference is just a formula and you can include any excel function in it).

As you add rows to your dataset, the value of CountA(A:A) increases, so your range grows downward.
As you add additional Column headers, the value of CountA(1:1) increases, so your range grows horizontially.

I use these all the time for Lookup Tables and they can make life much easier...

Note: this approach really only woks if you have a single lookup range per worksheet.