one last question
i have got it working for the Jul colum - thanks to you guys
Output is etc::
,3,4,5, , ,8, , , ,12, , , , , ,18, , , ,22, ,24, , ,27,28, ,30,
but when i copied and pasted into next cell to make a Aug column and changed the formula to read
=concatmth(D5:DU5,"Aug",TRUE)
it came back as a #value error
what am i doing please
__________________________
Function ConcatMth(rng As Range, item As String, Optional dte = False)
Application.Volatile
For Each ce In rng
If ce <> "" And Sheets(rng.Parent.Name).Cells(2, ce.Column) = item Then
If dte Then
holder = holder & Format(ce, "d") & ","
Else
holder = holder & ce & ""
End If
End If
Next ce
ConcatMth = Left(holder, Len(holder) - 1)
End Function
Function ConcatDay(rng As Range, item As String, Optional dte = False)
Application.Volatile
For Each ce In rng
If ce <> "" And Sheets(rng.Parent.Name).Cells(3, ce.Column) = item Then
If dte Then
holder = holder & Format(ce, "dd/mm") & ","
Else
holder = holder & ce & ","
End If
End If
Next ce
ConcatDay = Left(holder, Len(holder) - 1)
End Function
i have got it working for the Jul colum - thanks to you guys
Output is etc::
,3,4,5, , ,8, , , ,12, , , , , ,18, , , ,22, ,24, , ,27,28, ,30,
but when i copied and pasted into next cell to make a Aug column and changed the formula to read
=concatmth(D5:DU5,"Aug",TRUE)
it came back as a #value error
what am i doing please
__________________________
Function ConcatMth(rng As Range, item As String, Optional dte = False)
Application.Volatile
For Each ce In rng
If ce <> "" And Sheets(rng.Parent.Name).Cells(2, ce.Column) = item Then
If dte Then
holder = holder & Format(ce, "d") & ","
Else
holder = holder & ce & ""
End If
End If
Next ce
ConcatMth = Left(holder, Len(holder) - 1)
End Function
Function ConcatDay(rng As Range, item As String, Optional dte = False)
Application.Volatile
For Each ce In rng
If ce <> "" And Sheets(rng.Parent.Name).Cells(3, ce.Column) = item Then
If dte Then
holder = holder & Format(ce, "dd/mm") & ","
Else
holder = holder & ce & ","
End If
End If
Next ce
ConcatDay = Left(holder, Len(holder) - 1)
End Function