Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need to find and collect all cells with a certain word onto a shee
Hi there,
I'm new to macros. Could anyone help me with a macro to search for the word 'warranty' on all the sheets and collect all the cells with the word 'warranty' onto a new sheet. I'm struggling to do this. It sounds very difficult to too. Any help greatly appreciated Thanks Suzanne |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need to find and collect all cells with a certain word onto a shee
You could try something like this. I don't have time to add the sheet or put
it on the sheet. I'm sure someone else can do that for you. Sub FindWarranty() Dim WS As Worksheet Dim myRange As Range Dim myNewRange As Range For Each WS In ActiveWorkbook.Worksheets Set myNewRange = WS.Cells(1, 1) Do Set myRange = myNewRange Set myNewRange = WS.Cells.Find(What:="Warranty", After:=myRange, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False) If myRange.Address < myNewRange.Address Then Debug.Print WS.Name, myNewRange.Address, myNewRange.Value End If Loop While myRange.Address < myNewRange.Address Next WS End Sub -- HTH, Barb Reinhardt "Grd" wrote: Hi there, I'm new to macros. Could anyone help me with a macro to search for the word 'warranty' on all the sheets and collect all the cells with the word 'warranty' onto a new sheet. I'm struggling to do this. It sounds very difficult to too. Any help greatly appreciated Thanks Suzanne |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need to find and collect all cells with a certain word onto a shee
This macro records results in a worksheet called "new sheet". It scans thru
all cells in all worksheets looking for the keyword. If a keyword is found the sheetname, the cell address, and the cell contents are recorded back in new sheet: Sub collect_um() Set nsh = Worksheets("new sheet") k = 1 For Each sh In Worksheets If sh.Name < "new sheet" Then For Each r In sh.UsedRange If Application.WorksheetFunction.IsText(r) Then If Len(r.Value) < Len(Replace(r.Value, "warranty", "")) Then nsh.Cells(k, 1).Value = sh.Name nsh.Cells(k, 2).Value = r.Address nsh.Cells(k, 3).Value = r.Value k = k + 1 End If End If Next End If Next End Sub -- Gary''s Student - gsnu200756 "Grd" wrote: Hi there, I'm new to macros. Could anyone help me with a macro to search for the word 'warranty' on all the sheets and collect all the cells with the word 'warranty' onto a new sheet. I'm struggling to do this. It sounds very difficult to too. Any help greatly appreciated Thanks Suzanne |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need to find and collect all cells with a certain word onto a
Hi Gary,
Thanks it works I added the two following lines to create the sheet otherwise I understand that I need to have a sheet already existing before I ran. Sheets.Add ActiveSheet.Name = "new sheet" Thanks again "Gary''s Student" wrote: This macro records results in a worksheet called "new sheet". It scans thru all cells in all worksheets looking for the keyword. If a keyword is found the sheetname, the cell address, and the cell contents are recorded back in new sheet: Sub collect_um() Set nsh = Worksheets("new sheet") k = 1 For Each sh In Worksheets If sh.Name < "new sheet" Then For Each r In sh.UsedRange If Application.WorksheetFunction.IsText(r) Then If Len(r.Value) < Len(Replace(r.Value, "warranty", "")) Then nsh.Cells(k, 1).Value = sh.Name nsh.Cells(k, 2).Value = r.Address nsh.Cells(k, 3).Value = r.Value k = k + 1 End If End If Next End If Next End Sub -- Gary''s Student - gsnu200756 "Grd" wrote: Hi there, I'm new to macros. Could anyone help me with a macro to search for the word 'warranty' on all the sheets and collect all the cells with the word 'warranty' onto a new sheet. I'm struggling to do this. It sounds very difficult to too. Any help greatly appreciated Thanks Suzanne |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need to find and collect all cells with a certain word onto a
Thanks Barb,
I had a play with this and I see it prints what I want to the Immediates windows. Thanks to both you and Gary for the help that I needed. "Barb Reinhardt" wrote: You could try something like this. I don't have time to add the sheet or put it on the sheet. I'm sure someone else can do that for you. Sub FindWarranty() Dim WS As Worksheet Dim myRange As Range Dim myNewRange As Range For Each WS In ActiveWorkbook.Worksheets Set myNewRange = WS.Cells(1, 1) Do Set myRange = myNewRange Set myNewRange = WS.Cells.Find(What:="Warranty", After:=myRange, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False) If myRange.Address < myNewRange.Address Then Debug.Print WS.Name, myNewRange.Address, myNewRange.Value End If Loop While myRange.Address < myNewRange.Address Next WS End Sub -- HTH, Barb Reinhardt "Grd" wrote: Hi there, I'm new to macros. Could anyone help me with a macro to search for the word 'warranty' on all the sheets and collect all the cells with the word 'warranty' onto a new sheet. I'm struggling to do this. It sounds very difficult to too. Any help greatly appreciated Thanks Suzanne |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I collect specific data from cells in a fill-in form? | Excel Discussion (Misc queries) | |||
Using VLOOKUP to find product from 3 worksheets to put on one shee | Excel Worksheet Functions | |||
Use VBA to collect information from Excel cells to av word documen | Excel Programming | |||
VBA Problem - How to collect and display text information from applicable cells. | Excel Programming | |||
how to read multiple workbooks, collect a few cells from each? | Excel Programming |