在worksheet_change中选择目标表 [英] Select target sheet in worksheet_change
问题描述
我正在尝试编写一个vba宏,该宏将在发生任何更改的情况下在一组单元格中插入Vlookup公式(该公式应在另一张工作表中查找值并将其粘贴到 C列中)制成的表.这是我到目前为止的代码:
I'm trying to write a vba macro which inserts a Vlookup formula (which should look for a value in another sheet and paste it in Column C) in a set of cells when any change is made to the sheet. This is the code I have so far:
Private Sub worksheet_change(ByVal target As Range)
Dim thisRow As Integer
target.Range = Hoja4.Columns("C")
If target.Column = 1 Then
thisRow = target.Row
Range("B" & thisRow).Formula = "=vlookup(B & thisRow,Personal!$A$1:$H$500,2,false)"
Else: Exit Do
End Sub
问题是,如何告诉宏它应该在特定的工作表中运行?
Question is, how do I tell the macro that it should run in a specific Sheet?
任何帮助将不胜感激. 预先感谢!
Any help will be appreciated. Thanks in advance!
推荐答案
就像@ShaiRado所述,您的事件代码应在Worksheet
模块内运行.如果要检索对该工作表的引用或该工作表的属性(例如名称),则可以使用Target
范围的Parent
属性.例如,在您的代码中,它将如下所示:
Like @ShaiRado mentioned, your event code should be running inside a Worksheet
module. If you want to retrieve a reference to the sheet, or a property (e.g. name) of that sheet then you can use the Parent
property of the Target
range. For example in your code it would as follows:
Private Sub worksheet_change(ByVal target As Range)
Dim thisRow As Integer
target.Range = Hoja4.Columns("C")
Dim wsTarget As Worksheet
Set wsTarget = Range.Parent
...
如果使用Workbook
级事件处理程序检查Workbook
中的任何工作表中的更改,则目标表将被烘焙到事件签名中:
If you use a Workbook
level event handler to check for change in any worksheet in your Workbook
then the target sheet is baked into the event signature:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'Sh is the worksheet that has Target i.e the range that was changed
End Sub
这篇关于在worksheet_change中选择目标表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!