vba Worksheet_Change 宏 - 更改多个单元格

Worksheet_Change Macro - Changing multiple cells

提问by CinCity

I wrote a macro and while it works, functionally its not what is needed. It's an interactive checklist that breaks down multiple areas of machines and if they are working checks them off and then this updates a master list with multiple sections. However, it only works with one cell at a time and it needs to be able to work with multiple cells at a time (both with rows and columns). Here's my current code:

我写了一个宏，虽然它可以工作，但在功能上并不是所需要的。这是一个交互式清单，可以分解机器的多个区域，如果它们正在工作，则将它们检查出来，然后更新包含多个部分的主列表。但是，它一次只能处理一个单元格，并且需要能够一次处理多个单元格（包括行和列）。这是我当前的代码：

'Updates needed:
'       Make so more than one cell works at a time
'       in both x and y directions

Private Sub Worksheet_Change(ByVal Target As Excel.range)
    Dim wb As Workbook
    Dim mWS As Worksheet
    Dim conName As String
    Dim mCol As range
    Dim mCon As Integer
    Dim count As Long
    Dim cell As range
    Dim y As String

    count = 1
    y = ""
    Set wb = ActiveWorkbook
    Set mWS = wb.Sheets("Master")
    Set mCol = mWS.range("B:B")
    mCon = 0


   'Selects the name of the string value in which we need to search for in master list
    If Target.Column < 100 Then
       ThisRow = Target.Row
       conName = ActiveSheet.Cells(ThisRow, "B")
       y = Target.Value
    End If 

    'search for matching string value in master list
    For Each cell In mCol
        If cell.Value = conName Then
            mCon = count
                Exit For
        End If
       count = count + 1
    Next
  'mark as "x" in Master list
   Dim cVal As Variant
   Set cVal = mWS.Cells(count, Target.Column)
   cVal.Value = y
End Sub

What is happening - If I drag down "x" for multiple rows or columns my codes breaks at y = Target.Value and will only update the cell I first selected and its counterpart on the master list. What it should do is if I drag and drop the "x" onto multiple rows of columns it should update all of them in the sheet I'm working on and the master list. I only set up the macro for one cell at a time and I have no idea how to set it up for dragging and dropping the "x" value for multiple rows

发生了什么 - 如果我为多行或多列向下拖动“x”，我的代码会在 y = Target.Value 处中断，并且只会更新我首先选择的单元格及其在主列表中的对应单元格。它应该做的是，如果我将“x”拖放到多行列上，它应该更新我正在处理的工作表和主列表中的所有列。我一次只为一个单元格设置宏，我不知道如何设置它以拖放多行的“x”值

回答by David Zemens

I think you need a For ... Eachiteration over the Targetin order to work with multiple cells. As Michael noted in the comments, the _Changeevent fires only once, but the Targetreflects all cell(s) that changed, so you should be able to iterate over the Targetrange. I tested using this simple event handler:

我认为您需要For ... Each对 theTarget进行迭代才能使用多个单元格。正如迈克尔在评论中指出的那样，该_Change事件仅触发一次，但Target反映了所有更改的单元格，因此您应该能够遍历该Target范围。我使用这个简单的事件处理程序进行了测试：

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRange As Range
Dim myCell As Range
Set myRange = Target

For Each myCell In myRange.Cells
    Debug.Print myCell.Address
Next

End Sub

I am not able to test obviously on your data/worksheet, but I think it should put you on the right track.

我无法在您的数据/工作表上进行明显测试，但我认为它应该让您走上正轨。

Private Sub Worksheet_Change(ByVal Target As Excel.range)
Dim wb As Workbook
Dim mWS As Worksheet
Dim conName As String
Dim mCol As range
Dim mCon As Integer
Dim count As Long
Dim cell As range
Dim y As String

count = 1
y = ""
Set wb = ActiveWorkbook
Set mWS = wb.Sheets("Master")
Set mCol = mWS.range("B:B")
mCon = 0

'Add some new variables:
Dim myRange as Range
Dim myCell as Range
Set myRange = Target

Application.EnableEvents = False '## prevents infinite loop
For each myCell in myRange.Cells
    If myCell.Column < 100 Then
       ThisRow = myCell.Row
       conName = ActiveSheet.Cells(ThisRow, "B")
       y = myCell.Value
    End If 

    'search for matching string value in master list
    For Each cell In mCol
        If cell.Value = conName Then
            mCon = count
                Exit For
        End If
       count = count + 1
    Next
  'mark as "x" in Master list
   Dim cVal As Variant
   Set cVal = mWS.Cells(count, Target.Column)
   cVal.Value = y

Next
Application.EnableEvents = True '## restores event handling to True
End Sub

回答by Sam

You need to iterate through the cells using a ForEachloop.

您需要使用循环遍历单元格ForEach。

Also, you may be better using the Selectionobject rather than Target

此外，您可能会更好地使用该Selection对象而不是Target

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual


For Each cell In Selection
    Debug.Print cell.Address
Next cell



Application.EnableEvents = True
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

Exit Sub