Hi, i have 7 sheets. I want to write a name in sheet "search", in G1 and the
macro formula (i guess), to display in the same sheet, a list with all name
it founds. But i want to display name and 4 other cells next to name.
Something like in this example:

a b c d e f
g

1
john
2 john b 1978 id 03 12.06.08 NO
3 john b (ed) 173 id 03 05.06.08 YES
4 john (ec) 53 id 03 31.05.08 YES

The problem is that some names have parentheisis and second name "john b
(ed)". And i want the search to ignore the parenthesis and to display all the
names it found, like in example. I want to display the name with second name
and parenthesis (if it has one), but in search i will write only john, but
the formula to ignore parenthesis when its searching.

I need to search in 5 sheets only.

Thanks in advance!!

Sub FindNames()

Person = UCase(Sheets("Search").Range("G1"))
DisplayMessage = ""
For Each sht In ThisWorkbook.Sheets
If sht.Name < "Search" Then
LastRow = sht.Range("A" & Rows.Count).End(xlUp).Row
For RowCount = 1 To LastRow
NewName = UCase(sht.Range("A" & RowCount))
'Only look at the same numberr of characters as Person
If Left(NewName, Len(Person)) = Person Then
NewLine = sht.Range("A" & RowCount) & " " & _
sht.Range("B" & RowCount) & " " & _
sht.Range("C" & RowCount) & " " & _
sht.Range("D" & RowCount) & " " & _
sht.Range("E" & RowCount)
If DisplayMessage = "" Then
DisplayMessage = NewLine
Else
DisplayMessage = DisplayMessage & Chr(13) & NewLine
End If
End If
Next RowCount
End If
Next sht
MsgBox (DisplayMessage)
End Sub


"puiuluipui" wrote:

Hi, i have 7 sheets. I want to write a name in sheet "search", in G1 and the
macro formula (i guess), to display in the same sheet, a list with all name
it founds. But i want to display name and 4 other cells next to name.
Something like in this example:

a b c d e f
g

1
john
2 john b 1978 id 03 12.06.08 NO
3 john b (ed) 173 id 03 05.06.08 YES
4 john (ec) 53 id 03 31.05.08 YES

The problem is that some names have parentheisis and second name "john b
(ed)". And i want the search to ignore the parenthesis and to display all the
names it found, like in example. I want to display the name with second name
and parenthesis (if it has one), but in search i will write only john, but
the formula to ignore parenthesis when its searching.

I need to search in 5 sheets only.

Thanks in advance!!

Joel, its not working.
I wrote john in G1 and the macro its not showing anything. It doesn't show
the result list below. It doesn't find anything.
What i am doing wrong?

"Joel" a scris:

Sub FindNames()

Person = UCase(Sheets("Search").Range("G1"))
DisplayMessage = ""
For Each sht In ThisWorkbook.Sheets
If sht.Name < "Search" Then
LastRow = sht.Range("A" & Rows.Count).End(xlUp).Row
For RowCount = 1 To LastRow
NewName = UCase(sht.Range("A" & RowCount))
'Only look at the same numberr of characters as Person
If Left(NewName, Len(Person)) = Person Then
NewLine = sht.Range("A" & RowCount) & " " & _
sht.Range("B" & RowCount) & " " & _
sht.Range("C" & RowCount) & " " & _
sht.Range("D" & RowCount) & " " & _
sht.Range("E" & RowCount)
If DisplayMessage = "" Then
DisplayMessage = NewLine
Else
DisplayMessage = DisplayMessage & Chr(13) & NewLine
End If
End If
Next RowCount
End If
Next sht
MsgBox (DisplayMessage)
End Sub


"puiuluipui" wrote:

Hi, i have 7 sheets. I want to write a name in sheet "search", in G1 and the
macro formula (i guess), to display in the same sheet, a list with all name
it founds. But i want to display name and 4 other cells next to name.
Something like in this example:

a b c d e f
g

1
john
2 john b 1978 id 03 12.06.08 NO
3 john b (ed) 173 id 03 05.06.08 YES
4 john (ec) 53 id 03 31.05.08 YES

The problem is that some names have parentheisis and second name "john b
(ed)". And i want the search to ignore the parenthesis and to display all the
names it found, like in example. I want to display the name with second name
and parenthesis (if it has one), but in search i will write only john, but
the formula to ignore parenthesis when its searching.

I need to search in 5 sheets only.

Thanks in advance!!

There arre two reasons the code would not work

1) The code expects the names to start in Row 1 with no blank rows between
the names.

If the Names don't start in row 1 then change this line

For RowCount = 1 To LastRow (make the one a differnt number)

2) the code expects the name to start as the first character in the cell

"puiuluipui" wrote:

Joel, its not working.
I wrote john in G1 and the macro its not showing anything. It doesn't show
the result list below. It doesn't find anything.
What i am doing wrong?

