如何使用更改事件计算公式并将值插入到相应的单元格 [英] How to calculate formula and insert value to respective cell using change event

问题描述

我在以下问题中寻求代码的帮助:

Sheet2

这是我用于Sheet1的代码，它没有问题:

 选项显式枚举Nws工作表，其中使用数据"值'060-2NwsFirstDataRow = 10'更改为适合NwsTrigger = 8'触发列(5 =列E)NwsTarget = 10'目标列(无值=前一个+ 1)结束枚举范围为数据"的Enum Nta'列'060NtaId = 1'数据"范围的第一列NtaVal = 4''数据'范围的第三列结束枚举私人子Worksheet_Change(按目标的ByVal目标)'060-2昏暗范围昏暗的Tmp作为变体'如果更改了多个单元格，则跳过操作如果Target.CountLarge>1然后退出子设置Rng = Range(Cells(NwsFirstDataRow，NwsTrigger)，_单元格(Rows.Count，NwsTrigger).End(xlUp))如果不是Application.Intersect(Target，Rng)那么随着申请Tmp ＝ .VLookup(Target.Value，Range("Data")，NtaVal，False)如果不是IsError(Tmp)然后.EnableEvents = False'抑制'Change'事件单元格(Target.Row，NwsTarget).Value = Tmp.EnableEvents = True万一结束于别的设置Rng = Range("B2:E4")'更改为适合如果不是Application.Intersect(Target，Rng.Columns(NtaVal-1))那么'如果不是Application.Intersect(Target，Range("D2:D4"))那么什么都没有UpdateCategory单元格(Target.Row，Rng.Column).Resize(1，NtaVal).Value万一万一结束子私人子Worksheet_activate()'060-2Dim TgtWs As Worksheet'在其上使用了'Data'的选项卡昏暗的猫作为变体数据"类别(2个单元格为Nta)Dim R As Long'循环计数器:行设置TgtWs = Sheet1'更改以匹配您的事实使用Range("Data")'进行更改以匹配您的事实对于R = 1到.Rows.CountCat = .Rows(R).Value更新类别猫下一个R结束于结束子私人子UpdateCategory(类别为Cat的猫)'060-2Dim Fnd As Range'匹配单元格昏暗的FirstFound As Long'首场比赛昏暗范围Application.EnableEvents =假设置Rng = Range(Cells(NwsFirstDataRow，NwsTrigger)，_单元格(Rows.Count，NwsTrigger).End(xlUp))带Rng设置Fnd = .Find(Cat(1，NtaId)，LookIn:= xlValues，LookAt:= xlWhole)如果没有，那么FirstFound = Fnd.Row做单元格(Fnd.Row，NwsTarget).值= Cat(1，NtaVal)设置Fnd = .FindNext(Fnd)如果Fnd一无所有，则退出执行Fnd.Row时循环首次发现万一结束于Application.EnableEvents =真结束子 

Sheet2的代码不是:

 选项显式枚举Nws1'工作表，其中使用了'Data1'值'060-2Nws1FirstData1Row = 16'更改为适合Nws1Trigger = 18'触发列(5 =列E)Nws1Target = 20'目标列(无值=前一个+ 1)结束枚举范围'Data1'的Enum Nta1'列'060Nta1Id = 1'Data1'范围的第一列Nta1Val = 5'Data1'范围的第三列结束枚举私人子Worksheet_Change(按目标的ByVal目标)'060-2昏暗范围昏暗的Tmp作为变体'如果更改了多个单元格，则跳过操作如果Target.CountLarge>1然后退出子设置Rng = Range(Cells(Nws1FirstData1Row，Nws1Trigger)，_单元格(Rows.Count，Nws1Trigger).End(xlUp))如果不是Application.Intersect(Target，Rng)那么随着申请Tmp ＝ .VLookup(Target.Value，Range("Data1")，Nta1Val，False)如果不是IsError(Tmp)然后.EnableEvents = False'抑制'Change'事件单元格(Target.Row，Nws1Target).Value = Tmp.EnableEvents = True万一结束于别的设置Rng = Range("M19:M25")'更改为适合如果不是Application.Intersect(Target，Rng.Columns(Nta1Val-2))那么UpdateCategory单元格(Target.Row，Rng.Column).Resize(1，Nta1Val).Value万一万一结束子私人子Worksheet_activate()'060-2Dim TgtWs As Worksheet'在其上使用了'Data1'的选项卡Dim Cat As Variant'Data1'类别(2个单元格为Nta1)Dim R As Long'循环计数器:行设置TgtWs = Sheet2'更改以匹配您的事实使用Range("Data1")'进行更改以匹配您的事实对于R = 1到.Rows.CountCat = .Rows(R).Value更新类别猫下一个R结束于结束子私人子UpdateCategory(类别为Cat的猫)'060-2Dim Fnd As Range'匹配单元格昏暗的FirstFound As Long'首场比赛昏暗范围Application.EnableEvents =假设置Rng = Range(Cells(Nws1FirstData1Row，Nws1Trigger)，_单元格(Rows.Count，Nws1Trigger).End(xlUp))带Rng设置Fnd = .Find(Cat(1，Nta1Id)，LookIn:= xlValues，LookAt:= xlWhole)如果没有，那么FirstFound = Fnd.Row做单元格(Fnd.Row，Nws1Target).值= Cat(1，Nta1Val)设置Fnd = .FindNext(Fnd)如果Fnd一无所有，则退出执行Fnd.Row时循环首次发现万一结束于Application.EnableEvents =真结束子 

