设为首页收藏本站
开启辅助访问

Excel精英培训网

 找回密码
 注册
Excel精英培训网»论坛 › Excel 求助区 › Excel VBA开发 › worksheet change黏贴数据，不自动计算问题
数据透视表40+个常用小技巧，让你一次学会！
12下一页
返回列表 发新帖
查看: 3928|回复: 16

[已解决]worksheet change黏贴数据，不自动计算问题

[复制链接]
发表于 2013-12-19 01:26 | 显示全部楼层 |阅读模式
工作中常用的Excel函数公式，全印在一张超大鼠标垫上
本帖最后由 dreamknight0 于 2013-12-19 01:34 编辑

现在不能通过黏贴和向下填充数据让其自动计算，只能通过双击单元格方式使得此表自动联动计算，如何实现黏贴数据或向下填充后能自动计算？
  1. <div class="blockcode"><blockquote>Private Sub Worksheet_Change(ByVal Target As Range)
  2. On Error Resume Next
  3. If Target.Column = 12 And Target.Text <> "" And Target.Row > 4 Then
  4. a = Target.Row
  5. b = Target.Column
  6. Application.EnableEvents = False
  7. Cells(a, b - 6) = (1.187 * Cells(a, b) + 1) * Cells(a, b - 7)
  8. Application.EnableEvents = True

  10. ElseIf Target.Column = 6 And Target.Text <> "" And Target.Row > 4 Then
  11. a = Target.Row
  12. b = Target.Column
  13. Application.EnableEvents = False
  14. If Cells(a, b - 1).Text <> "" Then
  15. Cells(a, b + 6) = (Cells(a, b) - Cells(a, b - 1)) / (Cells(a, b - 1) * 1.187)
  16. Else
  17. Exit Sub
  18. End If
  19. Application.EnableEvents = True

  21. ElseIf Target.Column = 5 And Target.Text <> "" And Target.Row > 4 Then
  22. a = Target.Row
  23. b = Target.Column
  24. Application.EnableEvents = False
  25. If Cells(a, b + 7).Text <> "" Then
  26. Cells(a, b + 1) = (1.187 * Cells(a, b + 7) + 1) * Cells(a, b)
  27. Application.EnableEvents = True
  28. ElseIf Cells(a, b + 1).Text <> "" Then
  29. Cells(a, b + 7) = (Cells(a, b - 1) - Cells(a, b - 2)) / (Cells(a, b - 2) * 1.187)
  30. Else: Exit Sub
  31. End If
  32. End If
  33. End Sub
复制代码
最佳答案
月排行榜 / 总排行榜
hwc2ycy
2013-12-19 18:32
dreamknight0 发表于 2013-12-19 11:00
求高人渡劫~~
  1. Private Sub Worksheet_Change(ByVal Target As Range)
  2.     On Error Resume Next
  3.     Dim rg As Range
  4.     Application.EnableEvents = False
  5.     Application.ScreenUpdating = False

  7.     For Each rg In Target
  8.         With rg
  9.             If .Row > 4 Then
  10.                 Select Case .Column
  11.                     Case 12:
  12.                         '        If Target.Column = 12 And Target.Text <> "" And Target.Row > 4 Then
  13.                         '            a = Target.Row
  14.                         '            b = Target.Column
  15.                         '            Application.EnableEvents = False
  16.                         '            Cells(a, b - 6) = (1.187 * Cells(a, b) + 1) * Cells(a, b - 7)
  17.                         If Len(.Value) Then
  18.                             .Offset(, -6).Value = (1.187 * .Value + 1) * .Offset(, -7).Value
  19.                         End If
  20.                     Case 6:
  21.                         '        ElseIf Target.Column = 6 And Target.Text <> "" And Target.Row > 4 Then
  22.                         '            a = Target.Row
  23.                         '            b = Target.Column
  24.                         '            Application.EnableEvents = False
  25.                         '            If Cells(a, b - 1).Text <> "" Then
  26.                         '                Cells(a, b + 6) = (Cells(a, b) - Cells(a, b - 1)) / (Cells(a, b - 1) * 1.187)
  27.                         If Len(.Offset(, -1).Value) Then
  28.                             .Offset(, 6).Value = .Value - .Offset(, -1).Value / .Offset(, -1).Value * 1.187
  29.                         End If

  31.                     Case 5:
  32.                         '        ElseIf Target.Column = 5 And Target.Text <> "" And Target.Row > 4 Then
  33.                         '            a = Target.Row
  34.                         '            b = Target.Column
  35.                         '            Application.EnableEvents = False
  36.                         '            If Cells(a, b + 7).Text <> "" Then
  37.                         '                Cells(a, b + 1) = (1.187 * Cells(a, b + 7) + 1) * Cells(a, b)
  38.                         '                Application.EnableEvents = True
  39.                         '            ElseIf Cells(a, b + 1).Text <> "" Then
  40.                         '                Cells(a, b + 7) = (Cells(a, b - 1) - Cells(a, b - 2)) / (Cells(a, b - 2) * 1.187)
  41.                         '            Else: Exit Sub
  42.                         '            End If
  43.                         '        End If
  44.                         If Len(.Value) Then
  45.                             If Len(.Offset(, 7).Value) Then
  46.                                 .Offset(, 1).Value = 1.187 * (.Offset(, 7) + 1) * .Value
  47.                             ElseIf Len(.Offset(, 1).Value) Then
  48.                                 .Offset(, 7).Value = .Offset(, -1).Value - .Offset(, -2).Value / (.Offset(, 2).Value * 1.187)
  49.                             End If
  50.                         End If

  52.                 End Select
  53.             End If
  54.         End With
  55.     Next
  56.     Application.EnableEvents = True
  57.     Application.ScreenUpdating = True
  58. End Sub
