# Thread: Macro Help Needed (2003/SP1)

1. ## Macro Help Needed (2003/SP1)

I need to write a macro to do the following:

- Read column C in worksheet "One"
- Compare each cell in column C to each cell in column D in worksheet "Two"
- If a cell in column C (worksheet "One") matches a cell in column D (worksheet "Two"), then copy the contents of a different cell on the same row column D (worksheet "Two") to another cell in Worksheet "One"
- Loop through column C in worksheet "One" until all cells have been test.

Any help will be greatly appreciated!
Caroline

2. ## Re: Macro Help Needed (2003/SP1)

You need to be a little more specific than "then copy the contents of a different cell". Are we just supposed to pick a random cell on the same row and copy it to a random cell anywhere on worksheet "One"?

3. ## Re: Macro Help Needed (2003/SP1)

Sorry I wasn't more precise. Please see if this is any better.

- Read column C in worksheet "One"
- Compare each cell in column C to each cell in column D in worksheet "Two"
- If the contents C2 (worksheet "One") matches the contents of any cell (row "x") in column D (worksheet "Two"), then copy the contents of "Ex" (column E, row x) in worksheet "Two" to "E2" in Worksheet "One"
- Loop through column C in worksheet "One" until all cells have been tested

Caroline

4. ## Re: Macro Help Needed (2003/SP1)

You could do this without a macro. The formula in E1 on "One" would be

=IF(ISNA(VLOOKUP(C1,Two!\$D\$1:\$E\$5,2,FALSE)),"",VLO OKUP(C1,Two!\$D\$1:\$E\$5,2,FALSE))

(replace 5 with the row number of the last used row in column D on "Two". Fill down as far as needed. If you want a macro, try this:

Sub MatchOneTwo()
Dim wshSource As Worksheet
Dim wshTarget As Worksheet
Dim lngRow As Long
Dim lngMaxRow As Long
Dim lngMaxTargetRow As Long
Dim rngCell As Range
Dim rngTarget As Range

Set wshSource = Worksheets("One")
Set wshTarget = Worksheets("Two")

lngMaxTargetRow = wshTarget.Range("D65536").End(xlUp).Row
Set rngTarget = wshTarget.Range("D1" & lngMaxTargetRow)

lngMaxRow = wshSource.Range("C65536").End(xlUp).Row
For lngRow = 1 To lngMaxRow
Set rngCell = rngTarget.Find( _
What:=wshSource.Range("C" & lngRow), _
LookAt:=xlWhole)
If Not rngCell Is Nothing Then
wshSource.Range("E" & lngRow) = rngCell.Offset(0, 1)
End If
Next lngRow

Set rngCell = Nothing
Set rngTarget = Nothing
Set wshTarget = Nothing
Set wshSource = Nothing
End Sub

The code can easily be modified to start in row 2 instead of row 1.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•