"Joel" a scris:

Sub FindNames()

Person = UCase(Sheets("Search").Range("G1"))
DisplayMessage = ""
For Each sht In ThisWorkbook.Sheets
If sht.Name < "Search" Then
LastRow = sht.Range("A" & Rows.Count).End(xlUp).Row
For RowCount = 1 To LastRow
NewName = UCase(sht.Range("A" & RowCount))
'Only look at the same numberr of characters as Person
If Left(NewName, Len(Person)) = Person Then
NewLine = sht.Range("A" & RowCount) & " " & _
sht.Range("B" & RowCount) & " " & _
sht.Range("C" & RowCount) & " " & _
sht.Range("D" & RowCount) & " " & _
sht.Range("E" & RowCount)
If DisplayMessage = "" Then
DisplayMessage = NewLine
Else
DisplayMessage = DisplayMessage & Chr(13) & NewLine
End If
End If
Next RowCount
End If
Next sht
MsgBox (DisplayMessage)
End Sub


"puiuluipui" wrote:

Hi, i have 7 sheets. I want to write a name in sheet "search", in G1 and the
macro formula (i guess), to display in the same sheet, a list with all name
it founds. But i want to display name and 4 other cells next to name.
Something like in this example:

a b c d e f
g

1
john
2 john b 1978 id 03 12.06.08 NO
3 john b (ed) 173 id 03 05.06.08 YES
4 john (ec) 53 id 03 31.05.08 YES

The problem is that some names have parentheisis and second name "john b
(ed)". And i want the search to ignore the parenthesis and to display all the
names it found, like in example. I want to display the name with second name
and parenthesis (if it has one), but in search i will write only john, but
the formula to ignore parenthesis when its searching.

I need to search in 5 sheets only.

Thanks in advance!!

It still doesn't work. I will try to explain better.
Lets say i have only 3 sheets. Sheet 1 "monday" , sheet 2 "tuesday" , sheet
3 "search".

In first sheet (monday) i will write in 5 columns, (a5, b5, c5, d5, e5), and
continue in a6, b6, c6......a7, b7,c7.....etc....a simple table.

In second sheet (tuesday) i will write the same way. A simple table with data.

First cell in every sheet, doesn't contains text.(names) It begin with "A5"

Sheet 3, (search) its empty. I only want to write a name in "G1" cell and
below, in the same sheet (search), the formula to make a list with everything
it found.
Ex: if i will write John in G1, the formula to show in the same sheet,
begining with A2, everything it found with john in other sheets. If in the
other two sheets is no "John", the formula to display nothing.
I want the formula to display "John" and other 4 cells next to John.
I will try to show you an example

Sheet monday
a b c d e
1 john 101 no yes monday

Sheet tuesday
a b c d e
1 john 143 yes yes friday

Sheet search

a b c d e f g
1 john

2 john 101 no yes monday
3 john 143 yes yes friday

In G1 i wrote "John" and the formula shows me a list with what it has found
in other sheets (monday ; tuesday)

So...i want some kind of search sheet....
If i write John to display what it found.....if i erase John and i write
Mary, the search for John to dissapear and to show Mary's result.

I hope you can help me.
Thanks again....and allot

"Joel" a scris:

There arre two reasons the code would not work

1) The code expects the names to start in Row 1 with no blank rows between
the names.

If the Names don't start in row 1 then change this line

For RowCount = 1 To LastRow (make the one a differnt number)

2) the code expects the name to start as the first character in the cell

"puiuluipui" wrote:

Joel, its not working.
I wrote john in G1 and the macro its not showing anything. It doesn't show
the result list below. It doesn't find anything.
What i am doing wrong?

"Joel" a scris:

Sub FindNames()

Person = UCase(Sheets("Search").Range("G1"))
DisplayMessage = ""
For Each sht In ThisWorkbook.Sheets
If sht.Name < "Search" Then
LastRow = sht.Range("A" & Rows.Count).End(xlUp).Row
For RowCount = 1 To LastRow
NewName = UCase(sht.Range("A" & RowCount))
'Only look at the same numberr of characters as Person
If Left(NewName, Len(Person)) = Person Then
NewLine = sht.Range("A" & RowCount) & " " & _
sht.Range("B" & RowCount) & " " & _
sht.Range("C" & RowCount) & " " & _
sht.Range("D" & RowCount) & " " & _
sht.Range("E" & RowCount)
If DisplayMessage = "" Then
DisplayMessage = NewLine
Else
DisplayMessage = DisplayMessage & Chr(13) & NewLine
End If
End If
Next RowCount
End If
Next sht
MsgBox (DisplayMessage)
End Sub


"puiuluipui" wrote:

