Worksheet_Change(ByVal Target As Range) to include offset and allow user to drag cell value to subsequent rows, and countif formula

[WildBurrow]

Two parts to this query:

• 1) I've written the following code to address each row within range (F15:F44), aka table "WellList[ERP Group ID Number]". It works, but it's rather ridiculous and repetitive. I'm looking for something more concise/elegant which will still allow the user to drag the value from, say F15 down to F44, without triggering an error.

'Reset Removed items if only one item in Group ID
    If Not Intersect(Target, Range("F15")) Is Nothing Then
        If Target.Value = ("Removed") Then 'Keeps the data/row but eliminates the values from further consideration (e.g next procedure)
        Range(Target, Target.Offset(0, 1)).Copy Destination:=Range("V13") 'Paste Columns F & G to Columns V & W
            If Range("X15").Value = "Suspend" Then 'Column X value supplied by COUNTIF formula
                Call MsgMustSuspend ' Tells User to follow a different procedure
                Range("F15").Value = "-" 'Resets Column F value
            ElseIf Range("X15").Value = "OK" Then
                Exit Sub
            End If
        End If
    End If

• 2) The even clunkier part of my worksheet is this; I'm using range ("V13") as a temporary holding cell for the Target.offset(0,1) value (Column G). The purpose is to compare the selected row's Group ID with the total number of rows with the same Group ID within range("F15:F44"). If the selected row's Group ID is listed only once, then a message box appears which tells the user to follow a different procedure. Below is a sample of the formulas. This works, but it requires thirty lines of formulas. Is there a better way to accomplish this either by A) incorporating it into Part 1 (above) or keeping it as a second and separate step?

	Column V	Column W	Column X
Row 13 (Pasted from above code)	"Removed" (value from column F)	Group ID Number (value from column G)	not used
Row 14 Headers for this COUNTIF function 'table'	Group ID	# of Wells	Formula
Rows 15:44 (relative row referencing)	=WellList[@[ERP Group ID Number2]]	=COUNTIF(WellList[ERP Group ID Number2],V15)	=IFERROR(IFS($W$13=V15,IF(W15>1,"OK","Suspend")),"")

Blanket Apology:
I work for an entity that will not allow me to download “XL2BB” nor join platforms such as Box or DropBox. I am hampered by using a vehicle with square wheels .

 

[WildBurrow]

I've solved my problem by updating the formulas and code.

First, I removed all the formulas in Columns V:X (ugh, 90 formulas - ridiculous!). I then wrote two new formulas; one counting the number if rows in the target Group ID, and another counting the number of rows within the target Group ID that was listed as 'Removed'.

	Column V	Column W
Row 13 (values pasted from code)	"Removed" (value from column F)	Group ID Number (value from column
Row 14	Group ID row count (label)	=COUNTIF(WellList[[#All],[ERP Group ID Number2]],$W$13)
Row 15	'Removed' row count (label)	=COUNTIFS(WellList[Well Status],"=Removed",WellList[ERP Group ID Number2],$W$13)

Second, I revised the code to look at the difference between cells "W14" and "W15" to trigger the needed message box.

On Error GoTo JoyfulExodus
Application.EnableEvents = False
    If Not Intersect(Target, Range("F15:F44")) Is Nothing Then
        If Target.Value = ("Removed") Then
            Range(Target, Target.Offset(0, 1)).Copy Destination:=Range("V13")
            If Range("W14").Value = Range("W15").Value Then
                MsgBox "You have removed all the wells from this Group ID." _
                & vbNewLine & "Use the 'Suspend ERP Group ID' checkbox above.", vbOKOnly + vbInformation, "Must Use 'Suspend ERP Group ID'"
                Target.Value = "-"
            End If            
        End If
    End If
Application.EnableEvents = True


JoyfulExodus:
    Application.EnableEvents = True

End Sub