Macro - searches for key word in a column and concatenates entire

[andrei]

Let's say i have a column A and column B with a lot of cells with different
content. For every cell in A column with content , there is a B cell with
content

What i want is macro which searches for given key word in column A . If it
finds it , it concatenates entire row (without spaces between content of
different cells) and puts in in column C ( let's say )

The most important thing is where it puts it . And i give in example :

key word : house

A1 : garden B1 : basement
A2 : house B2 : bedroom
A3 : mother B3 : father
A4 : house B4 : like a cottage

Finds keyword in A2 , concatenates A2-B2 ( result - housebedroom ) and puts
it in C1 !
Finds keyword in A4 , concatenates A4-B4( result - houselike a cottage ) and
puts it in C2 !

so on ...

In my case , as i said before , i don't need spaces between content from
different cells . For what i use it , the concatenated result makes sense
without spaces

 

[Patrick Molloy]

why don't you just use a simple VLOOKUP
if your keyword is in D1, and you search for it in A:A concatenatign to B:B

the C1=D1 & VLOOKUP(D1,A:B,2,False)

 

[Matthew Herbert]

Andrei,

Why not use an IF function rather than a macro? You could place your key
word (i.e. the "find' word) in D1 and use an IF funciton to "find" and
concatenate the cells. For example, cell D1 has "house" in it and cell C1
has the following formula: =IF(A1=$D$1,A1&B1,""). Simply copy the formula
in cell C1 down, change the text in D1 accordingly, and recalculate the cells
(if you are not using Automatic calculation).

(If you really feel you need the macro then post back to the thread that you
need it, and I'll create a sample if someone else doesn't do so before I get
to it).

Best,

Matthew Herbert

 

[Dave Peterson]

Do you have to use a macro?

I'd just use a formula.

I'd add a header row (new row 1) and plop the keyword into C1.
Then put this in C2:
=if(a2<>$c$1,"",a2&b2)
and drag down

 

[andrei]

Thanks guys . Both functions work . I will try to manage with them . I
prefered a macro that moves the concatenated results in a column as i
explained in first post for the following reason :

The cells i have to process from 2 columns are from something like A1B1 ,
A9B9 , A12B12 , A25B25 going to something like A10000B10000 ( without a clear
pattern that i have content every 10th cell , it's random ) . That's why i
needed the results to be one below another in same column ( say from C1 to
C100 )

If a macro can be done , many thanks in advance

 

[Dave Peterson]

I didn't notice you wanted it contiguous cells.

I'd add a filter and show the non-blanks, but if that doesn't help:

Option Explicit
Sub testme()
Dim myCell As Range
Dim myRng As Range
Dim DestCell As Range
Dim myWord As String

myWord = "house"

With Worksheets("Sheet1")
Set myRng = .Range("A1", .Cells(.Rows.Count, "A").End(xlUp))
Set DestCell = .Range("C1")
End With

For Each myCell In myRng.Cells
With myCell
If LCase(.Value) = LCase(myWord) Then
DestCell.Value = .Value & .Offset(0, 1).Value
Set DestCell = DestCell.Offset(1, 0)
End If
End With
Next myCell

End Sub

 

[andrei]

Many thanks !

I didn't notice you wanted it contiguous cells.

I'd add a filter and show the non-blanks, but if that doesn't help:

Option Explicit
Sub testme()
Dim myCell As Range
Dim myRng As Range
Dim DestCell As Range
Dim myWord As String

myWord = "house"

With Worksheets("Sheet1")
Set myRng = .Range("A1", .Cells(.Rows.Count, "A").End(xlUp))
Set DestCell = .Range("C1")
End With

For Each myCell In myRng.Cells
With myCell
If LCase(.Value) = LCase(myWord) Then
DestCell.Value = .Value & .Offset(0, 1).Value
Set DestCell = DestCell.Offset(1, 0)
End If
End With
Next myCell

End Sub