Hi, i have 7 sheets. I want to write a name in sheet "search", in G1 and the
macro formula (i guess), to display in the same sheet, a list with all name
it founds. But i want to display name and 4 other cells next to name.
Something like in this example:

a b c d e f
g

1
john
2 john b 1978 id 03 12.06.08 NO
3 john b (ed) 173 id 03 05.06.08 YES
4 john (ec) 53 id 03 31.05.08 YES

The problem is that some names have parentheisis and second name "john b
(ed)". And i want the search to ignore the parenthesis and to display all the
names it found, like in example. I want to display the name with second name
and parenthesis (if it has one), but in search i will write only john, but
the formula to ignore parenthesis when its searching.

I need to search in 5 sheets only.

Thanks in advance!!

I made two changes. First the code starts at Row 5. Second, It looks at the
cell G1 for each sheet.

Sub FindNames()

DisplayMessage = ""
For Each Sht In ThisWorkbook.Sheets
If UCase(Sht.Name) < "SEARCH" Then
LastRow = Sht.Range("A" & Rows.Count).End(xlUp).Row
Person = UCase(Sht.Range("G1"))
For RowCount = 5 To LastRow
NewName = UCase(Sht.Range("A" & RowCount))
'Only look at the same numberr of characters as Person
If Left(NewName, Len(Person)) = Person Then
NewLine = Sht.Range("A" & RowCount) & " " & _
Sht.Range("B" & RowCount) & " " & _
Sht.Range("C" & RowCount) & " " & _
Sht.Range("D" & RowCount) & " " & _
Sht.Range("E" & RowCount)
If DisplayMessage = "" Then
DisplayMessage = NewLine
Else
DisplayMessage = DisplayMessage & Chr(13) & NewLine
End If
End If
Next RowCount
End If
Next Sht
MsgBox (DisplayMessage)
End Sub

"puiuluipui" wrote:

It still doesn't work. I will try to explain better.
Lets say i have only 3 sheets. Sheet 1 "monday" , sheet 2 "tuesday" , sheet
3 "search".

In first sheet (monday) i will write in 5 columns, (a5, b5, c5, d5, e5), and
continue in a6, b6, c6......a7, b7,c7.....etc....a simple table.

In second sheet (tuesday) i will write the same way. A simple table with data.

First cell in every sheet, doesn't contains text.(names) It begin with "A5"

Sheet 3, (search) its empty. I only want to write a name in "G1" cell and
below, in the same sheet (search), the formula to make a list with everything
it found.
Ex: if i will write John in G1, the formula to show in the same sheet,
begining with A2, everything it found with john in other sheets. If in the
other two sheets is no "John", the formula to display nothing.
I want the formula to display "John" and other 4 cells next to John.
I will try to show you an example

Sheet monday
a b c d e
1 john 101 no yes monday

Sheet tuesday
a b c d e
1 john 143 yes yes friday

Sheet search

a b c d e f g
1 john

2 john 101 no yes monday
3 john 143 yes yes friday

In G1 i wrote "John" and the formula shows me a list with what it has found
in other sheets (monday ; tuesday)

So...i want some kind of search sheet....
If i write John to display what it found.....if i erase John and i write
Mary, the search for John to dissapear and to show Mary's result.

I hope you can help me.
Thanks again....and allot

"Joel" a scris:

There arre two reasons the code would not work

1) The code expects the names to start in Row 1 with no blank rows between
the names.

If the Names don't start in row 1 then change this line

For RowCount = 1 To LastRow (make the one a differnt number)

2) the code expects the name to start as the first character in the cell

"puiuluipui" wrote:

Joel, its not working.
I wrote john in G1 and the macro its not showing anything. It doesn't show
the result list below. It doesn't find anything.
What i am doing wrong?

"Joel" a scris:

Sub FindNames()

Person = UCase(Sheets("Search").Range("G1"))
DisplayMessage = ""
For Each sht In ThisWorkbook.Sheets
If sht.Name < "Search" Then
LastRow = sht.Range("A" & Rows.Count).End(xlUp).Row
For RowCount = 1 To LastRow
NewName = UCase(sht.Range("A" & RowCount))
'Only look at the same numberr of characters as Person
If Left(NewName, Len(Person)) = Person Then
NewLine = sht.Range("A" & RowCount) & " " & _
sht.Range("B" & RowCount) & " " & _
sht.Range("C" & RowCount) & " " & _
sht.Range("D" & RowCount) & " " & _
sht.Range("E" & RowCount)
If DisplayMessage = "" Then
DisplayMessage = NewLine
Else
DisplayMessage = DisplayMessage & Chr(13) & NewLine
End If
End If
Next RowCount
End If
Next sht
MsgBox (DisplayMessage)
End Sub


"puiuluipui" wrote:

Hi, i have 7 sheets. I want to write a name in sheet "search", in G1 and the
macro formula (i guess), to display in the same sheet, a list with all name
it founds. But i want to display name and 4 other cells next to name.
Something like in this example:

a b c d e f
g

1
john
2 john b 1978 id 03 12.06.08 NO
3 john b (ed) 173 id 03 05.06.08 YES
4 john (ec) 53 id 03 31.05.08 YES

The problem is that some names have parentheisis and second name "john b
(ed)". And i want the search to ignore the parenthesis and to display all the
names it found, like in example. I want to display the name with second name
and parenthesis (if it has one), but in search i will write only john, but
the formula to ignore parenthesis when its searching.

I need to search in 5 sheets only.

Thanks in advance!!

try this where your summary sheet is sheet1

Sub findemall()
Sheet1.Range("a1:e100").ClearContents
what = Sheet1.Range("g1").Value

For i = 2 To Worksheets.Count
With Worksheets(i).Range("a5:a500")
Set c = .Find(what, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
dlr = Sheet1.Cells(Rows.Count, "a").End(xlUp).Row + 1
c.Resize(, 5).Copy Sheet1.Cells(dlr, "a")
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With
Next i
End Sub



--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"puiuluipui" wrote in message
...
It still doesn't work. I will try to explain better.
Lets say i have only 3 sheets. Sheet 1 "monday" , sheet 2 "tuesday" ,
sheet
3 "search".

In first sheet (monday) i will write in 5 columns, (a5, b5, c5, d5, e5),
and
continue in a6, b6, c6......a7, b7,c7.....etc....a simple table.

In second sheet (tuesday) i will write the same way. A simple table with
data.

First cell in every sheet, doesn't contains text.(names) It begin with
"A5"

Sheet 3, (search) its empty. I only want to write a name in "G1" cell and
below, in the same sheet (search), the formula to make a list with
everything
it found.
Ex: if i will write John in G1, the formula to show in the same sheet,
begining with A2, everything it found with john in other sheets. If in the
other two sheets is no "John", the formula to display nothing.
I want the formula to display "John" and other 4 cells next to John.
I will try to show you an example

Sheet monday
a b c d e
1 john 101 no yes monday

Sheet tuesday
a b c d e
1 john 143 yes yes friday

Sheet search

a b c d e f g
1
john

2 john 101 no yes monday
3 john 143 yes yes friday

In G1 i wrote "John" and the formula shows me a list with what it has
found
in other sheets (monday ; tuesday)

So...i want some kind of search sheet....
If i write John to display what it found.....if i erase John and i write
Mary, the search for John to dissapear and to show Mary's result.

I hope you can help me.
Thanks again....and allot

"Joel" a scris:

There arre two reasons the code would not work

1) The code expects the names to start in Row 1 with no blank rows
between
the names.

If the Names don't start in row 1 then change this line

For RowCount = 1 To LastRow (make the one a differnt number)

2) the code expects the name to start as the first character in the cell

"puiuluipui" wrote:

Joel, its not working.
I wrote john in G1 and the macro its not showing anything. It doesn't
show
the result list below. It doesn't find anything.
What i am doing wrong?

"Joel" a scris:

Sub FindNames()

Person = UCase(Sheets("Search").Range("G1"))
DisplayMessage = ""
For Each sht In ThisWorkbook.Sheets
If sht.Name < "Search" Then
LastRow = sht.Range("A" & Rows.Count).End(xlUp).Row
For RowCount = 1 To LastRow
NewName = UCase(sht.Range("A" & RowCount))
'Only look at the same numberr of characters as Person
If Left(NewName, Len(Person)) = Person Then
NewLine = sht.Range("A" & RowCount) & " " & _
sht.Range("B" & RowCount) & " " & _
sht.Range("C" & RowCount) & " " & _
sht.Range("D" & RowCount) & " " & _
sht.Range("E" & RowCount)
If DisplayMessage = "" Then
DisplayMessage = NewLine
Else
DisplayMessage = DisplayMessage & Chr(13) & NewLine
End If
End If
Next RowCount
End If
Next sht
MsgBox (DisplayMessage)
End Sub


"puiuluipui" wrote:

Hi, i have 7 sheets. I want to write a name in sheet "search", in
G1 and the
macro formula (i guess), to display in the same sheet, a list with
all name
it founds. But i want to display name and 4 other cells next to
name.
Something like in this example:

a b c d e
f
g

1
john
2 john b 1978 id 03 12.06.08 NO
3 john b (ed) 173 id 03 05.06.08 YES
4 john (ec) 53 id 03 31.05.08 YES

The problem is that some names have parentheisis and second name
"john b
(ed)". And i want the search to ignore the parenthesis and to
display all the
names it found, like in example. I want to display the name with
second name
and parenthesis (if it has one), but in search i will write only
john, but
the formula to ignore parenthesis when its searching.

I need to search in 5 sheets only.

Thanks in advance!!