If then statement based on partial contents

[OleMiss2010]

In a worksheet I need to put business or individual in column B if column F contains "LLC", "LLP", "Inc", "Corp", or "Partnership". This needs to happen in rows 4 through 5525. My current idea is to run a sub that will use an If Then statement to occupy column B; the problem I have is I'm not sure how to have it look within column F's contents.


Any suggestions?

 

[Luke M]

Assuming you're using a loop, that should let you keep track of what row you're on.

[pre]

Sub Lookup()
Dim ReturnValue As String
Dim FoundValue As String
Dim FRange As Range

Set FRange = Range("F4:F5525")
Application.ScreenUpdating = False
For i = 4 To 5525
FoundValue = FRange.Cells(i-3, "F").Value
Select Case FoundValue
Case "LLC", "LLP", "Inc", "Corp", "Partnership"
ReturnValue = "Business"
Case Else
ReturnValue = "Individual"
End Select
Cells(i, "B") = ReturnValue
Next i
Application.ScreenUpdating = False
End Sub

[/pre]

 

[OleMiss2010]

This is similar to what I had in mind. What is Case command doing?

 

[OleMiss2010]

So here's the code I used, which is similar to yours except I needed to search cell F4 to occupy B4, rather than searching F1. However, every row returns a value of "individual" though I know that is not correct.

[pre]

Sub BusInd()
Dim i As Integer
Dim ReturnValue As String
Dim FoundValue As String
Dim FRange As Range

Set FRange = Range("F4:F5525")
Application.ScreenUpdating = False
For i = 4 To 5525
FoundValue = FRange.Cells(i, "F").Value
Select Case FoundValue
Case "LLC", "LLP", "Inc", "Corp", "Partner"
ReturnValue = "Business"
Case Else
ReturnValue = "Individual"
End Select
Cells(i, "B") = ReturnValue
Next i
Application.ScreenUpdating = True
End Sub

[/pre]

 

[Luke M]

Doh. Two mistakes, same line:

FoundValue = FRange.Cells(i, "F").Value


Since we set FRange equal to F4:F5525, the first "cell" is already F4. However, since i starts out at 4, we need to subtract 3. Second, the "F" callout says to look in 5th column of FRange. But FRange only has 1 column! So, the "fifth" column returns blanks, and we go to the Case Else. =(

Corrected line:

FoundValue = FRange.Cells(i-3, 1).Value


As you may have discovered already, Case Select is another way of having VB make a choice based on some input. I chose it for this situation because of all the different choices you had, and I didn't want to write a bunch of Or statements.

Also, just a thought, but you might also need another Case statement, in case some of the cells in col F can be blank. Perhaps like:

[pre]

Sub BusInd()
Dim i As Integer
Dim ReturnValue As String
Dim FoundValue As String
Dim FRange As Range

Set FRange = Range("F4:F5525")
Application.ScreenUpdating = False
For i = 4 To 5525
FoundValue = FRange.Cells(i - 3, 1).Value
Select Case FoundValue
Case "LLC", "LLP", "Inc", "Corp", "Partner"
ReturnValue = "Business"
Case ""
ReturnValue = ""
Case Else
ReturnValue = "Individual"
End Select
Cells(i, "B") = ReturnValue
Next i
Application.ScreenUpdating = True
End Sub

[/pre]

 

[OleMiss2010]

So I corrected the issue in the Cells() statement (I can't believe I didn't catch that myself), but it's still not returning any "business" values when it should. I'm not sure if I was clear before, but column F contains the name of a business so it will have more information in it than just LLC, LLP, Inc, Corp, or Partner. It will say something like "Bob the Builder Inc" and I want to use the fact that "Inc" is somewhere in the contents as my indidcator that something is a business.

 

[Luke M]

Ahhh. That makes things a little different.

[pre]

Sub BusInd()
Dim i As Integer
Dim ReturnValue As String
Dim FoundValue As String
Dim FRange As Range

Set FRange = Range("F4:F5525")
Application.ScreenUpdating = False
For i = 4 To 5525
'Switching to uppercase just to make sure
FoundValue = UCase(FRange.Cells(i - 3, 1).Value)
If FoundValue Like "*LLC*" Or _
FoundValue Like "*LLP*" Or _
FoundValue Like "*INC*" Or _
FoundValue Like "*CORP*" Or _
FoundValue Like "*PARTNER*" Then
ReturnValue = "Business"
Else
ReturnValue = ""
End If
Cells(i, "B") = ReturnValue
Next i
Application.ScreenUpdating = True
End Sub

[/pre]

 

[OleMiss2010]

Perfect. Thanks. So the "Like" statement is what searches within the contents of a cell I take it?

 

[Luke M]

Yes. Note the use of wildcards. From the VB help file:

Like Operator Example

This example uses the Like operator to compare a string to a pattern.

[pre]

Dim MyCheck
MyCheck = "aBBBa" Like "a*a"    ' Returns True.
MyCheck = "F" Like "[A-Z]"    ' Returns True.
MyCheck = "F" Like "[!A-Z]"    ' Returns False.
MyCheck = "a2a" Like "a#a"    ' Returns True.
MyCheck = "aM5b" Like "a[L-P]#[!c-e]"    ' Returns True.
MyCheck = "BAT123khg" Like "B?T*"    ' Returns True.
MyCheck = "CAT123khg" Like "B?T*"    ' Returns False.

[/pre]