find and insert macro

Hi all,
I am a new, basic user of excel. ..

I have 1 worksheet with 15000 + rows of data by about 15 columns.

I have a second worksheet with about 5000 rows of data.

Both sheets have data that is a unique identifier (becomes a primary key in microsoft access DB).

The second smaller sheet contains text that I want to insert into its related column in the first main worksheet.

What I am after is some sort of Find and insert “macro/module” that can take the unique identifier from the second sheet, search the first worksheet for it, and when it finds a match, it inserts data from its column H from its corresponding worksheets column H.

I can obviously use the find utility in excel, then manually scroll across and insert the data but doing this 5000 times will not prove practical.

Any ideas?

Hi Daniel

To insert the macro into your workbook

1) press Alt+F11 to open the VBA Window.

2) Press Ctrl+R to ensure the Project window is open.

3) Navigate to the VBAProject(workbookname) that corresponds to your open workbook, for example VBAProject(Book1). Click on this line

4) Select menu Insert>Module. There should be a large white pane to the right.

5) Copy in the following code.

Please Login or Register  to view this content.

6) To run, from your workbook, press Alt+F8 and select 'DanielCode'

-----

You may have to make the following changes:

* I have assumed you have a sheet called "Sheet 1" with the 15,000 lines in. If you have called this something else, replace all references to sheet1 in the code with your local name.

* Simarly Sheet2 contains the 5000 lines of data and may need to be renamed.

* I have assumed that column A contains you unique key in both sheets. If not replace the "A" with the appropriate column ID. If the key is in a different column in sheet 2, you only need to change this under "Position Cursor in Sheet 2".

* if you do change the column from column A then the "myRange.Offset(0, 7).Value = ActiveCell.Offset(0, 7).Value" code wont work. Column H is 7 cells to the right of column A. In this line of code the first "7" refers to sheet 1 and the second to sheet 2. You will need to change the "7" to an appropriate value. Note you can use "-7" if your keys were in column O, i.e. column H was to the left of the keys column.

* The macro will stop once a empty cell is found in the primary key column of sheet2. So make sure that there aren't any empty rows in the middle of your data.

---

How this works.

Option Explicit makes sure you declare all variables before you use them - good coding practice.

Dim myRange declares a variant type variable for later use. We dont need to initialise this variable.

Sheets("Sheet1").Activate and Range("A1").Activate makes sure that the cursor in Sheet 1 (the 15000 line sheet) is placed at the start of the primary key column (note as stated abaove change the "A" to something else if column A does not contain the primary keys)

Sheets("Sheet2").Activate and Range("A1").Activate does the same for (the 5000 line) sheet 2. By putting this statement after the Activate for Shhet 1 ensures that sheet2 is the active sheet.

While Not IsEmpty(ActiveCell.Value) has a corresponding Wend and checks that the primary key in sheet2 (remeber above we made sure that sheet 2 is the active sheet) is not empty. The macro will stop once this condtion (cell is empty) becomes true. ActiveCell starts as "A1" because when we made Sheet2 active we also Range("A1").Activate.

With Sheets("Sheet1").Range("A1", "A" & ActiveCell.SpecialCells(xlLastCell).Row) has a corresponding End With. This selects a range in Sheet1 from A1 to A999, where 999 is the row number of the last data cell in the sheet. A special VBA method ActiveCell.SpecialCells(xlLastCell).Row is used to determine this.

Set myRange = .Find(ActiveCell.Value, LookIn:=xlValues) stores a value in the previously declared myRange value, relating to the row containing the first occurence of the primary key. For example would contain 2 if the primary key is found in cell "A2".

If Not myRange Is Nothing Then has a corresponding End If and checks that a primary key was found.

myRange.Offset(0, 7).Value = ActiveCell.Offset(0, 7).Value sets a cell 7 columns to the right of the cell found in sheet1 to equal the cell 7 columsn to the right of the activecell in sheet2.

ActiveCell.Offset(1, 0).Activate drives the loop (remember the While... statement above) by moving to the next cell down in sheet2.

Hope this helps

Art