Announcement

Collapse
No announcement yet.

Selection Change Event, Set New Dynamic Ranges and Loop

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Selection Change Event, Set New Dynamic Ranges and Loop



    I have a worksheet selection change event that sets the values in column A as such:
    Code:
    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[7]=R[-1]C[7],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 !

    Code:
    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
                     ' 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
                End If
        Next t
        Exit Sub
    errHandler:
     MsgBox "Error " & Err.Number & " - " & Err.Description
     
     End Sub
    Last edited by AKAUFMAN1; 3 days ago.
Working...
X