Values through match function

[grkchakri]

Hi All

User inputs a date and we will add one day to it as a result but if the result date is in particular range(lets say from a1:10) it should add one day from that day in the range. I am using a match function to check if the resultant dates are in a range.


I am using dateserial to add dates . If say user input 12/09/2013 we will get 13/09/2013. If the same
13/9/2013 is in that holiday list (in that range i meant) it should add 1 day to 13/09/2013 so that result wil be 14/09/2013

The following is the code.

Please Login or Register  to view this content.

many thanx

[AlphaFrog]

This web page may help:
A Better WORKDAY Function

[AlphaFrog]

This prompts for a date entry, and uses the Workday2 function from the link above to add one day.
Assumes no weekend and the holidays listed on Sheet1 A1:A10

Please Login or Register  to view this content.

[grkchakri]

hanx alpha. But that looks like complicated and i am going through it , However in the meanwhile i just wanted to check if was able to ask my question and get across the point that i am looking for,

* I want to add one day to the one user inputs

* Even if it is a sunday /saturday it is ok with me

* If the range(holiday list ) has dates for example
06-12-2013
12-12-2013

* Basically whenever the result is in the holiday list it will should show the subsequent day

Will the workday2 function will remove all the days in the list at one time
or whenever the result is in the holiday list?

many thanx

[AlphaFrog]

hanx alpha. But that looks like complicated and i am going through it , However in the meanwhile i just wanted to check if was able to ask my question and get across the point that i am looking for,

* I want to add one day to the one user inputs

* Even if it is a sunday /saturday it is ok with me

* If the range(holiday list ) has dates for example
06-12-2013
12-12-2013

* Basically whenever the result is in the holiday list it will should show the subsequent day

Will the workday2 function will remove all the days in the list at one time
or whenever the result is in the holiday list?

many thanx

I understood the question and that's what I had attempted to answer.

What if you had two or more consecutive holidays dates in your holiday list? You would need to do more than just test for one match of the holidays.

The solution I suggested may be slight overkill, but it should do what you want. All you have to do is copy the Workday2 function from the link and the code I provided. That's it.

If you don't have to consider weekends, here's a more simple version. It doesn't use the Workday2 function.

Please Login or Register  to view this content.

[grkchakri]

Sorry to open this post again. Since the match function turns out be a little unmatched to my knowledge. I want to learn a bit more rather than
being ignorant

In the above code i just want to understand why isnumeric was used at the do while stage. My point is we are trying match with a date in range which is/should already be in date format.

I have tried to write one more example where it simply try to match with the date value which the user entered in the inputbox and tries to match
the value in a range. But to my surpise it throws a TYPE MIS MATCH ERROR: (RUNTIME ERROR 13
The code is as below

Please Login or Register  to view this content.

[grkchakri]

SAME AS ABOVE, Previously i posted it with the marker solved. Just edited that option to open the question
in the forum.

many thanx

[AlphaFrog]

ISNUMBER returns TRUE if the Match function returns a number (match found) or it returns FALSE if the Match function errors (no match found).

Your code is testing if z equals the result from the match function.
If z = Application.WorksheetFunction.Match(CLng(x), CRAN, 0) Then

They will never match because z doesn't equal anything. It's empty. Your code doesn't have a syntax problem. It has a logic problem.

[grkchakri]

Thank you alpha for that insight i was able to modify the code as below and got the match function working for me

Please Login or Register  to view this content.