I'm using Private Sub Worksheet_Change(ByVal Target As Range)
to react to a changes in Range("AV9:AV" & lastrow)
in each of this cells is a dropdown list which is defined as follow:
Dim lastrow2 As Long
Dim lastcell As Long
lastrow2 = Tabelle3.Range("A" & Rows.Count).End(xlUp).Offset(8).Row
lastcell = Tabelle3.Range("AH1048576").End(xlUp).Row
For Each Cell In Tabelle3.Range(Tabelle3.Cells(9, 48), Tabelle3.Cells(lastcell, 48))
If Cell = "" Then
Dim MyList(2) As String
MyList(0) = "Relevant"
MyList(1) = "For Discussion"
MyList(2) = "Not Relevant"
With Tabelle3.Range("AV9:AV" & lastrow2).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:=Join(MyList, Application.International(xlListSeparator))
End With
End If
Next
Those lines are incorporated into a macro which fills Tabelle3
with data and all necessary functions, such as the dropdown field.
The Private Sub Worksheet_Change(ByVal Target As Range)
is defined as follow:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim lastrow As Long
lastrow = Tabelle3.Range("A" & Rows.Count).End(xlUp).Offset(8).Row
On Error Resume Next
If Not Intersect(Target, Range("AV9:AV" & lastrow)) Is Nothing And Target.Value = "Relevant" Or Target.Value = "For Discussion" Then
Application.CutCopyMode = False
Cells(Target.Row, "A").Resize(, 57).Copy
Tabelle14.Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
Tabelle14.Range("A" & Rows.Count).End(xlUp).PasteSpecial xlPasteFormats
Tabelle14.Range("A" & Rows.Count).End(xlUp).PasteSpecial xlPasteColumnWidths
Application.CutCopyMode = False
End If
If Not Intersect(Target, Range("AV9:AV" & lastrow)) Is Nothing And Target.Value <> "" Then
Cells(Target.Row, "A").Resize(, 2).Copy
Tabelle10.Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
Application.CutCopyMode = False
End If
'//Delete all duplicate rows
Set Rng = Tabelle10.UsedRange
Rng.RemoveDuplicates Columns:=Array(1)
End Sub
As you can see the first part of the Private Sub Worksheet_Change(ByVal Target As Range)
'should' only be executed If in a dropdown field in Range("AV9:AV" & lastrow) the option 'Relevant' or 'For Discussion' is selected
and the second part If anything is selceted
, therefore I have used Target.Value <> ""
. This is principally working fine but one bug occurs.
If I insert the data to Tabelle3
through the already mentioned macro, it seems the Private Sub Worksheet_Change(ByVal Target As Range)
is then automatically executed for row 9 in Tabelle3
and I can find its data in Tabelle14
and Tabelle10
as defined.
Does someone know what's going on here?
Aucun commentaire:
Enregistrer un commentaire