任何帮助将不胜感激！

解决方案

这是原始代码的摘录.

  Set Rng = Range("Data")'更改为适合如果不是Application.Intersect(Target，Rng.Columns(NtaVal))那么UpdateCategory单元格(Target.Row，Rng.Column).Resize(1，NtaVal).Value万一 

下面是Sheet1后面代码中的相应部分.

  Set Rng = Range("B2:E4")'更改为适合如果不是Application.Intersect(Target，Rng.Columns(NtaVal-1))那么UpdateCategory单元格(Target.Row，Rng.Column).Resize(1，NtaVal).Value万一 

这与Sheet2背后的代码完全相同.

  Set Rng = Range("M19:M25")'更改为适合如果不是Application.Intersect(Target，Rng.Columns(Nta1Val-2))那么UpdateCategory单元格(Target.Row，Rng.Column).Resize(1，Nta1Val).Value万一 

现在您可以分析发生了什么事情.

1. Data 范围已按名称声明，以减轻您多次检查地址的麻烦.您需要在工作表上使用它，而在代码中也需要使用它.您只需设置一次即可，无论您在何处使用该名称都是正确的.
2. 在您自己绘制的相同代码中，您将名称更改为工作表地址: Range("B2:E4").的确，这没有什么区别，只是您必须检查以确保Range("B2:E4")确实与 Data 相同.这是额外的工作，但是有效.
3. 使用 Set Rng = Range("M19:M25")，您走进了为自己设置的陷阱.根据您的设计，它应该被命名为范围 Data1 .但事实并非如此. Data1 有5列，而您声明的范围只有1列.

根据以上分析，很清楚您是通过哪种逻辑得出错误的.您不是拥有"的命名范围.因此，您努力将其替换为坐标.在此过程中，您放弃了使用命名变量的安全性，但是当您承担额外的风险时，便没有进行必要的额外检查.

请注意Sheet2代码中 UpdateCategory Cells(Target ... )行的缺失意图.缩进用于显示IF语句的开头和结尾.需要更多这样的帮助来阅读代码，而不是专家，但是，事实是，所有初学者(包括您自己的东西)都认为这没有什么区别，而且的确没有什么区别，但是更高级的程序员知道，他们比其他任何事情都需要清晰度.您可以从缩进代码中看出程序员的经验，这是一个非常可靠的指标.

I was asking for help with the code in the following question:

Insert value based on drop down list from cell next to matched one

With a big effort of @Variatus who helped me to find the solution I have working code to "insert value based on drop down list from cell next to matched one" which works in both ways. When I was playing around to to get deep in the code I tried to figure out how to use Worksheet_Change for formula calculation. I wanted to avoid complex code so I'm checking column "D" with dropdown list values and when this is changed then calculated formula value in the column "E" is copied to matched cell in the next table. Everything works like a charm on my "Sheet1". But when I tried to replicate the code to my "Sheet2" it stopped working this way even I didn't change anything. Maybe I'm missing something but I can't figure out what it is. I tried start over from the beginning but still nothing.

And here are two PrtScns of "Sheet1" and "Sheet2":

Sheet1

Sheet2

And this the code I used for Sheet1 which works with no issue:

