mandii01

07-06-2014, 10:56 PM

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

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