VBA: Find and select multiple cells in a column based on value

[Keppa4v]

Hi!

I've got a big stack of order receipts in a single column, and I'm trying to create a macro to sort it out and clean it up. I recorded a macro that for the most parts works fine, but for the very beginning where I want to search for a specific value and select all cells containing that value. When I record that part of the macro, the code merely reads:

Sub valintatesti()
'
' valintatesti Makro
' etsii ja valitsee
'


'
Columns("A:A").Select
End Sub

There's nothing about a search phrase and selecting all results.

This is what the rest of it looks like:

Sub Makro1()
'
' Makro1 Makro
'


'
*Here would be the code for finding and selecting*
Selection.Copy
Sheets("testilehti").Select
Range("E12").Select
ActiveSheet.Paste
Selection.Replace What:="Tuotenumero: ", Replacement:="", LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Application.CutCopyMode = False
Selection.NumberFormat = "0"
End Sub

Seems that I'm at the moment unable to add screen shots of the problem, but to clarify some here's an example:

1.

Tuotenumero:8 88072395145

Nimeke: Traveling Wilburys Vol.3

Aineistotyyppi: MUSIIKKIÄÄNITE (CD)

Allfons ID: 4402004

1 kpl a`13,31€ (Listahinta a`16,13€)

Kokonaissumma: 13,31€

2.

Tuotenumero: 0888072395138

Nimeke: Traveling Wilburys Vol.1

Aineistotyyppi: MUSIIKKIÄÄNITE (CD)

Allfons ID: 4402007

1 kpl a`13,31€ (Listahinta a`16,13€)

Kokonaissumma: 13,31€

3.

Tuotenumero: 4029759113461

Tekijä: Hubbard, Freddie

Nimeke: Hub of Hubbard

Aineistotyyppi: MUSIIKKIÄÄNITE (CD)

Allfons ID: 4403689

1 kpl a`9,90€ (Listahinta a`12,00€)

Kokonaissumma: 9,90€

4.

Tuotenumero: 4029759115144

Nimeke: Destiny

Aineistotyyppi: MUSIIKKIÄÄNITE (CD)

Allfons ID: 4403699

1 kpl a`13,39€ (Listahinta a`16,23€)

Kokonaissumma: 13,39€

<tbody>
</tbody>

Whoa it looks ugly. Well anyway there's thousands of rows of information like this in one single column, and I'm trying to find and select every cell containing "Tuotenumero", then doing the magic that actually works well already, then doing the same for other types of cells.

Any chance of it working?

 

[JLGWhiz]

try this one

Sub Tuotenumero()
Dim txt As String
txt = InputBox("Enter the string to replace", "STRING TO REPLACE")
    If txt = "" Then Exit Sub
With ActiveSheet
    .Range("E2", .Cells(Rows.Count, 5)).Replace txt, ""
End With
End Sub

 

[Keppa4v]

Thanks! Right now I'm not in a position to test it, but I will. I'm very new to VBA, in fact this is my first attempt at creating a macro, so could you walk me through this code? What does everything mean exactly?

 

[Keppa4v]

I quickly tested the code you provided, but didn't really understand the purpose of it.

But after some more googling I found a code like this:

Sub NoLoop()
    Cells.Find(What:="Tuotenumero", After:=[A1], LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False).Activate
End Sub

This finds the first occurrence of Tuotenumero, so can I make it continue so that it finds all occurrences and activates them all?

 

[JLGWhiz]

I quickly tested the code you provided, but didn't really understand the purpose of it.

But after some more googling I found a code like this:

Sub NoLoop()
    Cells.Find(What:="Tuotenumero", After:=[A1], LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False).Activate
End Sub

This finds the first occurrence of Tuotenumero, so can I make it continue so that it finds all occurrences and activates them all?

It was my impression that you wanted to essentially remove the string "Tuotenumero" from the text in each cell. But it was not clear if that was the only word you wanted to remove, so I put in an InputBox which allows the user to enter a string of their choice and the code will then replace that string throughout the specified range of cells, which was column E in this case, based on your sample code in the OP. Did you try the code? Maybe a more detailed explanation of what you want to do would help.