Object Required error on Application.Match function.

My apologies I had been fighting with this about 4 hours straight with no headway yesterday, I neglected to explain anything...

txt_Start & txt_End are text boxes on the form that calls this sub, user is requested to enter as dates (US format - m/d/yyyy). I can't require a specific format obviously... I would love to use a date picker but MS pulled that out of Excel 07.

What this command is doing is taking a range of dates the user enters (txt_Start & txt_End), and an option they choose (Vacation, Leave of Absence, Schedule Change) for a given employee and adjusting their schedule between the days. All 365 days of the year are on row 2 (there's a few headings before that for name and id number, etc...) - Where I had the 'do work' comment is where the actual editing for each cell between dstart and dend is done. I haven't gotten that part tested because I can't get past the Match to find the column references for the start and end points...


I did try changing the code as you mentioned, teylyn, and it continue to give me the same error.

Dumb question, since I never had to use "set" when I program in true VB.net, what are the differences between using set and not using set? If I remove the "set" from the app.match lines I get Object variable or With block variable not set but if I add "set" to other spots, like the "Format$" lines I then get the Object Required errors at that point instead.

I did change the Format lines to use the Datevalue() function, this still shows the date without quotes when I hover over in debug mode, same as the format() function showed, whereas the original textbox values did show the quotes as strings.

I still receive the object required error... Also, when I try to remove the "set" from the match lines I get the Object Variable error I mentioned above.

I've attached a copy of the file, stripped to only three schedules, and with no identifiable data, just so you can see what is going on. This workbook is used as not only a master schedule for the employees, but also to keep "backup" (not the official) records of vacation usage, extended leave, and call-outs for employees. A lot of people edit this, and recently we've seen a rash of people editing things incorrectly (we used to just manually change the data) so we decided to try and make it more fool-resistant (not quite foolproof) that may include password-protecting sheets in the future when the scripting is complete and working right.

Double-clicking on a cell in the main schedule area will show a window to select call-outs and vacation. Pressing the extra button on the bottom or double clicking a name will show the schedule adjustment window where extended vacations, leaves of absence (extended approved leave for medical reasons, etc) and the actual schedule can be adjusted for that person. It is on this schedule form that this issue is occurring, before it even begins to edit the data. It fails at trying to decide which cells need to be changed.


I should warn you that while I have been programming VB.Net and VBA for a couple years now, I tend to work more in the immediate logical sense, as in I know what I want done and figure it out as I go, so the code tends to be very inconveniently laid out for anyone experienced enough to take a truly programmatical approach. I apologize and if you have any pointers on things here that could be consolodated or improved let me know.

Again, THANK YOU for all your help, and when I get this straightened out, your reputations will not go unforgotten!

No dice - now I get a 1004 error: Application-defined or object-defined error. Debugging highlights the set cstart line you gave me. I did comment the old lines out.

The msgbox gives me a type mismatch, regardless of whether I display only sdcon or dates.

snb, both of those still result in object required errors if I leave set in, and the object variable error it I take set out.

Is there some other way I should be trying to do this? Is match maybe not the best? (Now that you can see the file and what it is actually trying to act on?

snb, thanks! It finally worked! I knew Excel was super picky, but I assumed it would at least take the sheet index instead of the name... Obviously makes it a little tougher when we get to 2012, but maybe I'll have something else going by then, who knows.

Thanks all! Reps added to all around!