HI Vasant
Sorry for the late reply & thanks you very much for the effort,.
Like to tell you that everything is perfect except one change need to do is
that when ever Emp taken leaves continuously like say (15th June to
23rdJune ) so when I click for output it should give me only one entry
but as
per your sheet it gives me 6 entries)
In Short:
1) Whenever Emp takes continues leaves (like Mon to Thu , Tue to wed ,
Mon to Fri ) I want only one entry And *(need to modify code for this)*
2) whenever Emp absent or taken leave on fri & mon then From & to Date
will be from Fri to Mon *this is comes properly in output. (no need to
change the code)*
3) And if Emp taken leaves or diff dates then from & to dates will be
same like (13th June to 13th June) *this is comes properly in output. (no
need to change the code)*
Rest is fine
Request you to kindly make the small change & resent the file
Thanks in advance
On Sun, Jun 26, 2011 at 11:32 PM, Vasant <vasant...@gmail.com> wrote:
> Hi Maulik
>
> Pls find the file attached
>
> I have created 3 sheets, in line with your original file. The code reads
> the data from sheet1 and populates sheet2 and sheet3 as per your
> requirements
>
> i encourage you to go through the code and understand it, so that you will
> be able to make modifications as per your original file.
>
> Also i would suggest that you use drop down list in sheet1 in the input
> area in order to avoid typo errors. Refer Y5 in sheet1.
>
> Hope this helps ! :)
>
>
> 'Code used
>
> Sub GetTotals()
> Dim DataWkSht As Worksheet, Lrow As Integer, LCol As Integer
> Dim StartRow1 As Integer, StartRow2 As Integer
> Dim LvDtls As Worksheet
> Dim LvSummary As Worksheet
> Set LvDtls = ThisWorkbook.Worksheets("Sheet2")
> Set LvSummary = ThisWorkbook.Worksheets("Sheet3")
> Set DataWkSht = ThisWorkbook.Worksheets("Sheet1")
> Lrow = DataWkSht.Range("A65536").End(xlUp).Row
> LCol = DataWkSht.Range("IV2").End(xlToLeft).Column
> For cntr = 2 To Lrow
> LvDtls.Cells(cntr, 1) = DataWkSht.Cells(cntr, 1)
> LvDtls.Cells(cntr, 2) = DataWkSht.Cells(cntr, 2)
> LvDtls.Cells(cntr, 3) = DataWkSht.Cells(cntr, 3)
> For ColCntr = 4 To LCol
> If Trim(UCase(DataWkSht.Cells(cntr, ColCntr))) = "IL" Or
> Trim(UCase(DataWkSht.Cells(cntr, ColCntr))) = "NCNS" Then
> If Weekday(DataWkSht.Cells(1, ColCntr)) = 6 Then
> If Trim(UCase(DataWkSht.Cells(cntr, ColCntr + 3))) = "IL" Or
> Trim(UCase(DataWkSht.Cells(cntr, ColCntr + 3))) = "NCNS" Then
> LvDtls.Cells(cntr, 4) = LvDtls.Cells(cntr, 4) + 4
> LvDtls.Cells(cntr, LvDtls.Cells(cntr, 256).End(xlToLeft).Column +
> 1) = Format(DataWkSht.Cells(1, ColCntr), "dd-mmm-yyyy")
> LvDtls.Cells(cntr, LvDtls.Cells(cntr, 256).End(xlToLeft).Column +
> 1) = Format(DataWkSht.Cells(1, ColCntr + 1), "dd-mmm-yyyy")
> LvDtls.Cells(cntr, LvDtls.Cells(cntr, 256).End(xlToLeft).Column +
> 1) = Format(DataWkSht.Cells(1, ColCntr + 2), "dd-mmm-yyyy")
> LvDtls.Cells(cntr, LvDtls.Cells(cntr, 256).End(xlToLeft).Column +
> 1) = Format(DataWkSht.Cells(1, ColCntr + 3), "dd-mmm-yyyy")
>
> SumRow = LvSummary.Range("A65536").End(xlUp).Row + 1
> LvSummary.Cells(SumRow, 1) = DataWkSht.Cells(cntr, 1)
> LvSummary.Cells(SumRow, 2) = DataWkSht.Cells(cntr, 2)
> LvSummary.Cells(SumRow, 3) = DataWkSht.Cells(cntr, 3)
> LvSummary.Cells(SumRow, 4) = Format(DataWkSht.Cells(1, ColCntr),
> "dd-mmm-yyyy")
> LvSummary.Cells(SumRow, 5) = Format(DataWkSht.Cells(1, ColCntr +
> 3), "dd-mmm-yyyy")
> LvSummary.Cells(SumRow, 6) = 4
>
> ColCntr = ColCntr + 3
> Else
> LvDtls.Cells(cntr, 4) = LvDtls.Cells(cntr, 4) + 1
> LvDtls.Cells(cntr, LvDtls.Cells(cntr, 256).End(xlToLeft).Column +
> 1) = Format(DataWkSht.Cells(1, ColCntr), "dd-mmm-yyyy")
>
> SumRow = LvSummary.Range("A65536").End(xlUp).Row + 1
> LvSummary.Cells(SumRow, 1) = DataWkSht.Cells(cntr, 1)
> LvSummary.Cells(SumRow, 2) = DataWkSht.Cells(cntr, 2)
> LvSummary.Cells(SumRow, 3) = DataWkSht.Cells(cntr, 3)
> LvSummary.Cells(SumRow, 4) = Format(DataWkSht.Cells(1, ColCntr),
> "dd-mmm-yyyy")
> LvSummary.Cells(SumRow, 5) = Format(DataWkSht.Cells(1, ColCntr),
> "dd-mmm-yyyy")
> LvSummary.Cells(SumRow, 6) = 1
>
>
> End If
> Else
> LvDtls.Cells(cntr, 4) = LvDtls.Cells(cntr, 4) + 1
> LvDtls.Cells(cntr, LvDtls.Cells(cntr, 256).End(xlToLeft).Column + 1) =
> Format(DataWkSht.Cells(1, ColCntr), "dd-mmm-yyyy")
>
> SumRow = LvSummary.Range("A65536").End(xlUp).Row + 1
> LvSummary.Cells(SumRow, 1) = DataWkSht.Cells(cntr, 1)
> LvSummary.Cells(SumRow, 2) = DataWkSht.Cells(cntr, 2)
> LvSummary.Cells(SumRow, 3) = DataWkSht.Cells(cntr, 3)
> LvSummary.Cells(SumRow, 4) = Format(DataWkSht.Cells(1, ColCntr),
> "dd-mmm-yyyy")
> LvSummary.Cells(SumRow, 5) = Format(DataWkSht.Cells(1, ColCntr),
> "dd-mmm-yyyy")
> LvSummary.Cells(SumRow, 6) = 1
>
> End If
> End If
> Next ColCntr
> Next cntr
> End Sub
>
>
>
> On Sun, Jun 26, 2011 at 2:10 PM, maulik desai <mauliksde...@gmail.com>wrote:
>
>> Hi All,
>>
>> Awaitnig your reply
>>
>> Kindly revert with the solution.
>>
>> On Sun, Jun 19, 2011 at 5:42 PM, maulik desai
>> <mauliksde...@gmail.com>wrote:
>>
>>> Hi All,
>>>
>>> I am having 1 Excel sheet which contains 3 sheets
>>> In sheet1 main database i have to update in every month for Leave
>>> calculation
>>> In Sheet2 I have to find out the total leaves taken in that month with
>>> dates of "IL & "NCNS" Staus from Sheet 1 (details given in attached sheet)
>>> In sheet3 i have to update the candidates details as per his leave dates
>>> (format & details given in attached sheet)
>>> Request you to kindly provide the solution with the help of formulas &
>>> macro
>>>
>>> For more details kindly find the attached file.
>>>
>>> --
>>> Thanks & Regards,
>>> Maulik Desai
>>> 9967363926
>>>
>>> --
>>>
>>> ----------------------------------------------------------------------------------
>>> Some important links for excel users:
>>> 1. Follow us on TWITTER for tips tricks and links :
>>> http://twitter.com/exceldailytip
>>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>>
>>> To post to this group, send email to excel-macros@googlegroups.com
>>>
>>> <><><><><><><><><><><><><><><><><><><><><><>
>>> Like our page on facebook , Just follow below link
>>> http://www.facebook.com/discussexcel
>>>
>>
>>
>>
>> --
>> Thanks & Regards,
>> Maulik Desai
>> 9967363926
>>
>> --
>>
>> ----------------------------------------------------------------------------------
>> Some important links for excel users:
>> 1. Follow us on TWITTER for tips tricks and links :
>> http://twitter.com/exceldailytip
>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>
>> To post to this group, send email to excel-macros@googlegroups.com
>>
>> <><><><><><><><><><><><><><><><><><><><><><>
>> Like our page on facebook , Just follow below link
>> http://www.facebook.com/discussexcel
>>
>
>
>
> --
> Regards
>
> Vasant
>
> --
>
> ----------------------------------------------------------------------------------
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>
--
Thanks & Regards,
Maulik Desai
9967363926
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
