How do I add an else if to this VBA I am working on?

I

ILMWT

New Member
Joined
Dec 9, 2014
Messages
35
So I am looking in column I for partial texts. For instance Column I can say:

"Health Care, Doctor J"
"Formula 1 Driver Ayrton Senna"
"Dog Toys"

My VBA is looking for a specific partial string in column I and then putting another specific text in Column L. So the one I have written here says if the words health care are in a cell in column I, then the corresponding cell in Column L will now say "Yes, Healthcare" in bold letters.

For Each c In Range("I3:I100")
If InStr(1, LCase(c.Value), "health care") > 0 Then
With Cells(c.Row, lastcol)
.Value = "Yes, Healthcare"
.Font.Bold = True
End With
Else
With Cells(c.Row, lastcol)
.Font.Bold = False
.ClearContents
End With
End If

What I want to do is add another part to this vba that looks for the words "Formula 1" and will write, "Yes, Formula 1" in the corresponding cell in column L. Exactly the same as the healthcare one above, just with these new parameters. How would I go about doing this?
 
Sort by date Sort by votes
Try it like this:

If you use indention it can be easier to read code. Note how the For Next construct is on one indent level while the If statement and with statement are on another

Code: 
[COLOR=#0000ff]For Each[/COLOR] c [COLOR=#0000ff]In[/COLOR] Range("I3:I100")
   [COLOR=#0000ff] If [/COLOR]InStr(1, LCase(c.Value), "health care") > 0 [COLOR=#0000ff]Then[/COLOR]
      [COLOR=#0000ff]  With[/COLOR] Cells(c.Row, lastcol)
            .Value = "Yes, Healthcare"
            .Font.Bold = [COLOR=#0000ff]True[/COLOR]
       [COLOR=#0000ff] End With[/COLOR]
 [COLOR=#0000ff]   ElseIf[/COLOR] InStr(1, LCase(c.Value), "Financial") > 0[COLOR=#0000ff] Then[/COLOR]
        [COLOR=#0000ff]With[/COLOR] Cells(c.Row, lastcol)
            .Font.Bold = [COLOR=#0000ff]False[/COLOR]
            .ClearContents
[COLOR=#0000ff]        End With[/COLOR]
[COLOR=#0000ff]    Else[/COLOR]
[COLOR=#008000]        'Something Else[/COLOR]
[COLOR=#0000ff]    End If[/COLOR]
[COLOR=#0000ff]Next[/COLOR]
 
Last edited:
Upvote 0
mrmmickle1 said:
Try it like this:

If you use indention it can be easier to read code. Note how the For Next construct is on one indent level while the If statement and with statement are on another

Code: 
[COLOR=#0000ff]For Each[/COLOR] c [COLOR=#0000ff]In[/COLOR] Range("I3:I100")
   [COLOR=#0000ff] If [/COLOR]InStr(1, LCase(c.Value), "health care") > 0 [COLOR=#0000ff]Then[/COLOR]
      [COLOR=#0000ff]  With[/COLOR] Cells(c.Row, lastcol)
            .Value = "Yes, Healthcare"
            .Font.Bold = [COLOR=#0000ff]True[/COLOR]
       [COLOR=#0000ff] End With[/COLOR]
 [COLOR=#0000ff]   ElseIf[/COLOR] InStr(1, LCase(c.Value), "Financial") > 0[COLOR=#0000ff] Then[/COLOR]
        [COLOR=#0000ff]With[/COLOR] Cells(c.Row, lastcol)
            .Font.Bold = [COLOR=#0000ff]False[/COLOR]
            .ClearContents
[COLOR=#0000ff]        End With[/COLOR]
[COLOR=#0000ff]    Else[/COLOR]
[COLOR=#008000]        'Something Else[/COLOR]
[COLOR=#0000ff]    End If[/COLOR]
[COLOR=#0000ff]Next[/COLOR]
Click to expand...


So this? Spacing on here is not working for me no matter how I format it for some reason...

For Each c In Range("I2:I600")
If InStr(1, LCase(c.Value), "Health Net") > 0 Then
With Cells(c.Row, lastcol)
.Value = "Health Care"
.Font.Bold = True
End With
ElseIf InStr(1, LCase(c.Value), "Regence") > 0 Then
With Cells(c.Row, lastcol)
.Value = "BCBS"
.Font.Bold = True
End With
ElseIf InStr(1, LCase(c.Value), "BCBS") > 0 Then
With Cells(c.Row, lastcol)
.Value = "BCBS"
.Font.Bold = True
End With
ElseIf InStr(1, LCase(c.Value), "Blue") > 0 Then
With Cells(c.Row, lastcol)
.Value = "BCBS"
.Font.Bold = True
End With
ElseIf InStr(1, LCase(c.Value), "Providence") > 0 Then
With Cells(c.Row, lastcol)
.Value = "Providence"
.Font.Bold = True
End With
ElseIf InStr(1, LCase(c.Value), "Moda") > 0 Then
With Cells(c.Row, lastcol)
.Value = "Moda"
.Font.Bold = True
End With
ElseIf InStr(1, LCase(c.Value), "Care Oregon") > 0 Then
With Cells(c.Row, lastcol)
.Value = "Care Oregon"
.Font.Bold = True
End With
With Cells(c.Row, lastcol)
.Font.Bold = False
.ClearContents
End With
End If
Next
 
Upvote 0
Do this to preserve formatting when posting:
Put "["code"]" before your code, and "["/code"]" after your code, without the quote marks.
Code: 
paste your code here
 
Last edited:
Upvote 0
Rich (BB code): 
 For Each c In Range("I2:I600") 
ElseIf InStr(1, LCase(c.Value), "Regence") > 0 Then
        With Cells(c.Row, lastcol)
            .Value = "BCBS"
            .Font.Bold = True
        End With
    ElseIf InStr(1, LCase(c.Value), "BCBS") > 0 Then
        With Cells(c.Row, lastcol)
            .Value = "BCBS"
            .Font.Bold = True
        End With
    ElseIf InStr(1, LCase(c.Value), "Blue") > 0 Then
        With Cells(c.Row, lastcol)
            .Value = "BCBS"
            .Font.Bold = True
        End With
    ElseIf InStr(1, LCase(c.Value), "Providence") > 0 Then
        With Cells(c.Row, lastcol)
            .Value = "Providence"
            .Font.Bold = True
        End With
    ElseIf InStr(1, LCase(c.Value), "Moda") > 0 Then
        With Cells(c.Row, lastcol)
            .Value = "Moda"
            .Font.Bold = True
        End With
    ElseIf InStr(1, LCase(c.Value), "Texas Care") > 0 Then
        With Cells(c.Row, lastcol)
            .Value = "Texas Care"
            .Font.Bold = True
        End With

Ah got it. So why is this not working? When I try to run it, none of them work?
 
Upvote 0
No / for the one at the beginning.
 
Upvote 0
Try stepping through the code using F8 or putting in some Debug.Print statements to see if the value is what you expect....

Code: 
 [COLOR=#0000ff]For Each[/COLOR] c [COLOR=#0000ff]In [/COLOR]Range("I2:I600") 
   [COLOR=#0000ff] If[/COLOR] InStr(1, LCase(c.Value), "Regence") > 0[COLOR=#0000ff] Then[/COLOR]
       [COLOR=#0000ff] Debug.Print [/COLOR]"Regence: " & c.Value
        [COLOR=#0000ff]With [/COLOR]Cells(c.Row, lastcol)
            .Value = "BCBS"
            .Font.Bold = [COLOR=#0000ff]True[/COLOR]
     [COLOR=#0000ff]   End With[/COLOR]
[COLOR=#0000ff]    ElseIf[/COLOR] InStr(1, LCase(c.Value), "BCBS") > 0 [COLOR=#0000ff]Then[/COLOR]
        [COLOR=#0000ff]Debug.Print[/COLOR] "BCBS: " & c.Value
        [COLOR=#0000ff]With[/COLOR] Cells(c.Row, lastcol)
            .Value = "BCBS"
            .Font.Bold =[COLOR=#0000ff] True[/COLOR]
    [COLOR=#0000ff]    End With[/COLOR]
[COLOR=#0000ff]    ElseIf [/COLOR]InStr(1, LCase(c.Value), "Blue") > 0 [COLOR=#0000ff]Then[/COLOR]
        [COLOR=#0000ff]Debug.Print [/COLOR]"Blue: " & c.Value
    [COLOR=#0000ff]    With [/COLOR]Cells(c.Row, lastcol)
            .Value = "BCBS"
            .Font.Bold = [COLOR=#0000ff]True[/COLOR]
      [COLOR=#0000ff]  End With[/COLOR]
  [COLOR=#0000ff]  ElseIf[/COLOR] InStr(1, LCase(c.Value), "Providence") > 0 [COLOR=#0000ff]Then[/COLOR]
        [COLOR=#0000ff]Debug.Print[/COLOR] "Providence: " & c.Value
  [COLOR=#0000ff]      With [/COLOR]Cells(c.Row, lastcol)
            .Value = "Providence"
            .Font.Bold = [COLOR=#0000ff]True[/COLOR]
    [COLOR=#0000ff]    End With[/COLOR]
   [COLOR=#0000ff] ElseIf[/COLOR] InStr(1, LCase(c.Value), "Moda") > 0[COLOR=#0000ff] Then[/COLOR]
      [COLOR=#0000ff]  Debug.Print[/COLOR] "Moda: " & c.Value
     [COLOR=#0000ff]   With[/COLOR] Cells(c.Row, lastcol)
            .Value = "Moda"
            .Font.Bold = [COLOR=#0000ff]True[/COLOR]
        [COLOR=#0000ff]End With[/COLOR]
[COLOR=#0000ff]    Else [/COLOR]
     [COLOR=#0000ff]   Debug.Print [/COLOR]"Else: " & c.Value
      [COLOR=#0000ff]  With[/COLOR] Cells(c.Row, lastcol)
            .Value = "Texas Care"
            .Font.Bold = T[COLOR=#0000ff]rue[/COLOR]
     [COLOR=#0000ff]   End With
    End If[/COLOR]
[COLOR=#0000ff] Next[/COLOR]

Use Ctrl + G to display the Immediate Window in the VBE to view the "printed" statements
 
Last edited:
Upvote 0
All that one did was put Texas Care in every cell on the last column. Did I miss something on the code?
 
Upvote 0
What are the values in column I?

You can try debugging the code by typing a statement in the immediate window like this? ----> Mid Procedure and see what the value is....

Code: 
?[COLOR=#333333]InStr(1, LCase(c.Value), "Regence")[/COLOR]

Is it greater then 0?? Is it something other than you expect?

You could also change your code like this..... Then you can see what row the results occur at:

Code: 
[COLOR=#0000ff]For [/COLOR]c = 2 [COLOR=#0000ff]To [/COLOR]600
  [COLOR=#0000ff]  If[/COLOR] InStr(1, LCase(cells(c, "I"),"Regence") > 0 [COLOR=#0000ff]Then[/COLOR]
        [COLOR=#0000ff]Debug.Print[/COLOR] "Regence: " & c
       [COLOR=#0000ff] With [/COLOR]Cells(c, lastcol)
            .Value = "BCBS"
            .Font.Bold = [COLOR=#0000ff]True[/COLOR]
   [COLOR=#0000ff]     End With[/COLOR]
    [COLOR=#0000ff]ElseIf [/COLOR]InStr(1, LCase(cells(c, "I"), "BCBS") > 0 [COLOR=#0000ff]Then[/COLOR]
[COLOR=#0000ff]        Debug.Print[/COLOR] "BCBS: " & c
   [COLOR=#0000ff]     With [/COLOR]Cells(c, lastcol)
            .Value = "BCBS"
            .Font.Bold = [COLOR=#0000ff]True[/COLOR]
 [COLOR=#0000ff]       End With
    ElseIf[/COLOR] InStr(1, LCase(cells(c, "I"), "Blue") > 0[COLOR=#0000ff] Then[/COLOR]
     [COLOR=#0000ff]   Debug.Print [/COLOR]"Blue: " & c
        [COLOR=#0000ff]With [/COLOR]Cells(c, lastcol)
            .Value = "BCBS"
            .Font.Bold = True
 [COLOR=#0000ff]       End With[/COLOR]
  [COLOR=#0000ff]  ElseIf[/COLOR] InStr(1, LCase(cells(c, "I"), "Providence") > 0[COLOR=#0000ff] Then[/COLOR]
       [COLOR=#0000ff] Debug.Print[/COLOR] "Providence: " & c
        [COLOR=#0000ff]With[/COLOR] Cells(c, lastcol)
            .Value = "Providence"
            .Font.Bold = [COLOR=#0000ff]True[/COLOR]
 [COLOR=#0000ff]       End With[/COLOR]
   [COLOR=#0000ff] ElseIf [/COLOR]InStr(1, LCase(cells(c, "I"), "Moda") > 0 [COLOR=#0000ff]Then[/COLOR]
       [COLOR=#0000ff] Debug.Print [/COLOR]"Moda: " & c
        [COLOR=#0000ff]With [/COLOR]Cells(c, lastcol)
            .Value = "Moda"
            .Font.Bold = [COLOR=#0000ff]True[/COLOR]
        [COLOR=#0000ff]End With[/COLOR]
  [COLOR=#0000ff]  Else [/COLOR]
        [COLOR=#0000ff]Debug.Print [/COLOR]"Else: " & c
        [COLOR=#0000ff]With [/COLOR]Cells(c, lastcol)
            .Value = "Texas Care"
            .Font.Bold = [COLOR=#0000ff]True[/COLOR]
  [COLOR=#0000ff]      End With
    End If[/COLOR]
[COLOR=#0000ff] Next[/COLOR] c
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

O
  • Question Question
I am new to VBA and I am trying to figure out how I can use VBA to fill out an online risk calculator using data from excel
Replies
2
Views
404
John_w
J
U
  • Solved
Doing a search for text within a call, from entries in a defined name range
Replies
3
Views
178
Upex
U
T
  • Question Question
Excel VBA - IF cell contains text or numbers then copy contents to new sheet
Replies
0
Views
87
tylerhyatt04
T
J
  • Question Question
vba to find specific text in a column and then when found copy all the rows above it
Replies
1
Views
382
mumps
mumps
J
  • Solved
Speeding up VBA
Replies
9
Views
401
jvoss
J

Share this page

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