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