I have a worksheet selection change event that sets the values in column A as such:
Sheets("Investigation Grid").Range("A2").Value = 1 LastRow2 = Range("B" & Rows.Count).End(xlUp).Row Sheets("Investigation Grid").Range("A3:A" & LastRow2).FormulaR1C1 = "=IF(RC=R[-1]C,R[-1]C,R[-1]C+1)"
So if the value in column H matches the value in column H for the row above it, then the index number in column A is the same as the index number in column A in the row above, if the value in column H does not match the value in column H in the row above, then the index number in column A is +1 of the index number in row above it.
This is a way for me to group rows that belong to the same case number found in column H.
I am trying to add to my worksheet selection change event to look at each index number as its own range… then look at the values in column 22 of that index number range.
For instance, there are 4 rows that have index number 78.
Within those 4 rows, I want to look at column 22 and if any of the values in the 4 rows in column 22 say “Substantiated” or “Indicated” then all 4 rows in column 41 should return “Substantiated” or “Indicated” based on whichever value is found in column 22. If none of the column 22 values are “Substantiated” or “Indicated” then the values in Column 41 should be the same as the values in column 22.
I have updated this to include code i started working on. The ElseIf section is where i need assistance! Thank you !
Sub DeterminationType() On Error GoTo errHandler Dim lastRow As Long, k As Long, t As Long, xRange As Range lastRow = Cells(Rows.Count, "A").End(xlUp).Row Set xRange = Range("A1:A" & lastRow) For t = 1 To lastRow k = Application.WorksheetFunction.CountIf(xRange, Cells(t, 1)) t = t + k - 1 If k = 1 Then ' if index number only exists 1 time Cells(t, 41).Value = Cells(t, 22).Value ' then put the value from column 22 in column 41 ElseIf k > 1 Then ' if index number exists more than 1 time [COLOR=#FF0000] ' need to loop through the values in column 22 and evaluate if any of the k values are "Substantiated" or "Indicated" then _ cells in column 41 need to be "Substantiated" or "Indicated" based on whichever of the two values are present _ If "Substantiated" or "Indicated" is not found in the k values then cells in column 41 should = values in column 22[/COLOR] End If Next t Exit Sub errHandler: MsgBox "Error " & Err.Number & " - " & Err.Description End Sub