Excel information

Most Popular Excel Questions

Using an If Statement in VBA

Posted by davesexcel on March 29, 2012 at 7:15 PM

You have an If statement in Cell B1 that goes like this.


In VBA the code would be.


Sub VBA_If_Statement()

    Dim Ar As Range

    Set Ar = Range("A1")

    Range("B1") = IIf(Ar = 20, "Yep", "Nope")

End Sub


An If statement in VBA is IIf


This is the long version of the same results

Sub VBA_If_Statement_Long_Version()

    Dim Ar As Range

    Set Ar = Range("A1")

    If Ar = 20 Then

        Range("B1") = "Yep"

    Else: Range("B1") = "Nope"

    End If

End Sub



You can use a Worksheet_Change event to make B1 change when A1 changes

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address <> "$A$1" Then Exit Sub

    Range("B1") = IIf(Target = 20, "Yep", "Nope")

End Sub



Categories: None

Post a Comment


Oops, you forgot something.


The words you entered did not match the given text. Please try again.

You must be a member to comment on this page. Sign In or Register