How Do I Automate Multiple Text Filter Contains Sequences and Add The Text Value To The Column To The Right?

Question

I've been trying to implement excel VBA's at work. I have to manually categorise each keyword into categories and my current process is a simple text filter contains then manually add to all cells (GIF to demonstrate at the bottom of the post).

The community has helped me get this far with my VBA code - I'm trying to loop through a range C2:C3 (freehold and leasehold) and then return the value freehold or lease hold in column B next to the relevant keyword.

I'm completely stuck on why this isn't working and I would love a hand.

Here is the excel spreadsheet I'm using to test my macro on

 Sub LoopRange()
    Dim lastrow, i As Variant
    lastrow = Range("A" & Rows.Count).End(xlUp).Row

    Dim rCell As Range
    Dim rRng As Range

    Set rRng = Sheet1.Range("C2:C3")

    For Each rCol In rRng.Columns
        For Each rCell In rCol.Rows
            Debug.Print rCell.Address, rCell.Value
        Next rCell
    Next rCol

    For i = 2 To lastrow
        If Range("A" & i).Value Like "*rCell.Value*" Or Range("A" & i).Value Like "*rCell.Value" Or Range("A" & i).Value Like "rCell.Value*" Then
            Range("B" & i).Value = "rCell.Value"
        End If
    Next i
End Sub

There is usually another 20-40 terms just like freehold and leasehold - that is why I need to use a loop through sequence.

P.S. Thank you to those who already replied - you guys have been immensely helpful already and I can't wait to improve my skills and start giving back to this community

Current process of manually adding the keyword categorisation.

Thanks again I really appreciate it guys!

Welcome to Stack Overflow. Please note that because this is no free code writing service it is necessary to show either what you have tried so far and where you got stuck or errors (by showing your code) or at least to show what you have researched and the effort you made. • Also it is absolutely necessary that you ask a question (you didn't ask one yet). • A good start could be the macro recorder (and apply [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) to the recorded code.) — Pᴇʜ, Feb 14 '19 at 14:08

Karthick Gunasekaran · Accepted Answer · 2019-02-18T12:42:58.647

0

use the below code.

Sub test()
    Dim lastrow, i As Long
    lastrow = Range("A" & Rows.Count).End(xlUp).Row
    For i = 2 To lastrow
        If Range("A" & i).Value Like "*freehold*" Or Range("A" & i).Value Like "*freehold" Or Range("A" & i).Value Like "freehold*" Then
            Range("B" & i).Value = "yes"
        End If
    Next i
End Sub

Output:

EDIT 1

As requested, try this with below.

Sub LoopRange()
    Dim lastrow As Long, i As Long, lastfilterrow As Long
    lastrow = Range("A" & Rows.Count).End(xlUp).Row
    lastfilterrow = Range("C" & Rows.Count).End(xlUp).Row
    For j = 2 To lastfilterrow
        For i = 2 To lastrow
            If Range("A" & i).Value Like "*" & Range("C" & j).Value & "*" Then
                Range("B" & i).Value = Range("C" & j).Value
            End If
        Next i
    Next j
End Sub

edited Feb 18 '19 at 12:42

answered Feb 14 '19 at 14:27

Karthick Gunasekaran

2,697
1
15
25

`Range("A" & i).Value Like "*freehold*"` should be enough to test you can throw out `freehold*` and `*freehold` they are both included in the first test. – Pᴇʜ Feb 14 '19 at 14:30
Wow thanks for the quick response guys! Is there a way to make this scale? Usually I have around 10-40 different search terms like 'freehold' and this repetitive process needs to be repeated for each term. – Tim Kelly Feb 14 '19 at 15:26
@TimKelly put a loop around that code, that loops through your 10-40 search items. Give it a try on your own. You will find many tutorials on how to loop throug a range. Then just replace `Range("A" & i).Value Like "*freehold*"` with the current value of the loop `Range("A" & i).Value Like "*" & YourValueVariable & "*"` give it a try it's not that difficult. If you get stuck or errors open up a new question showing your code. Asking here means you need to try it yourself **first** and present your attempts. – Pᴇʜ Feb 14 '19 at 15:49
Amazing thanks Peh! I'm going to try this now. Is it best practice to open new questions for the new code rather than just editing this question? I really appreciate you helping me out with this post. :) – Tim Kelly Feb 14 '19 at 15:50
@TimKelly You can try to edit this question if it is not closed down until then. If it is closed better open up a new one. – Pᴇʜ Feb 14 '19 at 15:51
@Pᴇʜ I've managed to get stuck again but I feel so close - could you please help me debug the new code edited? – Tim Kelly Feb 15 '19 at 10:02
@TimKelly look at what I wrote `Range("A" & i).Value Like "*" & YourValueVariable & "*"` and compare it with what you did `Range("A" & i).Value Like "*rCell.Value*"` ;) Note that you can delete `Or Range("A" & i).Value Like "*rCell.Value" Or Range("A" & i).Value Like "rCell.Value*"` you will not need that at all, both is checked already in the first one. – Pᴇʜ Feb 15 '19 at 10:06
@KarthickGunasekaran don't ever use `Variant` unless you have to. It is the worst datatype you could choose: `Dim lastrow As Long, i As Long, lastfilterrow As Long` • Row counting variables must be of type `Long`. – Pᴇʜ Feb 15 '19 at 10:26
Absolutely amazing Karthick - you've helped me out a boat load. How can I help you out? P.S. Is there any way to create a loop that loops to do this for multiple columns? AKA Do the loop for every 2nd column (the ones with the variables in them) until active cell is blank? – Tim Kelly Feb 15 '19 at 11:13
@TimKelly There is a way. But also this is a completely new question. Did you try something before you asked? If no try first. If yes show your attempts by asking a good question including your code according to [ask] please. – Pᴇʜ Feb 15 '19 at 12:14
@KarthickGunasekaran Please note that `Dim lastrow, i, lastfilterrow As Long` will only declare `lastfilterrow As Long` but `lastrow As Variant` and `i As Variant`. You must specify a type for **every** variable in VBA: `Dim lastrow As Long, i As Long, lastfilterrow As Long` otherwise VBA automatically assumes `Variant` – Pᴇʜ Feb 15 '19 at 13:50
1

@Pᴇʜ, Thanks. I am constantly improving. – Karthick Gunasekaran Feb 18 '19 at 12:49

How Do I Automate Multiple Text Filter Contains Sequences and Add The Text Value To The Column To The Right?

1 Answers1