VBA: enter value in cell if keyword appear in another cell

[ippeichan]

Hi there,

I'm doing our family accounting based on a regular download of bank account statement details in Excel. Based on certain keywords appearing in column H, I want a macro to fill out the category in column I. E.g. if cell H1 contains 'Wallmart' (apart from the usual incomprehensible stuff), I1 should be updated to 'Groceries'. The macro will have to loop through all cells to the end (the number of cells changes each period). Several 'keyword to category' combinations will need to be added.

I've read tons of posts, but so far none seems to do what I'm looking for. Any help to get me started would be much appreciated!

 

[Trevor G]

Welcome to the forum, give this ago:

Sub AddColData()
Range("H1").Select
Do Until ActiveCell.Value = ""
ActiveCell.Offset(1).Select
If ActiveCell = "Wallmart" Then ActiveCell.Offset(0, 1).Value = "Groceries"
Loop

End Sub

 

[ippeichan]

Welcome to the forum, give this ago:

Many thanks Trevor, for your super quick reply! Much appreciated.
Unfortunately, when I execute this macro, nothing appears in column I.
Any further advice would be well appreciated!

 

[steve the fish]

Hi. Give this a go. I have changed 'Wallmart' to 'Walmart' as I thought it only had one 'L'.

Sub groceries()

Dim c as Range
Dim myAddress as Variant
 
With Range("H1:H" & Range("H" & Rows.Count).End(xlUp).Row)
    Set c = .Find("Walmart", LookIn:=xlValues, LookAt:=xlPart)
    If Not c Is Nothing Then
        myAddress = c.Address
        Do
            c.Offset(0, 1).Value = "Groceries"
            Set c = .FindNext(c)
        Loop While Not c Is Nothing And c.Address <> myAddress
    End If
End With

End Sub

 

[ippeichan]

Hi. Give this a go. I have changed 'Wallmart' to 'Walmart' as I thought it only had one 'L'.

Sub groceries()

Dim c as Range
Dim myAddress as Variant
 
With Range("H1:H" & Range("H" & Rows.Count).End(xlUp).Row)
    Set c = .Find("Walmart", LookIn:=xlValues, LookAt:=xlPart)
    If Not c Is Nothing Then
        myAddress = c.Address
        Do
            c.Offset(0, 1).Value = "Groceries"
            Set c = .FindNext(c)
        Loop While Not c Is Nothing And c.Address <> myAddress
    End If
End With

End Sub

Excellent, that works! The other sample from Trevor works as well now, it was indeed due to the typo. My bad!

Based on your code sample, I managed to add a second keyword search as well.

Many thanks both for your excellent help!

 

[ippeichan]

Sorry Steve the fish, one more question. I'd like to add a subcategory as well...

eg if keyword 'mortgage' is found in column H, colum I should be 'home' and j should be 'mortgage'.

Could you be so kind to factor this in to your sample?

Many thanks in advance!!

 

[steve the fish]

c.Offset(0, 1).Value = "Home"
c.Offset(0, 2).Value = "Mortgage"

 

[ippeichan]

c.Offset(0, 1).Value = "Home"
c.Offset(0, 2).Value = "Mortgage"

Fantastic, this works like a charm!!

Thanks ever so much for your excellent advice!