Set ActiveCell value based upon text string in neighbor cell for whole column

Matt_in_CA

New Member
Joined
Feb 4, 2015
Messages
5
Hello,
I’ve written the below VBA code, and it works, but it seems klunky. Can you streamline/improve?
This code populates a blank column based upon a possible text string in the neighboring cell. For example if the neighboring cell contains anywhere the string “DOG” then the value for the ActiveCell will be “CANINE”. If the neighbor cell contains “CAT” the ActiveCell will be “FELINE”. If the neighbor cell contains “HORSE”, the ActiveCell will be “EQUINE”. And so on.
The idea is that the macro creates a “sort code column” based upon a chunk of text from a file that downloads from an outside system. Then, I just SUMIF and manipulate the data now that each record has a category (sort) code.
I can attach a sample worksheet, but I don't see where in the forum to add attachments.
Like I say, it works, but there are 2 problems: 1. I think it might be a bit klunky. Is there any simpler way to code this? Like using Select-Case structure? Unlike my example, in the actual application there are about 40 different Sort/Category codes and hundreds of downloaded records. 2. The other problem is that my code does not provide for a multi criterion selection. In other words in the neighbor cell contains “CAT” and (anywhere) “SIAMESE”, then the ActiveCell should read “CAT-EXOTIC”. If the neighbor cell contains “HORSE” and “KENTUCKY” then the ActiveCell should read “RACEHORSE”. And so on.
CODE HERE:
Sub FillSortCodePost()
Do While ActiveCell.Offset(0, 1).Value <> Empty
If InStr(1, ActiveCell.Offset(0, 1).Value, "CAT") <> 0 Then ActiveCell.Value = "FELINE"
If InStr(1, ActiveCell.Offset(0, 1).Value, "DOG") <> 0 Then ActiveCell.Value = "CANINE"
If InStr(1, ActiveCell.Offset(0, 1).Value, "HORSE") <> 0 Then ActiveCell.Value = "EQUINE"
ActiveCell.Offset(1, 0).Select
Loop
End Sub
END OF CODE HERE.
Thanks so much!
Matt
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
You can probably just use your category list, rather than try to incorporate that many items into a Select Case or IF...ELSEIF algorithm. Your list of keywords and categories, dog, cat, horse, etc. could be listed on a separate worksheet 2 in column A beginning in Cell 1 with the appropriate category in the column B adjacent cell , and your key words would be in column A of sheet 1.
Code:
Sub categorize()
Dim sh1 As Worksheet, sh2 As Worksheet, lr As Long, c As Range, fLoc As Range, fAdr As String
Set sh1 = Sheets(1) 'Edit sheet name - this would be your data sheet
Set sh2 = Sheets(2) 'Edit sheet name
lr = sh2.Cells(Rows.Count, 1).End(xlUp).Row
    For Each c In sh2.Range("A1:A" & lr)
        Set fLoc = sh1.Range("A:A").Find(c.Value, , xlValues, xlPart)
            If Not fLoc Is Nothing Then
                fAdr = fLoc.Address
                Do
                    fLoc.Offset(0, 1) = c.Offset(0, 1).Value
                    Set fLoc = sh1.Range("A:A").FindNext(fLoc)
                Loop While fLoc.Address <> fAdr
            End If
    Next
End Sub

This just walks down your list of keywords and searches for a match in the other sheet. If a match is found, it then enters the category for that keyword next to the matched item. It will search the entire worksheet for each occurrence of each keyword before moving to the next one, until all have been searched and the categories entered.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,444
Messages
5,624,811
Members
416,056
Latest member
VARSHA V VASWANI

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
Top