Excel，2张，2列，相同值？ [英] Excel, 2 sheets, 2 columns, same value?

问题描述

我在excel 2007文件中有2张sheet1和sheet2。

I have 2 sheets sheet1 and sheet2 in an excel 2007 file.

在sheet2中，我有一个由格式/宏管理的列（带树视图控制）。当选择元素时，单元格填充有x，当它被选中时，单元格填充（无）。

In sheet2 I have a column that is managed by a form/macro(with a tree view control). When an element has been selected, the cell is filled with an "x", when it has been unselected, the cell is filled with "" (nothing).

sheet1我想创建一个等于sheet2列的列。
所以例如：如果sheet2！C24 =x，那么sheet1！c24也应该是x
我也希望它的工作方式。如果用户将sheet1！c24更改为x，则我希望sheet2！c24采用相同的值。

In sheet1 I want to create a column equal to the sheet2 column. So for example: if sheet2!C24 = "x" then sheet1!c24 should also be "x" I also would like it to work both ways. If the user changes sheet1!c24 to "x", then I want sheet2!c24 to take the same value.

问题：
- 在Sheet1，I尝试了sheet1！c24 = sheet2！c24，但是当sheet2！c24 =时，sheet1！c24在Sheet2中显示0而不是
- 我尝试了sheet2！c24 = sheet1！c24，但是单元格显示公式（='sheet1！c24'）而不是值...

Problems: - in Sheet1, I tried sheet1!c24 = sheet2!c24, but then when sheet2!c24 = "", sheet1!c24 displays 0 instead of nothing - in Sheet2, I tried sheet2!c24 = sheet1!c24, but then the cells display the formula (='sheet1!c24') instead of the value...

所以基本上，我想要的是，无论你做什么改变，在sheet1或sheet2 ，sheet1和sheet2中的两列都被更新...
我该如何实现？

So basically, what I want is that whatever change you do, in sheet1 or in sheet2, both columns in sheet1 and sheet2 are updated... How can I achieve this?

推荐答案

您需要做的是使用两张表的 Worksheet_Change 事件，如果在您感兴趣的列中进行更改，则更新另一张表中的相同单元格。

What I think you need to do is use the Worksheet_Change events for both sheets and if a change is made in the column you are interested in, then you update the same cell in the other sheet.

这样的东西将进入工作表代码模块：

Something like this would go in the worksheet code module:

Private Sub worksheet_change(ByVal target As Range)
    Dim c As Range

    'Test to see if the cell just changed is
    'in the column we are interested in
    Set c = Application.Intersect(target, Range("A:A"))
    If Not c Is Nothing Then
        'Copy across to other sheet
        If Not beingEdited Then
            beingEdited = True
            Sheet1.Range(target.Address) = target.Value
            beingEdited = False
        End If
    End If
End Sub

你'需要一个 isEdited 变量被声明为具有较大范围的其他地方，以便您可以避免触发自身的事件和Excel陷入循环。

You'd need a beingEdited variable to be declared somewhere else with larger scope so that you could avoid the events triggering themselves and Excel getting stuck in a loop.

在另一张表中，您基本上具有相同的过程，但它将引用第一个工作表，例如 Sheet1.Range（target.Address）= target.Value 。

In the other sheet you'd basically have the same procedure, except that it would reference the first worksheet, e.g. Sheet1.Range(target.Address) = target.Value.

显然，你必须调整这个到你的范围/张。

Obviously, you'd have to tweak this to your ranges/sheets.

这篇关于Excel，2张，2列，相同值？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！