从功能单元格执行时，Excel VBA ByVal目标为范围不会更新目标工作表 [英] Excel VBA ByVal Target As Range Does not update Target Worksheet when excuting from a function cell

问题描述

从使用函数从另一个单元格获取其值的单元格复制值时遇到问题.将值插入到A1中，A3 = A1，并且无论何时A3的值更改，更改都需要自动转移到H列.我遇到的问题是，如果我将值键入A1，则A3会被更新，但值不会转移，如果我将值输入A3，则H列会更新.

I'm having a issues with copying a value from a cell that is using a function to get its value from another cell. Insert value into A1, A3=A1 and when ever the value of A3 changes the changes need to transfer automatically to column H. The issues i having is that if i type the value into A1 then A3 gets updated but the values do not transfer over, if i type the value into A3 then the H column does update.

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address = Range("A3").Address Then

    ' Get the last row on our destination sheet (using Sheet2, col A here)...
    Dim intLastRow As Long
    intLastRow = Sheet1.Cells(Sheet2.Rows.Count, "H").End(xlUp).Row

    ' Add our value to the next row...
    Sheet1.Cells(intLastRow + 1, "H") = Target.Value

End If

End Sub

请参见图片

我还尝试了以下代码.

 Private Sub Worksheet_Change(ByVal Target As Range)
 Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 Private Sub Worksheet_Change(ByVal Target As Range)

以上似乎均未触发更改事件.请我不能使用值A1进行值更改，因为它来自A3值更改.我什至考虑过将复制和粘贴宏运行到触发器A3.但是我真的希望值更改代码能够正常工作，我可以从A3获得更改值.预先感谢.

None of the above seems to trigger of the change event. Please I can not use Value A1 for the Value change it has come from A3 value change. I have even thought of running a copy and paste macro to Trigger A3. But would really like the value change code to work i can get the change value to work from A3. Thanks in Advance.

推荐答案

每次工作表上的计算完成时，以下脚本都会运行，有关更多信息，请参见

The following script will run every time the calculation on the sheet is finished, for more info see this link and it will then update Colunm H with the value in A3.

不确定是否确实有什么帮助，但可能会让您走上正确的道路.您还可以建立一个签入，以验证新的 A3 值是否与最后一个 Column H 值相同，如果相同，则可以跳过该值的插入

Not sure if it is exactly what will help but might get you on the right path. You can also build a check in to verify if the new A3 value is the same at the last Column H value and if they are then you could bypass the inserting of the value.

Private Sub Worksheet_calculate()

    ' Get the last row on our destination sheet (using Sheet2, col A here)...
    Dim intLastRow As Long
    intLastRow = Sheet1.Cells(Sheet2.Rows.Count, "H").End(xlUp).Row

    ' Add our value to the next row...
    Sheet1.Cells(intLastRow + 1, "H") = Sheet1.Range("A3").Value

End Sub

这篇关于从功能单元格执行时，Excel VBA ByVal目标为范围不会更新目标工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！