Hello guys !

Here is what i want from a macro

In cells from B column i have from time to time a keyword . The macro
should find that keyword , to concatenate text from that cell with the
text from coresponding cell in C column and put the result in D column
beginning with the first cell . Here is an example :

Keyword = "mother"

B1 = mother C1 = and
B4 = daddy C4 = uncle
B6 = mother C6 = goes
B8 = mother C8 = is
B10 = sister C10= and
B20 = mother C20 = was


The result should be :

D1 = mother and
D2 = Mother goes
D3 = mother is
D4 = mother was

If this macro could work in all sheets , that would be magnific !
Many thanks in advance

PS: Sorry for my bad english :p)


--
andrei
------------------------------------------------------------------------
andrei's Profile: 1056
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=176493

Microsoft Office Help

Andrei,

Use a simple IF function in your spreadsheet. So, let's say that cell A1
contains your keyword "mother". In cell D1, put the following formula:

=IF(B1=$A$1,B1&" "&C1,"")

You can then copy the formula down and you should see your desired results.

Best,

Matthew Herbert

"andrei" wrote:


Hello guys !

Here is what i want from a macro

In cells from B column i have from time to time a keyword . The macro
should find that keyword , to concatenate text from that cell with the
text from coresponding cell in C column and put the result in D column
beginning with the first cell . Here is an example :

Keyword = "mother"

B1 = mother C1 = and
B4 = daddy C4 = uncle
B6 = mother C6 = goes
B8 = mother C8 = is
B10 = sister C10= and
B20 = mother C20 = was


The result should be :

D1 = mother and
D2 = Mother goes
D3 = mother is
D4 = mother was

If this macro could work in all sheets , that would be magnific !
Many thanks in advance

PS: Sorry for my bad english :p)


--
andrei
------------------------------------------------------------------------
andrei's Profile: 1056
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=176493

Microsoft Office Help

.

Thanks , i use a function like that but it really kills me because my
sheets have 8000-10000 rows and my workbooks have 60-80 sheets . That's
why i'm searching for a macro


--
andrei
------------------------------------------------------------------------
andrei's Profile: 1056
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=176493

Microsoft Office Help

This will do what you need:

Sub fnd()
Dim rngFind As Range
Dim strValueToPick As String
Dim rngPicked As Range
Dim rngLook As Range
Dim strFirstAddress As String
Dim oCell As Range
Dim Sh As Worksheet
Application.ScreenUpdating = False
strValueToPick = InputBox("Enter value to find", "Find all
occurences")
For Each Sh In Sheets
Set rngLook = Sheets(Sh.Name).Range("B1:B" &
Sheets(Sh.Name).Range("B" & Rows.Count).End(xlUp).Row)

With rngLook
Set rngFind = .Find(strValueToPick, LookIn:=xlValues,
lookat:=xlWhole)
If Not rngFind Is Nothing Then
strFirstAddress = rngFind.Address
Set rngPicked = rngFind
Do
Set rngPicked = Union(rngPicked, rngFind)
Set rngFind = .FindNext(rngFind)
UserForm1.ListBox1.AddItem rngFind.Address
Loop While Not rngFind Is Nothing And rngFind.Address <
strFirstAddress
End If
End With

If Not rngPicked Is Nothing Then
For Each oCell In rngPicked
oCell.Offset(0, 2).Value = oCell.Value & " " & oCell.Offset(0,
1).Value
Next oCell
End If
Next Sh
Application.ScreenUpdating = True
End Sub


andrei;634582 Wrote:
Thanks , i use a function like that but it really kills me because my
sheets have 8000-10000 rows and my workbooks have 60-80 sheets . That's
why i'm searching for a macro


--
Simon Lloyd

Regards,
Simon Lloyd
'Microsoft Office Help' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: 1
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=176493

Microsoft Office Help

It gives me in error here :

USERFORM1.LISTBOX1.ADDITEM RNGFIND.ADDRESS

If i delete this from the macro , it works , but puts the result in D
column not the way i want , as i said in first post


--
andrei
------------------------------------------------------------------------
andrei's Profile: 1056
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=176493

Microsoft Office Help

Try this:Sub fnd()
Dim rngFind As Range
Dim strValueToPick As String
Dim rngPicked As Range
Dim rngLook As Range
Dim strFirstAddress As String
Dim oCell As Range
Dim Sh As Worksheet
Application.ScreenUpdating = False
strValueToPick = InputBox("Enter value to find", "Find all
occurences")
For Each Sh In Sheets
Set rngLook = Sheets(Sh.Name).Range("B1:B" &
Sheets(Sh.Name).Range("B" & Rows.Count).End(xlUp).Row)

With rngLook
Set rngFind = .Find(strValueToPick, LookIn:=xlValues,
lookat:=xlWhole)
If Not rngFind Is Nothing Then
strFirstAddress = rngFind.Address
Set rngPicked = rngFind
Do
Set rngPicked = Union(rngPicked, rngFind)
Set rngFind = .FindNext(rngFind)
Loop While Not rngFind Is Nothing And rngFind.Address
< strFirstAddress
End If
End With

If Not rngPicked Is Nothing Then
For Each oCell In rngPicked
oCell.Offset(0, 2).Value = oCell.Value & " " &
oCell.Offset(0, 1).Value
Next oCell
End If
Sh.Range("D1:D" & Sh.Range("D" &
Rows.Count).End(xlUp).Row).SpecialCells(xlCellType Blanks).Delete
shift:=xlUp
Next Sh
Application.ScreenUpdating = True
End Sub


--
Simon Lloyd

Regards,
Simon Lloyd
'Microsoft Office Help' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: 1
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=176493

Microsoft Office Help