Option Explicit

Enum Nws                    ' worksheet where 'Data' values are used
    ' 060-2
    NwsFirstDataRow = 10     ' change to suit
    NwsTrigger = 8           ' Trigger column (5 = column E)
    NwsTarget = 10           ' Target column (no value = previous + 1)
End Enum

Enum Nta                    ' columns of range 'Data'
    ' 060
    NtaId = 1               ' 1st column of 'Data' range
    NtaVal = 4              ' 3rd column of 'Data' range
End Enum

Private Sub Worksheet_Change(ByVal Target As Range)
    ' 060-2
    
    Dim Rng         As Range
    Dim Tmp         As Variant
    
    ' skip action if more than 1 cell was changed
    If Target.CountLarge > 1 Then Exit Sub
    
    Set Rng = Range(Cells(NwsFirstDataRow, NwsTrigger), _
                    Cells(Rows.Count, NwsTrigger).End(xlUp))
    If Not Application.Intersect(Target, Rng) Is Nothing Then
        With Application
            Tmp = .VLookup(Target.Value, Range("Data"), NtaVal, False)
            If Not IsError(Tmp) Then
                .EnableEvents = False       ' suppress 'Change' event
                Cells(Target.Row, NwsTarget).Value = Tmp
                .EnableEvents = True
            End If
        End With
    Else
        Set Rng = Range("B2:E4")             ' change to suit
        If Not Application.Intersect(Target, Rng.Columns(NtaVal - 1)) Is Nothing Then
'        If Not Application.Intersect(Target, Range("D2:D4")) Is Nothing Then
            UpdateCategory Cells(Target.Row, Rng.Column).Resize(1, NtaVal).Value
        End If
    End If
End Sub
    
Private Sub Worksheet_activate()
    ' 060-2
    
    Dim TgtWs           As Worksheet        ' the Tab on which 'Data' was used
    Dim Cat             As Variant          ' 'Data' category (2 cells as Nta)
    Dim R               As Long             ' loop counter: rows
    Set TgtWs = Sheet1                      ' change to match your facts
    
    With Range("Data")                      ' change to match your facts
        For R = 1 To .Rows.Count
            Cat = .Rows(R).Value
            UpdateCategory Cat
        Next R
    End With
End Sub

Private Sub UpdateCategory(Cat As Variant)
    ' 060-2
    
    Dim Fnd             As Range            ' matching cell
    Dim FirstFound      As Long             ' row of first match
    Dim Rng             As Range
    
    Application.EnableEvents = False
    Set Rng = Range(Cells(NwsFirstDataRow, NwsTrigger), _
                     Cells(Rows.Count, NwsTrigger).End(xlUp))
    With Rng
         Set Fnd = .Find(Cat(1, NtaId), LookIn:=xlValues, LookAt:=xlWhole)
         If Not Fnd Is Nothing Then
            FirstFound = Fnd.Row
            Do
                Cells(Fnd.Row, NwsTarget).Value = Cat(1, NtaVal)
                Set Fnd = .FindNext(Fnd)
                If Fnd Is Nothing Then Exit Do
            Loop While Fnd.Row <> FirstFound
        End If
    End With
    Application.EnableEvents = True
End Sub

And the code for Sheet2 which doesn't:

Option Explicit

Enum Nws1                    ' worksheet where 'Data1' values are used
    ' 060-2
    Nws1FirstData1Row = 16     ' change to suit
    Nws1Trigger = 18          ' Trigger column (5 = column E)
    Nws1Target = 20            ' Target column (no value = previous + 1)
End Enum

Enum Nta1                    ' columns of range 'Data1'
    ' 060
    Nta1Id = 1               ' 1st column of 'Data1' range
    Nta1Val = 5              ' 3rd column of 'Data1' range
End Enum

Private Sub Worksheet_Change(ByVal Target As Range)
    ' 060-2
    
    Dim Rng         As Range
    Dim Tmp         As Variant
    
    ' skip action if more than 1 cell was changed
    If Target.CountLarge > 1 Then Exit Sub
    
    Set Rng = Range(Cells(Nws1FirstData1Row, Nws1Trigger), _
                    Cells(Rows.Count, Nws1Trigger).End(xlUp))
    If Not Application.Intersect(Target, Rng) Is Nothing Then
        With Application
            Tmp = .VLookup(Target.Value, Range("Data1"), Nta1Val, False)
            If Not IsError(Tmp) Then
                .EnableEvents = False       ' suppress 'Change' event
                Cells(Target.Row, Nws1Target).Value = Tmp
                .EnableEvents = True
            End If
        End With
    Else
        Set Rng = Range("M19:M25")             ' change to suit
        If Not Application.Intersect(Target, Rng.Columns(Nta1Val - 2)) Is Nothing Then
        UpdateCategory Cells(Target.Row, Rng.Column).Resize(1, Nta1Val).Value
        End If
    End If
