davesexcel.com

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.

=IF(A1=20,"Yep","Nope")

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!

Oops, you forgot something.

Oops!

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

0 Comments