If else statement (basic)

Question

Need help to create If else statement. If sum all field 9 = sum all field 10, delete field 10 else highlight field 10 entire column. Note: Field 9 is Column "I", Field 10 is Column "J"

Sub Macro1()

        ActiveSheet.Range("A1").AutoFilter field:=9, Criteria1:="<>"
        ActiveSheet.Range("A1").AutoFilter field:=10, Criteria1:="<>"

 'IF Formula
IF  Activesheet.Range ("I") = Activesheet.Range ("J"),     
        Columns("J:J").Select
        Selection.Delete Shift:=xlToLeft
Else 
     Do nothing
 'If Formula ends
    End Sub

You write "If sum all field 9 = sum all field 10" so you need IF application.worksheetfunction.sum("I:I") = application.worksheetfunction.sum("J:J") then — Harassed Dad, Oct 25 '18 at 08:27
Should be THEN instead of a comma at the end of your line starting IF — OwlsSleeping, Oct 25 '18 at 08:44

Pierre44 · Answer 1 · 2018-10-25T12:13:56.793

A few mistakes in your code:

The basics of IF:

You need to write

If *Condition* Then
*Do smth*
Else 
*Do Smth*
End If

You can take the Else away if you don't do anything.

Sum in a filter:

As Harassed Dad wrote in his comment: The condition should be written like this :

Application.worksheetfunction.sum("I:I") = Application.worksheetfunction.sum("J:J")

Do not use Select:

Using select makes your code slow and harder to read. The first thing to optimize is to avoid using select. In this link you will find a great answer to avoid using Select: How to avoid using Select in Excel VBA

Finally, your code should look rather like this:

Sub Macro1()

 ActiveSheet.Range("A1").AutoFilter field:=9, Criteria1:="<>"
 ActiveSheet.Range("A1").AutoFilter field:=10, Criteria1:="<>"

 'IF Formula
     IF  Application.worksheetfunction.sum(Range("I:I").SpecialCells(xlCellTypeVisible)) = _
Application.worksheetfunction.sum(Range("J:J").SpecialCells(xlCellTypeVisible)) Then    
            Columns("J:J").Delete Shift:=xlToLeft 'instead of .Select and Selection.
         End If
     'If Formula ends
    End Sub

I tried input the code, it prompt run-time error '1004': Unable to get the Sum property of the WorksheetFunction Class. — chee seng ng, Oct 25 '18 at 11:00
Because Sum("A:A") doesn t work. You need to enter a range inside. I corrected it sorry. Now I tested the code, it should work — Pierre44, Oct 25 '18 at 12:15
It works as per what I needed. Really appreciate your assistance on this. Have a great day ahead. — chee seng ng, Oct 29 '18 at 06:12

If else statement (basic)

1 Answers1