select and replace cell

[nix14085]

Aloha!

I am new to VBA and trying to learn some code by setting up a personal budget. I have been copying data as exported from my bank and slowly setting up an excel document that filters the data based on various criteria. the problem i am running into is the file from my bank include a lot of extra information that i don't need i have included a sample line below

POS PURCHASE ABC STORES 123456789012345 ABC STORES Honolulu HI R111222333444555US

What I would like to do is search within the column (column C) for specific criteria (eg. ABC STORES) and then clear the cell and replace it with a given string (eg. PURCHASE - ABC STORES). I am trying to figure out a way to do this with VBA for certain purchases I make often to save time when importing data. I have been trying to search for a similar case but I haven't found anything that seems to work. Can anyone point me in the direction of where to start for something like this, or what functions i should be using? I am in excel 2010

Thanks!

[psctornado]

Would this work for you?

Set up your xls so that your list of information starts in column A1 for instance. Then your return possibilities say in column M.

=IF(COUNT(SEARCH(" "&M1&" "," "&A1&" "))>0=TRUE,M1,"")

[D_Marcel]

Hi nix14085, welcome to VBAX!

Welcome to VBA world also, hope you can learn a lot here.
To your first question, try this one:

[VBA]Sub Budget()

Dim Register As Range
Dim New_Value As String
Set Cells_Range = Plan1.Range(Cells(1, 3), Cells(Cells(Rows.Count, 3).End(xlUp).Row, 3))


For Each Register In Cells_Range
If Register.Value Like "*ABC STORES*" Then
New_Value = Replace(Register, "ABC STORES", "PURCHASE - ABC STORES")
Register.Value = New_Value
End If
Next Register


End Sub[/VBA]

[nix14085]

Thanks!

That worked perfectly, and I actually understand most of it lol. I did remove the replace function as I was trying to replace the entire line not just "ABC STORES" so here is what i ended up with in the end.

Sub Budget()

Dim Register As Range
Dim New_Value As String
Set Cells_Range = Sheets("export-1").Range(Cells(1, 3), Cells(Cells(Rows.Count, 3).End(xlUp).Row, 3))

For Each Register In Cells_Range
If Register.Value Like "*ABC STORES*" Then
New_Value = "PURCHASE - ABC STORES"
Register.Value = New_Value
End If
Next Register


End Sub

Thanks again!