复制代码
框架就是这样，根据你的改了下。
怎么计算，计算的方法你自己改下。


change
问题.jpg

报价工具.rar

19.96 KB, 下载次数: 10

附表

相关帖子
回复

使用道具 举报

工作中最常用的函数公式，全印在一张超大的鼠标垫上
hwc2ycy
发表于 2013-12-19 08:47 | 显示全部楼层
工作中常用的Excel函数公式，全印在一张超大鼠标垫上
你注意到没，你的代码中EXIT SUB执行前没有加上Application.EnableEvents = TRUE
这样只要一次不满足条件的测试，就会导致事件不会在发生了。
如果是更改F列数据是代码会自动帮你更新，但这一切的前提是有录入了数据，如果只是删除了数据，你的代码就不会更新了。

回复

使用道具 举报

hwc2ycy
发表于 2013-12-19 09:08 | 显示全部楼层
工作中最常用的Excel函数公式，全印在一张鼠标垫上
粘贴是会触发事件的，我忘了看你的标题了。可能是你的代码有提前退出，事件响应关闭后没有打开。
回复

使用道具 举报

发表于 2013-12-19 09:26 | 显示全部楼层
前面烟花说了，你使用了 关闭事件代码，条件不满足时你退出了过程却没有打开事件，这是导致后来事件不能用的原因

还有就是，你代码中使用了 on error
你用了这个，并没有对预想出问题的地方添加错误判断，

这会导致所有的错误都会被忽略，最后的结果就是你看到的 “不能计算”

a=traget.row,b=target.coumn

这两个代码，当你操作的是单元格区域时，如果行区域数大于1，或者列区域数大于1，就会报错
（由于你忽略了错误，就不会提示错误）最后就是你看到的“不能计算”

对于事件，要想支持区域操作，应该是
for each T in target
  这里对每一个操作的单元格进行核算
next

回复

使用道具 举报

 楼主| 发表于 2013-12-19 09:29 | 显示全部楼层
hwc2ycy 发表于 2013-12-19 09:08
粘贴是会触发事件的，我忘了看你的标题了。可能是你的代码有提前退出，事件响应关闭后没有打开。

仔细看了下，的确有缺少事件响应打开，但是补充后还是不行，烦请您看看
  1. Private Sub Worksheet_Change(ByVal Target As Range)
  2. On Error Resume Next
  3. If Target.Column = 12 And Target.Text <> "" And Target.Row > 4 Then
  4. a = Target.Row
  5. b = Target.Column
  6. Application.EnableEvents = False
  7. Cells(a, b - 6) = (1.187 * Cells(a, b) + 1) * Cells(a, b - 7)
  8. Application.EnableEvents = True

  10. ElseIf Target.Column = 6 And Target.Text <> "" And Target.Row > 4 Then
  11. a = Target.Row
  12. b = Target.Column
  13. Application.EnableEvents = False
  14. If Cells(a, b - 1).Text <> "" Then
  15. Cells(a, b + 6) = (Cells(a, b) - Cells(a, b - 1)) / (Cells(a, b - 1) * 1.187)
  16. Application.EnableEvents = True
  17. Else
  18. Application.EnableEvents = True
  19. Exit Sub
  20. End If


  23. ElseIf Target.Column = 5 And Target.Text <> "" And Target.Row > 4 Then
  24. a = Target.Row
  25. b = Target.Column
  26. Application.EnableEvents = False
  27. If Cells(a, b + 7).Text <> "" Then
  28. Cells(a, b + 1) = (1.187 * Cells(a, b + 7) + 1) * Cells(a, b)
  29. Application.EnableEvents = True
  30. ElseIf Cells(a, b + 1).Text <> "" Then
  31. Cells(a, b + 7) = (Cells(a, b - 1) - Cells(a, b - 2)) / (Cells(a, b - 2) * 1.187)
  32. Application.EnableEvents = True
  33. Else:
  34. Application.EnableEvents = True
  35. Exit Sub
  36. End If
  37. End If
  38. End Sub
