Case statement if string contains

Question

I am trying to do a select case statement where its based off a string that I have stored in a variable contains certain words. From what I've seen everywhere I've looked I have it right, but its not working. I keep getting "Compile Error: Invalid Qualifier" on my variable "Model", in the case statement, i.e Case Model.Contains("GR CHER").

Does anyone see something that Im missing here?

i = 2 Model = Sheets("Data").Cells(i, 8) While Not IsEmpty(Cells(i, 1)) Select Case True Case Model.Contains("GR CHER") Model = "Grand Cherokee" Case Model.Contains("CHRGR") Model = "Charger" Case Model.Contains("HLLCT") Model = "HellCat" Case Model.Contains("R1500") Model = "Ram 1500" Case Else Rows([i]).EntireRow.Delete i = i - 1 End Select Sheets("Data").Cells(i, 8).Value = Model i = i + 1 Wend

so you are treating Model as text variable and so you would want to use something like this instead. Instr("GR CHER", Model) — Scott Craner
– Scott Craner, Commented Jan 17, 2016 at 22:34

chris neilsen · Accepted Answer · 2016-01-17 22:40:38Z

2

Your code doesn't look like VBA.

Also, I don't think you can do anything that equals "contains" is a Select Case

Your code, refactored, also fixing a few other issues

Sub zx() Dim i As Long Dim Model As Variant Dim Deleted As Boolean i = 2 With Sheets("Data") Do While Not IsEmpty(Sheets("Data").Cells(i, 1)) Model = Sheets("Data").Cells(i, 8) Deleted = False If Model Like "*GR CHER*" Then Model = "Grand Cherokee" ElseIf Model Like "*CHRGR*" Then Model = "Charger" ElseIf Model Like "*HLLCT*" Then Model = "HellCat" ElseIf Model Like "*R1500*" Then Model = "Ram 1500" Else Rows(i).EntireRow.Delete Deleted = True End If If Not Deleted Then .Cells(i, 8).Value = Model i = i + 1 End If Loop End With End Sub

answered Jan 17, 2016 at 22:40

chris neilsen

53.3k10 gold badges91 silver badges127 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

user1787114 Over a year ago

for some reason it executes the else statement on every row (deletes every row after the header row), even when the values its looking for are there. Even when I step through it and look at the value for Model,it will be, for example, GR CHER but the code will delete the row anyway.

chris neilsen Over a year ago

Works fine for me. look closely at spelling and check for special characters eg nbs

user1787114 Over a year ago

it looks like what its doing is looking for exact matches. If i have "AVENGER (3.6L)" in a cell, and the code says ElseIf Model Like "AVENGER" Then Model = "Avenger" Deleted = False it wont catch on that elseif statement and will go down until the else and delete the row

chris neilsen Over a year ago

If you want case insensitive use Model = UCase( ... ) and write your If clauses in CAPS. To allow for partial matches include the *'s as shown in my answer

user1787114 Over a year ago

that was it. the values in the cells are all upper case, I didnt even think about the fact that I wrote the value it was looking for in standard capitalization i.e Grand Cherokee. thanks for the help!

brettdj · Accepted Answer · 2016-01-22 08:20:01Z

No need to

Loop through each row to delete - quicker to AutoFilter
Or derive and store the variable Model at each step as you don`t use it

You could try the approach below, which is the VBA equivalent

of entering a formula in a working column to retun TRUE or FALSE for testing each string in column H
filtering out the False results

=COUNT(SEARCH({"*GR CHER*","*CHRGR*","*HLLC*","*R1500*"},I2))=1

code

Sub QuickCull() Dim ws As Worksheet Dim rng1 As Range Set ws = Sheets("Data") Set rng1 = ws.Range(ws.[b2], ws.Cells(Rows.Count, "B").End(xlUp)) Application.ScreenUpdating = False With rng1.Offset(0, 1) .EntireColumn.Insert .FormulaR1C1 = "=COUNT(SEARCH({""*GR CHER*"",""*CHRGR*"",""*HLLC*"",""*R1500*""},RC[5]))=1" .AutoFilter Field:=1, Criteria1:="False" .Offset(1, 0).EntireRow.Delete .EntireColumn.Delete End With Application.ScreenUpdating = True End Sub

Registered User · Accepted Answer · 2016-01-17 23:51:32Z

As Chris mentioned, you can't use "contains", the function would be "InStr" but I am pretty sure the following method will get the work done.

Sub Models() Dim i As Long: i = 2 With ThisWorkbook.Sheets("Data") While i <= .Cells(.Rows.Count, "A").End(xlUp).Row If .Cells(i, 8) Like "*GR CHER*" Then .Cells(i, 8) = "Grand Cherokee" i = i + 1 ElseIf .Cells(i, 8) Like "*CHRGR*" Then .Cells(i, 8) = "Charger" i = i + 1 ElseIf .Cells(i, 8) Like "*HLLCT*" Then .Cells(i, 8) = "HellCat" i = i + 1 ElseIf .Cells(i, 8) Like "*R1500*" Then .Cells(i, 8) = "Ram 1500" i = i + 1 Else .Rows(i).EntireRow.Delete End If Wend End With End Sub

user1787114 · Accepted Answer · 2016-01-21 02:28:22Z

It looks like what its doing is looking for exact matches. If i have "AVENGER (3.6L)" in a cell and the code says:

If Model Like "*AVENGER*" Then Model = "Avenger" Deleted = False

or if i try:

If Model Like "AVENGER" Then Model = "Avenger" Deleted = False

it wont catch on that If statement and will go down until the else statement and delete the row

Collectives™ on Stack Overflow

Case statement if string contains

4 Answers 4

5 Comments

Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

5 Comments

Comments

Comments

Comments

Related