Excel VBA - Use Consecutive IF Statements or Use If/ElseIf

gertie193

Board Regular
Joined
Feb 28, 2008
Messages
82
Hello,

I'm trying to create code so that when a user enters an ID in A2, the city will autopopulate in B2, and the person's name associated with that ID will autopopulate in C2.

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]ID (A2)[/TD]
[TD]City (B2)[/TD]
[TD]Person (C2)[/TD]
[/TR]
[TR]
[TD]N11111[/TD]
[TD]Airville[/TD]
[TD]John Doe[/TD]
[/TR]
</tbody>[/TABLE]

?? Does it matter whether I list multiple/consecutive IF statements or should I use IF/ELSEIF statements?

I don't see how listing consecutive IF statements will be a problem with my code and would like to see if someone can tell me what would be the correct way to use IF statements re: the code below.

I appreciate your help!


Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim wkbk As Workbook
Dim wksht As Worksheet
Dim node As String


Set wkbk = ActiveWorkbook
Set wksht = ActiveSheet


node = Range("A2").Value

[B]'Clear B2 if A2 is blank[/B]
    If node = "" Then
        Range("B2").Value = ""
    End If


[B]'MAIN CODE 
' If value entered in A2 starts with N then add Airville to B2  [/B]
    If node Like "N*" Then
        Range("B2").Value = "Airville
    End If
 [B]' If value entered in A2 starts with E then add Aleive to B2  [/B]  
    If node Like "E*" Then
        Range("B2").Value = "Aleive"
    End If

[B]'Etc., etc. 31 more times
[/B]End Sub

<tbody style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; font-size: inherit; line-height: inherit; font-family: inherit; vertical-align: baseline;">
[TD="class: votecell"]

[/TD]
[TD="class: answercell"][/TD]

</tbody>
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
For that many Ifs I'd personally creat a sheet with the various values & then do a lookup.
Failing that I'd tend to use a select case statement, on the lines of
Code:
    Select Case Left(Range("A2"), 1)
        Case "N"
            '
            '
        Case "E"
            '
            '
    End Select
 
Upvote 0
For that many Ifs I'd personally creat a sheet with the various values & then do a lookup.
Failing that I'd tend to use a select case statement, on the lines of
Code:
    Select Case Left(Range("A2"), 1)
        Case "N"
            '
            '
        Case "E"
            '
            '
    End Select

Thank you, Fluff.

I corrected the code.

1) Will I run into trouble where I have 2 different SELECT CASE statements, one for LEFT get the first (1) character and one for LEFT get the first 2 characters?

2) Also, is it okay to have the IF statement to clear the B2 if A2 is blank where it is located in the code?

Thank you for you or anyone's help.

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)'ID City Person code


Dim wkbk As Workbook
Dim wksht As Worksheet
Dim rID As Range
Dim rCity As Range


Set wkbk = ActiveWorkbook
Set wksht = ActiveSheet


Set rID = Range("A2")
Set rCity = Range("B2")


'Clear B2 if A2 blank - [B]<=====Is it okay to have this here?[/B]
If rID.Value = "" Then
    rCity.Value = ""
End If


'For IDs starting with 1 letter 
Select Case Left(rID, 1) 'Case sensitive - [B]<=====1st Select Case[/B]
    Case "N"
        rCity.Value = "Airville"
    Case "E"
        rCity.Value = "Aleive"
    Case "X"
        rCity.Value = "Hubble"
End Select


'For IDs starting with 2 letters
Select Case Left(rID, 2) - [B]<=====2nd Select Case[/B]
    Case "GP"
        rCity.Value = "Greens"
   Case "N1"
        rCity.Value = "Airtime"
End Select
    
End Sub
 
Last edited:
Upvote 0
What you've got is fine, one possible change is to add the line in red
Code:
If rID.Value = "" Then
    rCity.Value = ""
[COLOR=#ff0000]    Exit Sub[/COLOR]
End If
If rID is blank there is no need to continue through the select case statements, as they will all be false
 
Upvote 0
One difference between consecutive If vs. If ElseIf is that in the consecutive if situation an early IF can change the results of a later If

Put "big cat" in A1 and compare these two routines

Code:
With Range("A1")
    If .Value Like "*cat*" then 
        .Value = "not dog"
    End If
    If .Value Like "*dog*" then 
        .Value = "not cat"
    End If
End With

' vs.

With Range("A1")
    If .Value Like "*cat*" Then
        .Value = "not dog"
    ElseIf .Value Like "*dog*"
        .Value = "not cat"
    End If
End With
 
Last edited:
Upvote 0
One difference between consecutive If vs. If ElseIf is that in the consecutive if situation an early IF can change the results of a later If

Put "big cat" in A1 and compare these two routines...

Very helpful explanation. I'm learning VBA so thank you!
 
Upvote 0
Glad to help & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,223,888
Messages
6,175,207
Members
452,618
Latest member
Tam84

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top