VBA Auto Categorizing Based on Text In Cell

[shanenavy26]

Hello,
I currently have an excel sheet to track my monthly expenses. Currently, when I paste the data into the excel sheet, it will look for terms set in a list I made in Col S, and to set the category to whatever is next to it in Col T. After tinkering with it for awhile, I am noticing that it will only look for 1 word at the beginning, and won't work for terms in the middle of a cell or anything with spaces. Example:

What I want:
COSTCO = House
COSTCO Gas = Gas
ABC Restaurant = Fast Food

Currently, it will only look for the first word, and I cannot set words in the middle of cells such as Gas and Restaurant.

Here is the code I currently have, please let me know if i need to provide a better example or explain better, Thank you!

For Each Cl In Intersect(Target, Columns("B"))
If Not Cl.Value = "" Then
s = Split(Cl.Value)(0)
Cat = Application.Index(Range("S2:T100"), Application.Match(s, Range("S2:S100"), 0), 2)
If Not IsError(Cat) Then Cl.Offset(, 1).Value = Cat
End If
Next Cl
Application.EnableEvents = True

 

[Micron]

It would help if you also showed what you have to work with. If you split ABC Restaurant using (0) you'll only get ABC. If you use 1 you'll get the 2nd word and so on. Without seeing the inputs it's hard to say if you should be splitting the strings at all, or if so, looping over the array and looking for matches on single values.

 

[shanenavy26]

It would help if you also showed what you have to work with. If you split ABC Restaurant using (0) you'll only get ABC. If you use 1 you'll get the 2nd word and so on. Without seeing the inputs it's hard to say if you should be splitting the strings at all, or if so, looping over the array and looking for matches on single values.

Would you like me to upload the file? or mini-sheet?
Here are some actual values that I am trying to have the VBA read.

SANDEE THAI RESTAURANT

COSTCO GAS #0017 EUGENE

EMPIRE FITNESS 877

DAIRY QUEEN #1812

It seems to only be counting the 1st word before a space.

 

[Micron]

Aren't you doing this kind of backwards? Why not use the category range in S and if one of the unique values is found in B where C has no value (or don't worry about that, just overwrite it) then get what's in T on that row? If sticking with vba, Instr function can look for Costco in B. However, you'd probably want to only run once for "Bills" so I'd probably build an array from S.

It seems to only be counting the 1st word before a space.

Explained in post 2.

 

[shanenavy26]

I have some further VBA code that is automatically renaming all of the expenses (to take off the "POS PURCHASE - ####", so I did it this was as to not conflict with that. I am very very noob with this so piecing these two codes together was the only thing I found to work. But now this issue with it only looking for the first word matching is my problem. I would prefer to stick with a VBA method of making it happen, but is the code that I am using not going to cut it? or do I have to change one of the values or add something else? I apologize for my lack of knowledge.

 

[Micron]

is the code that I am using not going to cut it?

Afraid not. Not sure I get the point about the other code if it acts on col B - it shouldn't matter unless that code messes up B values. Saying that because you have Costco Gas in one cell and CostcoGas in another, so no matter what there will be no match. Been thinking but not sure yet how/if you can use vba. Did you consider a data validation list and and just pick from that? After entering Costco Gas you click in the adjacent cell and pick "Cars" or whatever, from your list. Much, much simpler.

 

[shanenavy26]

Afraid not. Not sure I get the point about the other code if it acts on col B - it shouldn't matter unless that code messes up B values. Saying that because you have Costco Gas in one cell and CostcoGas in another, so no matter what there will be no match. Been thinking but not sure yet how/if you can use vba. Did you consider a data validation list and and just pick from that? After entering Costco Gas you click in the adjacent cell and pick "Cars" or whatever, from your list. Much, much simpler.

The "CostcoGas" was actually my attempt at making it work, by having VBA remove the space in between, making it technically 1 word, and then it works for the auto categorize.
I have a list already set for the Category Row, what I am attempting to do is just semi automate that part with the very frequent expenses like a grocery store we go to alot, or a gas station.