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
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