CODEWITHSUNDEEP
vbaexcelif-statement
joekeny
joekeny

Reputation: 15

If Statements in Macros in Excel

I originally had a macro written that would clear the specified cells in an excel sheet but I want to be able to only clear certain rows if I need to. This is what my macro looks like currently.

Sub Rectangle1_Click()
response = MsgBox("Are You Sure?", vbYesNo

If response = vbNo Then
    MsgBox ("OK")
    Exit Sub
End If
    Range("A4:C30").Select
    Selection.ClearContents
    Range("G4:H30").Select
    Selection.ClearContents
End Sub

I'm wanting to make it look something more like this though

Sub Rectangle1_Click()
response = MsgBox("Are You Sure?", vbYesNo)

If response = vbNo Then
    MsgBox ("OK")
    Exit Sub
End If
If I4 = 1 then
    Range("A4:C4","G4:H4").Select
    Selection.ClearContents
    Exit Sub
End If
End Sub

And then repeat that code for rows 5-30, when I run the code with multiple if statements it doesn't appear to do anything after the dialogue box pops up. Is there something else that I need to change?

Upvotes: 1

Views: 159

Answers (3)

Olly
Olly

Reputation: 7891

You can loop through rows 4 -30:

Sub Rectangle1_Click()
    Dim lRow As Long
    If MsgBox("Are you sure?", vbYesNo + vbQuestion) = vbYes Then
        For lRow = 4 To 30
            With ActiveSheet
                If .Cells(lRow, "I").Value = 1 Then
                    .Cells(lRow, "A").Resize(1, 3).ClearContents
                    .Cells(lRow, "G").Resize(1, 2).ClearContents
                End If
            End With
        Next lRow
    Else
        MsgBox "OK", vbOKOnly + vbInformation
    End If
End Sub

Upvotes: 2

newacc2240
newacc2240

Reputation: 1425

I suggest that you could add a line Option Explicit to make sure all the variables in your code are well defined.

If you want to access a range, use a range object.

Option Explicit

Sub Rectangle1_Click()
    Dim response As Variant
    response = MsgBox("Are You Sure?", vbYesNo)

    If response = vbNo Then
        MsgBox ("OK")
        Exit Sub
    End If

    Dim rngI As Range
    Dim cel As Range
    Set rngI = Range("I4:I30")

    For Each cel In rngI
        If cel.value = 1 Then
            cel.Offset(0, -8).Resize(1, 3).ClearContents 'A:C
            cel.Offset(0, -2).Resize(1, 2).ClearContents 'G:H
        End If
    Next cel
End Sub

Upvotes: 0

urdearboy
urdearboy

Reputation: 14580

No .Select required. Also, you do not need to test for vbNo. Just code for vbYes and vbNo does nothing by default

Sub Rectangle1_Click()

response = MsgBox("Are You Sure?", vbYesNo)

If response = vbYes and Range("I4") = 1 Then
    Range("A4:C4","G4:H4").ClearContents
End If

End Sub

If you want to display a message box when they click no you can do something like so (although this seems redundant since the user already said no). If you place the vbNo at the end, you can avoid Exit Sub since this will happen during the natural flow

Sub Rectangle1_Click()

response = MsgBox("Are You Sure?", vbYesNo)

If response = vbYes and Range("I4") = 1 Then
    Range("A4:C4","G4:H4").ClearContents
ElseIf response = vbNo
    Msgbox "OK"
End If

End Sub

Upvotes: 3

Related Questions