application.match() doesn't need the "on error resume next" line.

application.worksheetfunction.match() does.

default105 wrote:

I found the problem. It seems this is tied to excel with the
Application.match and a date in a cell. You have to use the following
res = Application.Match(CLng(.Cells(iRow, iCol).Value), PBlackburnVacRng, 0)
Clng was all it needed to work however it will differently error so you have
to resume next so it will work without starting the debugger when it reaches
a range that is not in long format. Thanks for a point in the right
direction. The code cycles much faster.
--
Pete Blackburn - words to live by:
"Don''t ever let somebody tell you. You can''t do something.You got a
dream,You gotta protect it." Edited Quote from the Pursuit Of Happiness

"Dave Peterson" wrote:

Did you change the way res was declared.

It was declared:
dim res as variant
for a reason. That way if there is no match, you can check it with
iserror(res).

I don't think the code suggested is gonna work very well.

I think I'd try to drive the range to check based on the name of the user (and
the name of the sheet).

You may be able to use:

dim YearCol as long
with worksheets(thecellthatcontainsthename.value)
yearcol = year(cellwithdate.value) - 2009 + .range("W1").column
Set myListRng = .Range(.cells(3,yearcol), .Cells(.Rows.Count,
yearcol).End(xlUp))
end with

But the cell with the name has to match the worksheet name exactly.
Tom Smith
won't match
Tom Smith



default105 wrote:

No it does not work error 2042 for res, no the dates are always different. I
could not find anything in the help file but it looks like this is set up to
set the last row with the one that has a v in it. I am probably wrong but
that is why I am asking. That is why I just used a specified range that is
longer then anyone has in days available in vacation days. I would love to
programmically see what cells have values in column V but how do you set it
to look at Not empty.

Set myListRng = .Range("V3", .Cells(.Rows.Count, "V").End(xlUp))

Ok, this will be a long explanation so please bear with me. Workbook
worksheets are as follows. Sheet1 name 4 Rotating, Sheet2 name 5 Rotating,
Sheet3 name 4 rot 64wk view, sheet4 - sheet14 are personalized to one
individual employee only, sheet4 pete, sheet5 tom, etc... sheet15 name ISO
Week, sheet16 book scheduling (a report basically) Sheet17 Holidays.
Sheet4 -sheet14 are identical except for one cell, it defines an offset from
sheet 1 which is the current people in the department. It was setup like
this so if on change had to be made to many formula you can just copy and
paste to the other sheets and just adjust the one cell value.
That being said v3:34 is for the employee on sheet4 for the year 2009,
w3:w34 is for that employee for the year 2010, This worksheet is set up to
the year 2025. On sheet15 I use a select case on the worksheet change to
decide what to run and I clearcontents the range of cells on sheets16
everytime the year on sheet15 is changed This is the sheet (sheet15, e1) that
drives the entire workbook. When you enter in the years in e1 it calculates
all the weeks and days for that year on sheet15. sheet1 is static and has
the employees shift schedules, startup days(special days designated by color)
project schedules(designated by color) Every year is laid out until the year
2025. Sheet4 -sheet14 use formulas and one udf to populate a range of cells
identify shift and special conditions again by week(why you ask, I wondered
the same but some employees wanted to have a personal printout for easy
reference) So I utilized this to use to store vacation schedules on these
sheets.
Sheet16 is just a report with 53 printable pages for a hard copy book for
the department. Which I want to programmically populate.

My goal is to have it to all be as mistake proof as possible. The code I
was using and what I submitted was a snippet, it is still being built up be I
had a working structure however I just did not seem proper or efficient,
which you so kindly pointed out. I need one working smoothly before bitting
of the hole thing.

"Dave Peterson" wrote:

No thanks to the workbook.

The code I suggested didn't look for V's. It tried to match the value in
sheet16 to whatever was in sheet4 column V.

But I don't see how matching up a date to a list of dates would apply to every
name in that list. Does everyone take the same vacation days at your
business????

And it looks like your code does that offset by one row that you want. Doesn't
it work?

--

Dave Peterson


--

Dave Peterson