excel - Excel Macro with IF statement not working

Question

Question about excel macros + VBA.

I have a button that activates a Macro and creates a new line of data in a new sheet. The thing is that I want it to create a new line only if the finish date is more recent than the start date. I tried calling the Macro inside the VBA code but nothing happens.

If I try using the IF inside the macro, it just gives the error in the Msgbox, but no line is added even if conditions are met.

The Macro is working just fine, the IF statement does nothing as is.

The code I tried using:

Sub ButtonStuff()

If Range("H13").Value > Range("H7").Value Then

Call Macro15

Else

If Range("H13").Value <= Range("H7").Value Then

    MsgBox "End date cannot be previous to Start date"

    End If

    End If

End Sub

 

 

Sub Macro15() 
'
' Macro15 Macro
'
'

    Range("H5,H7,H9,H11,H13").Select

    Range("H13").Activate

    Selection.Copy

    Sheets("Data").Select

    Columns("A:A").Select

    Selection.End(xlDown).Select

    Selection.End(xlDown).Select

    Selection.End(xlUp).Select

    ActiveCell.Offset(1, 0).Select

    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _

        False, Transpose:=True

    Sheets("Incident Report").Select

    Range("H5,H9,H11").Select

    Range("H11").Activate

    Application.CutCopyMode = False

    Selection.ClearContents

    Range("N8").Select

    Selection.Copy

    Range("H7").Select

    Range("H7").Activate

    ActiveSheet.Paste

    Range("N9").Select

    Selection.Copy

    Range("H13").Select

    Range("H13").Activate

    ActiveSheet.Paste

    Range("H5").Select

 

End Sub

Thanks!

标签： excelvba

Answer 1

如果您取出所有Select/Activate行，这几乎就是您的宏所做的，这是不必要的。缺少的是代码中大多数范围的工作表名称。这可能就是为什么它没有按照您的预期进行，因为您将影响当时恰好处于活动状态的任何工作表，而不一定是您想要的工作表。这应该可以帮助您查看要更改的工作表。

Sub ButtonStuff()
'
'
'
If Range("H13").Value > Range("H7").Value Then

    Range("H13").Copy

    Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteAll

    Sheets("Incident Report").Range("H11").ClearContents

    Range("H7") = Range("N8")

    Range("H13") = Range("N9")
    
    Else
        
        MsgBox "End date cannot be previous to Start date"
        
End If


End Sub