Home |
Search |
Today's Posts |
#13
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Find loop doesn't loop | Excel Discussion (Misc queries) | |||
For Each Loop | Excel Discussion (Misc queries) | |||
How to loop | Excel Discussion (Misc queries) | |||
if & Loop | Excel Discussion (Misc queries) | |||
Help with Do...Loop | Excel Discussion (Misc queries) |