当我更改引用的单元格时，会自动记录整行单元格(带有对其他单元格的引用) [英] Recording an entire row of cells (with references to other cells) automatically when I change the referenced cells

问题描述

我有需要更改的公式，这些公式具有输出和解决方案.我创建了整行A46:CV42，该行显示了每个单元格中每个受影响的值(公式解决方案).我需要的是，每当任何单元格值发生更改时，就会自动将对整个行的每个更改记录下来.可以重复使用它在其下面同一张纸上，也可以在另一张纸上.

I have formulas that I need to change that have outputs and solutions. I have created an entire row A46:CV42 that shows every effected Value (formulas solution) in each cell. What I need is to record every single change to that entire row automatically every time any cell value is changed. Be it on the same sheet below it in recurring rows or on another sheet.

我已经尝试了一些发现的东西，但是它们都处理了要更改的单个单元格或当您手动更改数据时.

I have tried some things I have found but they all deal with individual cells being changed or when you manually change data.

我尝试过的例子.我是该领域的新手.

Example of what I've tried. I am a novice in this area.

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

推荐答案

我不确定是要行46还是行42.请在下面看到一种方法，并在代码中添加注释以获取更多详细信息:

I'm not quite sure if you want row 46 or row 42. Please see below an approach, and comments in the code for further details:

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

这可能不是理想的解决方案，但我期待看到其他解决方案...同时，我认为它可以满足您的要求.

This might not be the ideal solution, but i'm looking forward to see other solutions... meanwhile, I think it does what you've asked.

这篇关于当我更改引用的单元格时，会自动记录整行单元格(带有对其他单元格的引用)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！