I have been playing with it and the problem is in my array resultArray(i).
When instead of the line resultArray(i) = Sheets("DeSL_CP").Range("P" & j).Value, I use .Range("M" & i).Value = Sheets("DeSL_CP").Range("P" & j).Value, it works, but takes longer.
Why is resultarray(i) returning all zeros?
Original post:
I have two sheets: Summary has a productid in col A and a field that marks the product as unlicensed or licensed in AK. DeSL_CP has multiple lines for each productId (in col B).
I need to find the line with activity code (Col K) AA0001 for unlicensed product and return the date for baseline end (col P). Then I need to find the code A0003 for the remaining products and return that lines baseline end. Baseline N should be in col M of the summary sheet.
My code is not throwing errors. It populates all of column M with 1/0/1900.
Sheets("Summary").Select
Dim lastRow As Long, lastRow1 As Long
lastRow = Range("A" & Rows.Count).End(xlUp).Row
lastRow1 = Sheets("DeSL_CP").Range("A" & Rows.Count).End(xlUp).Row
lastRow1 = lastRow1 - 1
Dim BaselineEnd As Variant, ActivityCode As Variant, ProductIDDeSL As Variant, _
Licensed As Variant, ProductIDSumm As Variant
BaselineEnd = ThisWorkbook.Worksheets("DeSL_CP").Range("P2:P" & lastRow1).Value
ActivityCode = ThisWorkbook.Worksheets("DeSL_CP").Range("K2:K" & lastRow1).Value
ProductIDDeSL = ThisWorkbook.Worksheets("DeSL_CP").Range("B2:B" & lastRow1).Value
Licensed = ThisWorkbook.Worksheets("Summary").Range("AK7:AK" & lastRow).Value
ProductIDSumm = ThisWorkbook.Worksheets("Summary").Range("A7:A" & lastRow).Value
Dim resultArray() As Date
ReDim resultArray(7 To lastRow)
Dim i As Long, j As Long
With ThisWorkbook.Worksheets("Summary")
For i = 7 To UBound(ProductIDSumm)
For j = 2 To UBound(ProductIDDeSL)
If ProductIDSumm(i, 1) = ProductIDDeSL(j, 1) Then
If Licensed(i, 1) = "Unlicensed" Then
If ActivityCode(j, 1) = "AA0001" Then
resultArray(i) = Sheets("DeSL_CP").Range("P" & j).Value
Exit For
End If
Else
If ActivityCode(j, 1) = "A0003" Then
resultArray(i) = Sheets("DeSL_CP").Range("P" & j).Value
Exit For
End If
End If
End If
Next j
Next i
.Range("M7").Resize(lastRow - 7 + 1, 1).Value = resultArray
End With
There are times it is blank, but many times not. I hid a lot of data to focus on the columns that matter. It is in century month - does that matter?


Sheets("DeSL_CP").Range("P" & i).Valuecould be blank, or is the wrong range reference.resultarrayit is emptyresultArray(i)will equal the last result of thejFOR/NEXTloop and all earlier values will be overwritten. Is that what you want?