Macro that finds word in table and reports all rows with data to new table

**jermsalerms** · 01-26-2007, 05:48 PM

Here is what I am trying to do. I am not sure how to write this macro.

Given:

Table1.xls
Contains a table on sheet2 A1:F1000.
(Ex. A1 = 456325 B1 = this is a website C1 = the name of the file is 1003_3.14.aspx)

Criteria.xls
Contains a list of text that I am searching for (A1:P1)
(Ex. A1 = 1003_3)

Results.xls
Blank

Goals:

1. find cell A1 on Criteria.xls and create a new sheet on Results.xls with that name. (Ex - Criteria.xls, A1 = 1003_3 then new sheet on Results.xls is called 1003_3)

2. find cell A1 on Criteria.xls and searches for ALL fields that contain that word in Work in Table1.xls, sheet2, range A1:F1000. (IMPORTANT - if my search criteria is 1003_3 and it finds that inside a "word" such as 1003_3.14.aspx then that should be accepted as a result.)

2. When it finds any combination of , Criteria.xls cell A1, on sheet2 of Table1.xls it copies the entire row from sheet2 on Table1.xls to the sheet created in Results.xls (sheet 1003_3) and continues to copy row after row until it has reached the end of the table (A1:F1000) on Table1.xls sheet2.

3. Then it restarts the entire process with the next line down (cell B1 on Criteria.xls; makes a new sheet named as the search criteria, and searches for the criteria as before) This continues until it reaches the end of the list on Criteria.xls

I have no idea where to start here...any suggestions?

**T-J** · 01-26-2007, 08:27 PM

Some ideas to accomplish the task, in order of your goals:

1. To create a new worksheet, use the Add method of the worksheets collection.
To set the worksheet name, use the Name property of the Worksheet object.

2. Use the Find method (good example in VB help) of the Range object to search.
Set the Range to Table1.xls, sheet2, range A1:F1000.
Set the optional LookAt argument to xlPart.
Find returns a Range object representing the first cell where data is found.

3. Use the EntireRow property and the Copy method of the Range object (the cell returned by Find) to copy to a Worksheet object.
Use the FindNext method of the Range object to continue searching.

4. Use For Each CriteriaCell In Range( .. ) to loop through cells on the criteria sheet.

**jermsalerms** · 01-30-2007, 11:08 AM

I am still learning VBA and dont really understand what you are saying. I have simplified what I am looking for a bit. Everything is now in one spreadsheet and no need for a macro to create sheets. Here is an update. Please help if you can.

_I. Sheet1 - 949 rows of data - Is a list of unique files
___A. Row 1 - column titles
___B. Row 2-949 - data being reviewed
___C. Column A - contains file names (ex. vsd.html) of next software publish
___D. Column B - Contains overview notes on each file
___E. Column C - contains detailed notes on each file
_II. Sheet2 - 804 rows of data - Is a list of files that may appear on multiple rows
___A. Row 1 - column titles
___B. Row 2-804 - data being reviewed
___C. Column A - contains file names (ex. vsd.html) of customized files
___D. Column B - contains version number
___E. Coulmn C - contains notes for version changes
III. Sheet 3 - Where output should go
____A. Row 1 - column titles
____B. Column A - will display data from Sheet 1, column A
____C. Column B - will display data from Sheet 1, column B
____D. Column C - will display data from Sheet 1, column C
____E. Column D - will display data from Sheet 2, column A
____F. Column E - will display data from Sheet 2, column B
____G. Column F - will display data from Sheet 2, column C
_IV. Goals
____A. create macro that runs the following.
_______1. selects Sheet1, A2 and searches for a match on Sheet2, Range A2:804.
_______2. if a match is found it copies range A2:C:2 to sheet 3, Range A2:C2
_______3. it then copies the matching rows data in columns A, B, & C and copies it to sheet 3, Range D2:F2
_______4. it continues to search through sheet 2Range A2:804 since there may be mutiple instances of the file being searched since sheet 2 lists all versions of the file. If multiple versions are found it copies the same data on each next row down on sheet 3.
____B. make macro loop.
_______1. once search of sheet 1, A2 is done it goes to the next line down and searches for that file and repeats the same steps
_______2. continues this until it has searched for all 948 rows on sheet 1.
____C. End result should be a new list that details all the files that will be affected by the next publish that have been customized

**jermsalerms** · 02-01-2007, 04:48 PM

I was able to get it done...half automated and half manual. Used a few autofilters and a few manual changes...would be nice to figure this out but this time I was under time constraints and had to improvise.

Thanks