Ok, so you want to check for 3 merged ranges, whenever there is Time value.
Let's go step by step.
First you need to change the range used to fill chkArr.
Since time is stored in J:K merged column...
chkArr = Range("J10:P" & Cells(Rows.Count, "J").End(xlUp).Row).Value
Next, you need to adjust each column index (array) used to check against twpRng, prgRng, actRng.
If statement does not change since you are still checking if first column contains value (Time).
If Len(chkArr(i, 1)) > 0 Then
for twpRng...
lVal = Application.Match(chkArr(i, 3), twpRng, 0)
for prgRng...
nVal = Application.Match(chkArr(i, 5), prgRng, 0)
Basically, each check's column index is increased by 2 (since J & K are added to column range).
So code becomes...
Sub Demo()
Dim chkArr, lVal, nVal, pVal
Dim twpRng As Range, prgRng As Range, actRng As Range
Dim resStr As String
chkArr = Range("J10:P" & Cells(Rows.Count, "J").End(xlUp).Row).Value
With Sheets("Dashboard")
Set twpRng = .Range("AW7:AW" & .Cells(Rows.Count, "AW").End(xlUp).Row)
Set prgRng = .Range("AY7:AY" & .Cells(Rows.Count, "AY").End(xlUp).Row)
Set actRng = .Range("BA7:BA" & .Cells(Rows.Count, "BA").End(xlUp).Row)
End With
For i = 1 To UBound(chkArr)
If Len(chkArr(i, 1)) > 0 Then
lVal = Application.Match(chkArr(i, 3), twpRng, 0)
If IsError(lVal) Then
resStr = IIf(Len(resStr) = 0, "L" & i + 9, resStr & ", L" & i + 9)
End If
nVal = Application.Match(chkArr(i, 5), prgRng, 0)
If IsError(nVal) Then
resStr = IIf(Len(resStr) = 0, "N" & i + 9, resStr & ", N" & i + 9)
End If
pVal = Application.Match(chkArr(i, 7), actRng, 0)
If IsError(pVal) Then
resStr = IIf(Len(resStr) = 0, "P" & i + 9, resStr & ", P" & i + 9)
End If
End If
Next
[V5].Value = resStr
End Sub
Bookmarks