当我更改引用的单元格时，自动记录整行单元格(引用其他单元格)
当我更改引用的单元格时，自动记录整行单元格(引用其他单元格)
提问于 2019-06-02 01:34:07
我有需要更改的公式，这些公式有输出和解决方案。我已经创建了一个完整的行A46:CV42，它显示了每个单元格中的每个受影响的值(公式解)。我需要的是在每次更改任何单元格值时自动记录对整个行的每一次更改。它可以在它下面的同一张纸上，在循环行中，或者在另一张纸上。
我已经尝试了一些我发现的东西，但它们都处理单个单元格被更改或当您手动更改数据时。
我尝试过的例子。我是这个领域的新手。
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$2" Then
a = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row + 1
Sheets("Sheet2").Range("A" & a).Value = Sheets("Sheet1").Range("A").Value
End If
End Sub回答 1
Stack Overflow用户
回答已采纳
发布于 2019-06-02 02:16:57
我不太确定您是想要行46还是行42。有关更多详细信息，请参阅下面的方法和代码中的注释：
Private Sub Worksheet_Change(ByVal Target As Range)
Dim wb As Workbook: Set wb = ThisWorkbook
Dim wsWatch As Worksheet: Set wsWatch = wb.Sheets("Sheet1") 'declare and set the sheet (change the name or use Target.Worksheet instead as needed)
Dim rngWatch As Range: Set rngWatch = wsWatch.Range("A46:CV46") 'declare and set the range to watch over
Dim arrWatch As Variant: arrWatch = rngWatch 'allocate the range to an array
Dim wsHistory As Worksheet: Set wsHistory = wb.Sheets("Sheet2") 'declare and set the sheet
With wsHistory
Dim lRow As Long: lRow = .Cells(.Rows.Count, "A").End(xlUp).Row 'get the last row in the history
Dim rngHistory As Range: Set rngHistory = .Range(.Cells(lRow, 1), .Cells(lRow, 100)) 'declare and set the range of the last populated row (last historic change)
End With
Dim arrHistory As Variant: arrHistory = rngHistory 'allocate the range to an array
Dim C As Long
'Only one row in the arrays, let's loop over the columns
For C = LBound(arrWatch, 2) To UBound(arrWatch, 2) 'for each column in the ranges
If arrWatch(1, C) <> arrHistory(1, C) Then 'if there is a mismatch
rngHistory.Offset(1) = rngWatch.Value 'allocate the values in the next free row
Exit For 'exit here if mismatch found
End If
Next C
End Sub这可能不是理想的解决方案，但我期待看到其他解决方案……同时，我认为它做到了你所要求的。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/56408979
复制相关文章
相似问题
腾讯云开发者
