Hi sathishsusa
This is a very complicated requirement.
You are asking for a complete solution _.... :-(
_... Generally in such a Help Forum as Excel Fox, we try to help you with particular problems, and not do an entire project for you.
But your latest attempt at explaining is good. Nice explanation of your requirement. Well done _.. :-) It was a nice clear and easy to follow File which you uploaded :-). Very Good :-)
That made it a lot easier for me
_.. So, I can make a start for you, based only loosely on your proposed solution.
Here is a brief description of a code i habe done for you:
I do not know anything about check boxes. :-(
Rem 2) So I look at TRUE or FALSE value in Range("AS8:AS11")
‘2a) take in checked names data into Array
‘2b) from ‘2a) produce
'2b)(i) complete initial string of overlapping names
and
'2b(ii)
( '2b)(i) add all names to initial string of overlapping names )
and
Build Array of all checked as True names, arrChkTrueEmp()
Rem 3) Main Input data
'3a) I use test data Range "B21:E26" – you will need to change as appropriate.
'3b) use 4 arrays for each data column
Rem 4) Main Outer loop # =====Cnt =========
I take each name form column B data
‘4b) Only if I have a name in the Array of all checked as True names, arrChkTrueEmp(), do I consider the current Name , ( Else do nothing and go on to next name in column B )
____If I have a name in the Array of all checked as True names, Then
________ The Inner Loop ‘ -----CntIn-----------
Rem 5) goes “down” all names in column B.
_________ If a name is also in the Array of all checked as True names, arrChkTrueEmp(), the main Criteria check is done
The criteria is either:_..
_.. The name under consideration in the outer loop has a From date within the From and To dates of name under consideration in second inner loop
________ (arrFrm(Cnt, 1) >= arrFrm(CntIn, 1) And arrFrm(Cnt, 1) <= arrTo(CntIn, 1))
Or
_.. The name under consideration in the outer loop has a To date within the From and To dates of name under consideration in second inner loop
________ (arrTo(Cnt, 1) >= arrFrm(CntIn, 1) And arrTo(Cnt, 1) <= arrTo(CntIn, 1))
_____________ If the criteria is met I put a string of "Overlap" in the Array , arrOvrLpDts(CntIn, 1). This is like your idea ...” …Create a boolean variable to hold the results of the test (eg myFlag)...”
Rem 6) Loop through arrOvrLpDts() Array to add to report string and mark cell
Rem 7) Message Box output of report string
Bookmarks