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

[gertie193]

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!

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>

 

[Fluff]

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

    Select Case Left(Range("A2"), 1)
        Case "N"
            '
            '
        Case "E"
            '
            '
    End Select

 

[gertie193]

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

    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.

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

 

[Fluff]

What you've got is fine, one possible change is to add the line in red

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

 

[mikerickson]

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

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

 

[gertie193]

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!

 

[gertie193]

Thank you for your help, Fluff.

 

[Fluff]

Glad to help & thanks for the feedback