如何重置一个变量，让另一个If语句可以使用它?

So I have data from every month of the year, with each month split into two 15 day worksheets.

所以我有每年每个月的数据，每个月分成两个15天的工作表。

I'm trying to sum the two worksheets and then put the value into a compilation worksheet then moving on to the next month.

我试图将这两个工作表相加，然后将值放入一个编译工作表中，然后继续下个月。

As it stands it just adds everything starting from January, I've tried resetting it to 0 but that just results in everything being zero.

从1月开始，它会把所有的都加进来，我试过把它重置为0，但结果是所有的都是0。

I could easily just apply a formula at the end of the code that applies a subtraction of the previous cells but I figured it'd be better to learn how to do it in VBA. Perhaps I'm just not using the right words in google.

我可以很容易地在代码的末尾应用一个公式，它对前面的单元格进行减法，但是我认为最好在VBA中学习如何做。也许我只是没有在谷歌中使用合适的词。

Thanks for the help.

谢谢你的帮助。

Sub Sum()

Dim DestSh As Worksheet
Dim Sh As Worksheet
Dim lastcol As Long
Dim lastrow As Long
Dim destrow As Long
Dim prevval As Double
Dim nextval As Double

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    Application.DisplayAlerts = False

    'Deletes Compilation worksheet if it exists
    On Error Resume Next
    ActiveWorkbook.Worksheets("Compilation").Delete
    On Error GoTo 0
    Application.DisplayAlerts = True

    'Add a worksheet with the name "Compilation"
    Set DestSh = ActiveWorkbook.Worksheets.Add

    'loop through all worksheets
    For Each Sh In ActiveWorkbook.Worksheets

        'Loop through all worksheets except Compilation
        If IsError(Application.Match(Sh.Name, _
                                     Array(DestSh.Name), 0)) Then

        'Check for the last column on row 6
        lastcol = Sh.Cells(6, Columns.Count).End(xlToLeft).Column
        'Check for the last row that the last column above has
        lastrow = Sh.Cells(Rows.Count, lastcol).End(xlUp).Row

            'Checks if Cell B3, contains JAN and then enters the contents to DestSh
            If Sh.Cells(3, 2).Value Like "*JAN*" Then
                destrow = DestSh.Cells(3, 2).End(xlUp).Row
                    prevval = prevval
                    nextval = prevval + Sh.Cells(lastrow, lastcol).Value
                DestSh.Cells(destrow + 2, 2).Value = nextval
                    prevval = nextval
            End If
            If Sh.Cells(3, 2).Value Like "*FEB*" Then
                destrow = DestSh.Cells(3, 3).End(xlUp).Row
                    prevval = prevval
                    nextval = prevval + Sh.Cells(lastrow, lastcol).Value
                DestSh.Cells(destrow + 2, 3).Value = nextval
                    prevval = nextval
            End If
        End If
    Next
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
End Sub

1 个解决方案

#1

Well in light of my novice myopia I didn't quite see the obvious. Thanks @TimWilliams

根据我的新近视眼，我没有看到明显的。由于@TimWilliams

Changed the formatting to the if and then statements to this:

将格式改为if，然后将语句改为:

If Sh.Cells(3, 2).Value Like "*JAN*" Then
     destrow = DestSh.Cells(3, 2).Row
     DestSh.Cells(destrow, 2).Value = DestSh.Cells(destrow, 2).Value _
        + Sh.Cells(lastrow, lastcol).Value
End If

#1