End Sub
    
Private Sub Worksheet_activate()
    ' 060-2
    
    Dim TgtWs           As Worksheet        ' the Tab on which 'Data1' was used
    Dim Cat             As Variant          ' 'Data1' category (2 cells as Nta1)
    Dim R               As Long             ' loop counter: rows
    Set TgtWs = Sheet2                      ' change to match your facts
    
    With Range("Data1")                      ' change to match your facts
        For R = 1 To .Rows.Count
            Cat = .Rows(R).Value
            UpdateCategory Cat
        Next R
    End With
End Sub

Private Sub UpdateCategory(Cat As Variant)
    ' 060-2
    
    Dim Fnd             As Range            ' matching cell
    Dim FirstFound      As Long             ' row of first match
    Dim Rng             As Range
    
    Application.EnableEvents = False
    Set Rng = Range(Cells(Nws1FirstData1Row, Nws1Trigger), _
                     Cells(Rows.Count, Nws1Trigger).End(xlUp))
    With Rng
         Set Fnd = .Find(Cat(1, Nta1Id), LookIn:=xlValues, LookAt:=xlWhole)
         If Not Fnd Is Nothing Then
            FirstFound = Fnd.Row
            Do
                Cells(Fnd.Row, Nws1Target).Value = Cat(1, Nta1Val)
                Set Fnd = .FindNext(Fnd)
                If Fnd Is Nothing Then Exit Do
            Loop While Fnd.Row <> FirstFound
        End If
    End With
    Application.EnableEvents = True
End Sub

Any help would be well appreciated!

解决方案

This is an excerpt from the original code.

Set Rng = Range("Data")             ' change to suit
If Not Application.Intersect(Target, Rng.Columns(NtaVal)) Is Nothing Then
    UpdateCategory Cells(Target.Row, Rng.Column).Resize(1, NtaVal).Value
End If

Below is the corresponding part from your code behind Sheet1.

Set Rng = Range("B2:E4")             ' change to suit
If Not Application.Intersect(Target, Rng.Columns(NtaVal - 1)) Is Nothing Then
    UpdateCategory Cells(Target.Row, Rng.Column).Resize(1, NtaVal).Value
End If

And here is the exact same part from your code behind Sheet2.

Set Rng = Range("M19:M25")             ' change to suit
If Not Application.Intersect(Target, Rng.Columns(Nta1Val - 2)) Is Nothing Then
UpdateCategory Cells(Target.Row, Rng.Column).Resize(1, Nta1Val).Value
End If

Now you can analyse what happened.

1. The Data range was declared by name to relieve you of the chore to check the address multiple times. You need it on the sheet and you need it in the code. You set it once and it will be correct wherever you use the name.
2. In your own rendering of the same code you changed the name to a sheet address: Range("B2:E4"). It's true that it makes no difference, except that you have to check to be sure that Range("B2:E4") really is the same as Data. It's extra work but it works.
3. with Set Rng = Range("M19:M25") you walked into the trap which you set for yourself. By your design this is supposed to be the named range Data1. But it isn't. Data1 has 5 columns and the range you declare in its place has only 1.

From the above analysis it's very clear by which logic you arrived at the mistake. You didn't "own" the named range. Therefore you strove to replace it with coordinates. In the process you gave up the safety that comes from using named variables and then failed to put in the extra checking needed when you take extra risk.

Please observe the missing intent for the line UpdateCategory Cells(Target... in your code for Sheet2. The indent serves to show the beginning and End of the IF statement. One would expect a beginner to need more of such help reading code than an expert. Truth is however that all beginners (your good-self included) think it makes no difference, and it really doesn't, but more advanced programmers know that they need clarity above all else. You can tell the experience of a programmer from the indenting he applies in his code. It's a very reliable indicator.

这篇关于如何使用更改事件计算公式并将值插入到相应的单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！