Formula won't work past a certain date

[mandii01]

Hi All,

Please help I have no idea of what is going wrong.

I have the following formula which is working fine up to and including week 26 but then when I put the next end week date (For week 27) in the formula isn't working - won't bring over the text in the specified cell range?? If I go back to week 26 still works like a dream!

I have the following;

Sheet 1: Date formula in cell G4 of =WEEKNUM(G3,2) this refers to the cell G3 where I manually enter the week ending date.

This formula is working still returning the appropriate end product after week 26

I then in cell A69 have the formula =LOOKUPCONCATENATE(G4,A68) where A68 = "Summary of Incidents"

This formula has worked perfectly up to the end of week 26 and now it won't work?

For the formula in cell A69 I have a VBA module that is as follows;

Public Function LookUpConcatenate(InputDate As Range, Header As Range) As String

Dim wsData As Worksheet
Dim TempText As String
Dim DatePointer As Integer, ColumnHeader As Integer, Counter As Integer, ControlCounter As Integer, Instances As Integer

Set wsData = Worksheets("DATASHEET")
DatePointer = WorksheetFunction.Match(InputDate, wsData.Range("A:A"), 0)
ColumnHeader = WorksheetFunction.Match(Header, wsData.Range("2:2"), 0)
Instances = WorksheetFunction.CountIf(wsData.Range("A:A"), DatePointer)
ControlCounter = (DatePointer + Instances) - 1

For Counter = DatePointer To ControlCounter
TempText = wsData.Cells(Counter, ColumnHeader).Value
If TempText <> "" Then
If LookUpConcatenate <> "" Then
LookUpConcatenate = LookUpConcatenate & Chr(12) & TempText
Else
LookUpConcatenate = TempText
End If
End If
Next Counter

End Function

Please help!!

[Bob Phillips]

There is more data involved here, Datasheet. Can you post the work book, it is hard to set it all up?

[mandii01]

Hi

here is a copy of the workbook as requested.

Thanks

[Bob Phillips]

I think you were wrongly calculating Instances

Public Function LookUpConcatenate(InputDate As Range, Header As Range) As String
Dim wsData As Worksheet
Dim TempText As String
Dim DatePointer As Integer, ColumnHeader As Integer, Counter As Integer, ControlCounter As Integer, Instances As Integer

    Set wsData = Worksheets("DATASHEET")
    DatePointer = WorksheetFunction.Match(InputDate, wsData.Range("A:A"), 0)
    ColumnHeader = WorksheetFunction.Match(Header, wsData.Range("2:2"), 0)
    Instances = WorksheetFunction.CountIf(wsData.Range("A:A"), InputDate)
    ControlCounter = (DatePointer + Instances) - 1
    
    For Counter = DatePointer To ControlCounter
        TempText = wsData.Cells(Counter, ColumnHeader).Value
        If TempText <> "" Then
            If LookUpConcatenate <> "" Then
            LookUpConcatenate = LookUpConcatenate & Chr(12) & TempText
            Else
            LookUpConcatenate = TempText
            End If
        End If
    Next Counter
End Function

But you still have a problem. G4 is a week number, but that weeknumber can apply to 2014 and 2015 and so on, so Instances counts more that the weeknum for the given date.

[mandii01]

Thank you !! Brilliant it works now !