PDA

View Full Version : Cell Split or add Rows Excel VBA Leave Plan



farmerkzhel
01-16-2016, 04:57 AM
I am having leave plan which shows name in calendar depends on start date and end date
looks like this

15191

using this function

VB:
Function vac(dt As Date, rngnme As Range, rngstrt As Range, rngend As Range) As String Application.Volatile
Dim i&
Dim temp As String
temp = ""


For i = 1 To rngnme.Rows.Count
If rngnme.Cells(i, 1) <> "" Then
If rngstrt.Cells(i, 1) <= dt And rngend.Cells(i, 1) >= dt Then
temp = temp & rngnme.Cells(i, 1) & ","
End If
End If
Next i


If temp = "" Then
vac = CVErr(xlErrNA)
ElseIf Len(temp) - Len(Replace(temp, ",", "")) > 4 Then
vac = "> 4"
Else
vac = Left(temp, Len(temp) - 1)
End If
If temp = "" Then
vac = CVErr(xlErrNA)
ElseIf Len(temp) - Len(Replace(temp, ",", "")) = 0 Then
vac = "= 0"
Else
vac = Left(temp, Len(temp) - 1)
End If
End Function

and formula is

=IFERROR(vac(DATE($D$1,ROWS($A$3:$A3),B$2),$A$60:$A$100,$B$60:$B$100,$C$60: $C$100),"")

I want to add rows if more than one name is in cell i want it to be look like something like this attached image below

15192

What can be the possible solution please help

p45cal
01-17-2016, 12:23 PM
cross post:
http://www.mrexcel.com/forum/excel-questions/914773-adding-rows-split-cells-excel-visual-basic-applications-leave-plan.html

farmerkzhel
01-17-2016, 01:41 PM
Yes I did post that on both forums so that i can increase the viewership to get help

p45cal
01-17-2016, 01:53 PM
have a read of http://www.excelguru.ca/content.php?184,
perhaps the rules too.

SamT
01-17-2016, 05:28 PM
@ P45cal,
I'll just wait another day with my solution to see how the OP responds to you.