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!

Note that the `If Range("H13").Value <= Range("H7").Value Then` is redundant - remove that and an `End If`. — BigBen, Aug 18 '21 at 15:55
Next step: rewrite [avoiding Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). — BigBen, Aug 18 '21 at 16:02
If you see the message box, it seems that content of H13 *is less than* content of H7 ... — FunThomas, Aug 18 '21 at 16:08
You can use `Data Validation` to avoid a value being entered in the first place — Darrell H, Aug 18 '21 at 16:48

score 0 · Answer 1 · answered Aug 18 '21 at 21:26

0

This is pretty much what your macros do if you take out all the Select/Activate lines, which aren't necessary. What is missing is the sheet names for most of the ranges in the code. That may be why it isn't doing what you expect, because you will effect whatever sheet happens to be active at the time and not necessarily the one you intended. This should help you see which sheets you intended to change.

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

answered Aug 18 '21 at 21:26

Darrell H

1,876
1
9
14

I used data validation as suggested and It gives a warning, even though it still allows for the button to be pressed. I tried using the code you sent, and it's not working for me. Maybe something to do with all the macros the file has. Since the file was not done by me and I'm just trying to help someone else correcting but I think that the file should be done with less macros to be more functional. For now it has to be like it is. Anyways, thanks for your help! :) – Ana Modesto Aug 19 '21 at 12:21
Using data validation, if you set the validation criteria to be a date greater than the other cell, it will not allow you to proceed until that condition is met. This code will not work unless you qualify the ranges, i.e. define the sheets that the ranges apply to. – Darrell H Aug 19 '21 at 13:12

Excel Macro with IF statement not working

1 Answers1