尝试这个。您可能需要稍微调整一下,但它应该可以帮助您前进。我添加了一个全局变量,您可以存储下拉列表中的先前值。
In the SelectionChange
我尝试创建错误处理来处理选定的多个单元格。如果仅选择 1 个单元格,则该值将绑定到全局变量。然后,您可以使用该变量在下拉列表中查找上一个值的工作表,循环遍历该工作表,然后删除该值。
首先,我已将其添加到您的天然气、电力等子项目中。使它们充满活力。
Sub Power(c As Range)
Dim rng As Range
Set rng = Nothing
Set rng = Range("A" & c.Row & ":F" & c.Row) '<< A1:F1 here is *relative to c.EntireRow*
'copy the values
With Worksheets("Power").Cells(Rows.Count, 1).End(xlUp)
.Offset(1, 0).Resize(1, rng.Cells.Count).Value = rng.Value
' Copy formating from Master Sheet
With Worksheets("Master")
Range("A" & c.Row & ":F" & c.Row).Copy
End With
.Offset(1, 0).PasteSpecial xlPasteFormats
Application.CutCopyMode = False
End With
End Sub
在主表(不是模块)下,我添加了以下内容:
' Add this to the absolute top of the sheet, must be outside a procedure (sub)
Option Explicit
Public cbxOldVal As String
Dim PrevVal As Variant
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Rows.Count > 1 Then Exit Sub
If Target.Columns.Count > 1 Then Exit Sub
cbxOldVal = Target.Value
End Sub
Private Sub Worksheet_Activate()
If Selection.Rows.Count = 1 And Selection.Columns.Count = 1 Then
PrevVal = Selection.Value
Else
PrevVal = Selection
End If
End Sub
将其添加到您的Worksheet_Change
事件。
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range, c As Range
Set rng = Intersect(Target, Range("G2:G1001"))
If Not Intersect(Target, Columns("G")) Is Nothing Then
If PrevVal <> "" Or cbxOldVal <> "" Then
If cbxOldVal = Target.Value Then
MsgBox "You have to click on another cell " & vbNewLine & "and then click back on " & Target.Address & " to change the value", vbExclamation, "Error"
Cells(Target.Row, Target.Column) = PrevVal
Exit Sub
ElseIf Target.Value = "" Or Target.Value = PrevVal Then Exit Sub
End If
End If
End If
If Not rng Is Nothing Then
' Your loop
然后我添加了一些代码到你的Worksheet_Change
事件。将其添加到之后End Select
.
If cbxOldVal = "" Then
' do nothing
Else
With Worksheets(cbxOldVal)
Dim i As Integer
Dim strFindA As String, strFindB As String, strFindC As String
Dim strFindD As String, strFindE As String, strFindF As String
strFindA = Sheets("Master").Range("A" & c.Row)
strFindB = Sheets("Master").Range("B" & c.Row)
strFindC = Sheets("Master").Range("C" & c.Row)
strFindD = Sheets("Master").Range("D" & c.Row)
strFindE = Sheets("Master").Range("E" & c.Row)
strFindF = Sheets("Master").Range("F" & c.Row)
For i = 1 To 100 ' replace with lastrow
If .Cells(i, 1).Value = strFindA _
And .Cells(i, 2).Value = strFindB _
And .Cells(i, 3).Value = strFindC _
And .Cells(i, 4).Value = strFindD _
And .Cells(i, 5).Value = strFindE _
And .Cells(i, 6).Value = strFindF _
Then
.Rows(i).EntireRow.Delete
MsgBox "deleted row " & i
GoTo skip:
End If
Next i
End With
End If
skip: