工作表更改时变量未存储在代码中 [英] Variable not being stored in the code when sheet changes

问题描述

我对编码还很陌生，并且遇到了一个非常琐碎的问题.当我在"Trade_Sheet"选项卡中逐行运行代码时，将变量Date_range正确地用作先前复制的日期，并在7天后进行了更新.但是，当我在宏所在的主选项卡"Share_Calc_Tab"中运行宏时，就会出现问题.如果这样做，似乎变量Date_range设置为0，并且在执行其余操作时，日期将丢失.

I'm quite new to coding and have encountered a probably very trivial issue. When I run the code line by line in the 'Trade_Sheet' tab, the variable Date_range is picked up correctly as a date previously copied, and updated for 7 days later. However the problem comes when I run the macro in the main tab 'Share_Calc_Tab' where the macro is situated. If I do so, it seems like the variable Date_range sets to 0, and while the rest of the operation is performed, the date will be missing.

代码如下:

Sub Audit_Trade()

    Dim Trade_Sheet As Worksheet
    Dim Share_Calc_Tab As Worksheet
    Dim lastrow As Long
    Dim Date_range As Date

    Set Trade_Sheet = ThisWorkbook.Worksheets("Trades")
    Set Share_Calc_Tab = ThisWorkbook.Worksheets("End Share Calc (ESC) GLOBAL")

    Application.ScreenUpdating = False

    With Trade_Sheet

        lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
        Date_range = Cells(lastrow, 1).Offset(-27, 0).Value
        Cells(lastrow + 2, 1).Value = Date_range + 7

    End With

    Share_Calc_Tab.Activate
    Range("Trade_Instruction_Daily").Copy
    Trade_Sheet.Activate
    Cells(lastrow + 3, 1).Select
    Selection.PasteSpecial Paste:=xlPasteFormats
    Selection.PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False

    Share_Calc_Tab.Activate
    Range("B22").Select

    Application.ScreenUpdating = True

End Sub

任何帮助将不胜感激.谢谢大家！

Any help will be much appreciated. Thanks all!

推荐答案

问题是您没有正确定义工作表.在这里查看要点(点):

The problem is that you do not define the worksheet correctly. See the points(dots) here:

With Trade_Sheet
    lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
    Date_range = .Cells(lastrow, 1).Offset(-27, 0).Value
    .Cells(lastrow + 2, 1).Value = Date_range + 7
End With

在您的代码中，您缺少其中两个:

In your code, you are missing 2 of them:

因此，Cells()是指ActiveSheet，而不是Trade_Sheet.通常，在Activate和Select被认为是不良做法. > vba ，因此最好避免使用它们:

Thus, the Cells() refers to the ActiveSheet, and not to the Trade_Sheet. In general, Activate and Select are considered a bad practice in vba, thus it is a good idea avoid them:

• 如何避免在Excel VBA中使用选择" .

对于有价值的东西，这可能是 vba ，因此您可以为自己感到骄傲.我想几乎每个VBA人员都至少经历过一次.

For what is worth, this is probably the most common error in vba, thus you can be proud of yourself for reaching it. I guess that almost every VBA person has experienced it at least once.

这篇关于工作表更改时变量未存储在代码中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！