IF statements in VBA.

[rower15]

Greetings all... I am a "newbie" and this is my first post. Actually, it's my first foray into VB as well. Here's the question...

Let's say I have a cell (A15) into which I can enter one of four values (either UNK, NA, YES, or NO).

Depending on what is entered into A15, I want to populate cells D15, D16, D17, D18.

If I enter UNK into A15, then I want to see UNK in D15-D18.
If I enter NA into A15, then I want to see NA in D15-D18.
If I enter NO into A15, then I want to see NA in D15 and leave D16-D18 blank.
If I enter YES into A15, then I want to leave D15-D18 blank.


As a second part to this question, in VB, can I use cell names as the variables in my code?


Many, many thanks!

- Evan

 

[just_jon]

Welcome to MrExcel -- you could do this via formula, but if it's VBA you have your heart set on then a change event macro might work -

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)
<SPAN style="color:#00007F">If</SPAN> Intersect(Target, [A15]) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN>
Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN>
<SPAN style="color:#00007F">With</SPAN> Range("D15:D18")
    <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> UCase(Target.Value)
        <SPAN style="color:#00007F">Case</SPAN> "UNK", "NA", "NO", "YES"
            .Value = Target.Value
        Case <SPAN style="color:#00007F">Else</SPAN>
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN>
Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

It goes in the worksheet module of the sheet containing A15

Post back if you have problems/questions.

EDIT Care to expand on your 2nd item?

 

[Joe Was]

To use a Range Name just replace the Range with your Range Name!


Private Sub Worksheet_Change(ByVal Target As Range)
'Sheet module code, like: Sheet1.

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

Select Case UCase([A15].Value)

Case "UNK"
[D15:D18].Value = "UNK"

Case "NA"
[D15:D18].Value = xlNull

Case "NO"
[D15].Value = "NA"
[D16:D18].Value = xlNull

Case "YES"
[D15:D18].Value = xlNull

Case ""
[D15:D18].Value = xlNull

Case xlNull
[D15:D18].Value = xlNull

Case Else
MsgBox "You should only enter:" & vbLf & vbLf & _
"UNK, NA, NO or YES"

End Select
End Sub