Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro search
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!! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro search
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!! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro search
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!! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro search
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!! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro search
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!! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro search
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!! |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro search
I dont want the formula to look at the cell G1 for each sheet. I only want
"G1" to be a search box. I write something in the search box and the formula to show me the results in the same search sheet. In search box "G1" i will write a word and under "G1" to show me the results list. I want a search sheet but to display what it founds in other sheets. A search sheet with search box. and search box to be "G1". And the formula to look at "A1:E30" in every sheet. I found something in forum, but il display the sheets name when it found something. I want the formula to work as a search engine and display what i am looking for. Here is what i found. And its closer with what i want, but it display somthing else. http://www.microsoft.com/office/comm...0-1fc42d92c39f Can you make a formula like this one to display what i need? This formula to have a search box "G1", and to display what it found after i write something in search box "G1" THANKS ALLOT "Joel" a scris: 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!! |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro search
Don, it doesn't work. Or i dont know how to make it work.
sheet 1 "monday" sheet 2 "tuesday" sheet 3 "search" sheet "monday" and "tuesday" = database I put the code in sheet "search", (visual basic). Than i wrotte a name in cell G1 in "search" sheet. But nothing happened. Than i tryed to put the code in "monday" sheet (visual basic), where is database, and wrotte a name in G1 cell of the "monday" sheet. Nothing happened again.... Maybe i do something wrong.... can you help me with more details pls? Thanks allot! "Don Guillett" a scris: 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!! |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro search
My macro assumed your search sheet was sheet ONE. If all else fails, send your file to my email below. -- Don Guillett Microsoft MVP Excel SalesAid Software "puiuluipui" wrote in message ... Don, it doesn't work. Or i dont know how to make it work. sheet 1 "monday" sheet 2 "tuesday" sheet 3 "search" sheet "monday" and "tuesday" = database I put the code in sheet "search", (visual basic). Than i wrotte a name in cell G1 in "search" sheet. But nothing happened. Than i tryed to put the code in "monday" sheet (visual basic), where is database, and wrotte a name in G1 cell of the "monday" sheet. Nothing happened again.... Maybe i do something wrong.... can you help me with more details pls? Thanks allot! "Don Guillett" a scris: 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!! |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro search
Thanks Don. I have send to you an email.
Thanks! "Don Guillett" a scris: My macro assumed your search sheet was sheet ONE. If all else fails, send your file to my email below. -- Don Guillett Microsoft MVP Excel SalesAid Software "puiuluipui" wrote in message ... Don, it doesn't work. Or i dont know how to make it work. sheet 1 "monday" sheet 2 "tuesday" sheet 3 "search" sheet "monday" and "tuesday" = database I put the code in sheet "search", (visual basic). Than i wrotte a name in cell G1 in "search" sheet. But nothing happened. Than i tryed to put the code in "monday" sheet (visual basic), where is database, and wrotte a name in G1 cell of the "monday" sheet. Nothing happened again.... Maybe i do something wrong.... can you help me with more details pls? Thanks allot! "Don Guillett" a scris: 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!! |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro search
I sent back a workbook modified to suit.
-- Don Guillett Microsoft MVP Excel SalesAid Software "puiuluipui" wrote in message ... Thanks Don. I have send to you an email. Thanks! "Don Guillett" a scris: My macro assumed your search sheet was sheet ONE. If all else fails, send your file to my email below. -- Don Guillett Microsoft MVP Excel SalesAid Software "puiuluipui" wrote in message ... Don, it doesn't work. Or i dont know how to make it work. sheet 1 "monday" sheet 2 "tuesday" sheet 3 "search" sheet "monday" and "tuesday" = database I put the code in sheet "search", (visual basic). Than i wrotte a name in cell G1 in "search" sheet. But nothing happened. Than i tryed to put the code in "monday" sheet (visual basic), where is database, and wrotte a name in G1 cell of the "monday" sheet. Nothing happened again.... Maybe i do something wrong.... can you help me with more details pls? Thanks allot! "Don Guillett" a scris: 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!! |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro search
It works great! Thanks allot John. You really are good. Thanks again. "Don Guillett" a scris: I sent back a workbook modified to suit. -- Don Guillett Microsoft MVP Excel SalesAid Software "puiuluipui" wrote in message ... Thanks Don. I have send to you an email. Thanks! "Don Guillett" a scris: My macro assumed your search sheet was sheet ONE. If all else fails, send your file to my email below. -- Don Guillett Microsoft MVP Excel SalesAid Software "puiuluipui" wrote in message ... Don, it doesn't work. Or i dont know how to make it work. sheet 1 "monday" sheet 2 "tuesday" sheet 3 "search" sheet "monday" and "tuesday" = database I put the code in sheet "search", (visual basic). Than i wrotte a name in cell G1 in "search" sheet. But nothing happened. Than i tryed to put the code in "monday" sheet (visual basic), where is database, and wrotte a name in G1 cell of the "monday" sheet. Nothing happened again.... Maybe i do something wrong.... can you help me with more details pls? Thanks allot! "Don Guillett" a scris: 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!! |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro search
Don, the code its searching in planning sheet too. Can you make the code to
exclude planning sheet from search? I need the code to serch only the first 5 sheets. (monday, tuesday, wensday, thursday, friday). Thanks allot!!! "Don Guillett" a scris: I sent back a workbook modified to suit. -- Don Guillett Microsoft MVP Excel SalesAid Software "puiuluipui" wrote in message ... Thanks Don. I have send to you an email. Thanks! "Don Guillett" a scris: My macro assumed your search sheet was sheet ONE. If all else fails, send your file to my email below. -- Don Guillett Microsoft MVP Excel SalesAid Software "puiuluipui" wrote in message ... Don, it doesn't work. Or i dont know how to make it work. sheet 1 "monday" sheet 2 "tuesday" sheet 3 "search" sheet "monday" and "tuesday" = database I put the code in sheet "search", (visual basic). Than i wrotte a name in cell G1 in "search" sheet. But nothing happened. Than i tryed to put the code in "monday" sheet (visual basic), where is database, and wrotte a name in G1 cell of the "monday" sheet. Nothing happened again.... Maybe i do something wrong.... can you help me with more details pls? Thanks allot! "Don Guillett" a scris: 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!! |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro search
Done -- Don Guillett Microsoft MVP Excel SalesAid Software "puiuluipui" wrote in message ... Don, the code its searching in planning sheet too. Can you make the code to exclude planning sheet from search? I need the code to serch only the first 5 sheets. (monday, tuesday, wensday, thursday, friday). Thanks allot!!! "Don Guillett" a scris: I sent back a workbook modified to suit. -- Don Guillett Microsoft MVP Excel SalesAid Software "puiuluipui" wrote in message ... Thanks Don. I have send to you an email. Thanks! "Don Guillett" a scris: My macro assumed your search sheet was sheet ONE. If all else fails, send your file to my email below. -- Don Guillett Microsoft MVP Excel SalesAid Software "puiuluipui" wrote in message ... Don, it doesn't work. Or i dont know how to make it work. sheet 1 "monday" sheet 2 "tuesday" sheet 3 "search" sheet "monday" and "tuesday" = database I put the code in sheet "search", (visual basic). Than i wrotte a name in cell G1 in "search" sheet. But nothing happened. Than i tryed to put the code in "monday" sheet (visual basic), where is database, and wrotte a name in G1 cell of the "monday" sheet. Nothing happened again.... Maybe i do something wrong.... can you help me with more details pls? Thanks allot! "Don Guillett" a scris: 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!! |
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro search
Ok!
Thanks allot Don! "Don Guillett" a scris: Done -- Don Guillett Microsoft MVP Excel SalesAid Software "puiuluipui" wrote in message ... Don, the code its searching in planning sheet too. Can you make the code to exclude planning sheet from search? I need the code to serch only the first 5 sheets. (monday, tuesday, wensday, thursday, friday). Thanks allot!!! "Don Guillett" a scris: I sent back a workbook modified to suit. -- Don Guillett Microsoft MVP Excel SalesAid Software "puiuluipui" wrote in message ... Thanks Don. I have send to you an email. Thanks! "Don Guillett" a scris: My macro assumed your search sheet was sheet ONE. If all else fails, send your file to my email below. -- Don Guillett Microsoft MVP Excel SalesAid Software "puiuluipui" wrote in message ... Don, it doesn't work. Or i dont know how to make it work. sheet 1 "monday" sheet 2 "tuesday" sheet 3 "search" sheet "monday" and "tuesday" = database I put the code in sheet "search", (visual basic). Than i wrotte a name in cell G1 in "search" sheet. But nothing happened. Than i tryed to put the code in "monday" sheet (visual basic), where is database, and wrotte a name in G1 cell of the "monday" sheet. Nothing happened again.... Maybe i do something wrong.... can you help me with more details pls? Thanks allot! "Don Guillett" a scris: 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!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Search Macro | Excel Discussion (Misc queries) | |||
Search In a Macro/VBA | Excel Discussion (Misc queries) | |||
search macro/formula help | Excel Discussion (Misc queries) | |||
search macro/formula help | Excel Discussion (Misc queries) | |||
search macro | Excel Discussion (Misc queries) |