复制代码
回复

使用道具 举报

 楼主| 发表于 2013-12-19 09:31 | 显示全部楼层
无聊的疯子 发表于 2013-12-19 09:26
前面烟花说了，你使用了 关闭事件代码，条件不满足时你退出了过程却没有打开事件，这是导致后来事件不能用的 ...

谢谢回复，我在检查一下，第一次使用这个经验欠缺，非常感谢！
回复

使用道具 举报

 楼主| 发表于 2013-12-19 10:01 | 显示全部楼层
无聊的疯子 发表于 2013-12-19 09:26
前面烟花说了，你使用了 关闭事件代码，条件不满足时你退出了过程却没有打开事件，这是导致后来事件不能用的 ...

还是调不好，请您帮我看看。
回复

使用道具 举报

 楼主| 发表于 2013-12-19 11:00 | 显示全部楼层
求高人渡劫~~
回复

使用道具 举报

hwc2ycy
发表于 2013-12-19 18:32 | 显示全部楼层    本楼为最佳答案   
dreamknight0 发表于 2013-12-19 11:00
求高人渡劫~~
  1. Private Sub Worksheet_Change(ByVal Target As Range)
  2.     On Error Resume Next
  3.     Dim rg As Range
  4.     Application.EnableEvents = False
  5.     Application.ScreenUpdating = False

  7.     For Each rg In Target
  8.         With rg
  9.             If .Row > 4 Then
  10.                 Select Case .Column
  11.                     Case 12:
  12.                         '        If Target.Column = 12 And Target.Text <> "" And Target.Row > 4 Then
  13.                         '            a = Target.Row
  14.                         '            b = Target.Column
  15.                         '            Application.EnableEvents = False
  16.                         '            Cells(a, b - 6) = (1.187 * Cells(a, b) + 1) * Cells(a, b - 7)
  17.                         If Len(.Value) Then
  18.                             .Offset(, -6).Value = (1.187 * .Value + 1) * .Offset(, -7).Value
  19.                         End If
  20.                     Case 6:
  21.                         '        ElseIf Target.Column = 6 And Target.Text <> "" And Target.Row > 4 Then
  22.                         '            a = Target.Row
  23.                         '            b = Target.Column
  24.                         '            Application.EnableEvents = False
  25.                         '            If Cells(a, b - 1).Text <> "" Then
  26.                         '                Cells(a, b + 6) = (Cells(a, b) - Cells(a, b - 1)) / (Cells(a, b - 1) * 1.187)
  27.                         If Len(.Offset(, -1).Value) Then
  28.                             .Offset(, 6).Value = .Value - .Offset(, -1).Value / .Offset(, -1).Value * 1.187
  29.                         End If

  31.                     Case 5:
  32.                         '        ElseIf Target.Column = 5 And Target.Text <> "" And Target.Row > 4 Then
  33.                         '            a = Target.Row
  34.                         '            b = Target.Column
  35.                         '            Application.EnableEvents = False
  36.                         '            If Cells(a, b + 7).Text <> "" Then
  37.                         '                Cells(a, b + 1) = (1.187 * Cells(a, b + 7) + 1) * Cells(a, b)
  38.                         '                Application.EnableEvents = True
  39.                         '            ElseIf Cells(a, b + 1).Text <> "" Then
  40.                         '                Cells(a, b + 7) = (Cells(a, b - 1) - Cells(a, b - 2)) / (Cells(a, b - 2) * 1.187)
  41.                         '            Else: Exit Sub
  42.                         '            End If
  43.                         '        End If
  44.                         If Len(.Value) Then
  45.                             If Len(.Offset(, 7).Value) Then
  46.                                 .Offset(, 1).Value = 1.187 * (.Offset(, 7) + 1) * .Value
  47.                             ElseIf Len(.Offset(, 1).Value) Then
  48.                                 .Offset(, 7).Value = .Offset(, -1).Value - .Offset(, -2).Value / (.Offset(, 2).Value * 1.187)
  49.                             End If
  50.                         End If

  52.                 End Select
  53.             End If
  54.         End With
  55.     Next
  56.     Application.EnableEvents = True
  57.     Application.ScreenUpdating = True
  58. End Sub
复制代码
框架就是这样，根据你的改了下。
怎么计算，计算的方法你自己改下。


回复

使用道具 举报

 楼主| 发表于 2013-12-19 22:17 | 显示全部楼层
hwc2ycy 发表于 2013-12-19 18:32
框架就是这样，根据你的改了下。
怎么计算，计算的方法你自己改下。

版主，我修改了下，您看看对不对
  1. Private Sub Worksheet_Change(ByVal Target As Range)
  2.     On Error Resume Next
  3.     Dim rg As Range
  4.     Application.EnableEvents = False
  5.     Application.ScreenUpdating = False

  7.     For Each rg In Target
  8.         With rg
  9.             If rg.Row > 4 Then
  10.                 Select Case rg.Column
  11.                     Case 12:
  12.                         '        If Target.Column = 12 And Target.Text <> "" And Target.Row > 4 Then
  13.                         '            a = Target.Row
  14.                         '            b = Target.Column
  15.                         '            Application.EnableEvents = False
  16.                         '            Cells(a, b - 6) = (1.187 * Cells(a, b) + 1) * Cells(a, b - 7)
  17.                         If Len(rg.Value) <> "" Then
  18.                             Cells.Offset(rg.Row, rg.Column - 6).Value = (1.187 * Cells(rg.Row, rg.Column).Value + 1) * Cells.Offset(rg.Row, rg.Column - 7).Value
  19.                         End If
  20.                     Case 6:
  21.                         '        ElseIf Target.Column = 6 And Target.Text <> "" And Target.Row > 4 Then
  22.                         '            a = Target.Row
  23.                         '            b = Target.Column
  24.                         '            Application.EnableEvents = False
  25.                         '            If Cells(a, b - 1).Text <> "" Then
  26.                         '                Cells(a, b + 6) = (Cells(a, b) - Cells(a, b - 1)) / (Cells(a, b - 1) * 1.187)
  27.                         If Len(Cells.Offset(rg.Row, rg.Column - 1).Value) <> "" Then
  28.                             Cells.Offset(rg.Row, rg.Column + 6).Value = (Cells(rg.Row, rg.Column).Value - Cells.Offset(rg.Row, rg.Column - 1).Value) / Cells.Offset(rg.Row, rg.Column - 1).Value * 1.187
  29.                         End If

  31.                     Case 5:
  32.                         '        ElseIf Target.Column = 5 And Target.Text <> "" And Target.Row > 4 Then
  33.                         '            a = Target.Row
  34.                         '            b = Target.Column
  35.                         '            Application.EnableEvents = False
  36.                         '            If Cells(a, b + 7).Text <> "" Then
  37.                         '                Cells(a, b + 1) = (1.187 * Cells(a, b + 7) + 1) * Cells(a, b)
  38.                         '                Application.EnableEvents = True
  39.                         '            ElseIf Cells(a, b + 1).Text <> "" Then
  40.                         '                Cells(a, b + 7) = (Cells(a, b - 1) - Cells(a, b - 2)) / (Cells(a, b - 2) * 1.187)
  41.                         '            Else: Exit Sub
  42.                         '            End If
  43.                         '        End If
  44.                         If Len(rg.Value) <> "" Then
  45.                             If Len(Cells.Offset(rg.Row, rg.Column + 7).Value) <> "" Then
  46.                                 Cells.Offset(rg.Row, rg.Column + 1).Value = 1.187 * (Cells.Offset(rg.Row, rg.Column + 7) + 1) * rg.Value
  47.                             ElseIf Len(.Offset(, 1).Value) Then
  48.                                 Cells.Offset(rg.Row, rg.Column + 7).Value = (Cells.Offset(rg.Row, rg.Column - 1).Value - Cells.Offset(rg.Row, rg.Column - 2).Value) / (Cells.Offset(rg.Row, rg.Column - 2).Value * 1.187)
  49.                             End If
  50.                         End If

  52.                 End Select
  53.             End If
  54.         End With
  55.     Next
  56.     Application.EnableEvents = True
  57.     Application.ScreenUpdating = True
  58. End Sub
复制代码
试了下，不行，肯定是我哪填错了吧
回复

使用道具 举报

下一页 »
12下一页
返回列表 发新帖
高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 | 注册

本版积分规则

小黑屋|手机版|Archiver|Excel精英培训 ( 豫ICP备11015029号 )

GMT+8, 2024-4-16 12:59 , Processed in 0.359748 second(s), 14 queries , Gzip On, Yac On.

Powered by Discuz! X3.4

Copyright © 2001-2020, Tencent Cloud.

快速回复 返回顶部 返回列表