PDA

View Full Version : Help! I'm working with two spread sheets



pickens11735
06-19-2008, 04:13 PM
I'm a novice and I need someone's help.

I have two spreadsheets, sheet 1 and 2.

Both have rows like this, and 4 columns.

Sheet 1
First Middle Last Action
John B Smith
Frank M Jones
Lisa Q Clark

Sheet 2
First Middle Last Action
Thom K Barrow No
John B Smith Yes
Frank M Jones No
Lisa Q Clark Yes

I want the script to loop through each row or string name in sheet 1, trying to find a match of first, middle AND last name of those in sheet 2. When it finds a match on sheet 2, I want it to take the value associated with that person's name in sheet 2, and put that value next to the person's name on Sheet 1.

For example, it will start with Sheet 1, row one John B Smith. It will loop through all the rows on sheet 2. At the 2nd row it will match John and B and Smith. Then it will take the Yes value in the fourth column of the second row on sheet 2, and put that Yes value on the first sheet next to John B Smith.

Once it has done that for John B. Smith, it will move to row 2 on sheet 1 and do the same again.

JimmyTheHand
06-20-2008, 12:52 PM
Try this code:

Sub test()
Dim rngSource As Range, rngLookUp As Range, Hit As Range, cel As Range

Set rngSource = Sheets("Sheet1").Range("A2", Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp)).Offset(, 4)
Set rngLookUp = Sheets("Sheet2").Range("A2", Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp)).Offset(, 4)
rngSource.FormulaR1C1 = "=RC[-4] & ""_"" & RC[-3] & ""_"" & RC[-2]"
rngLookUp.FormulaR1C1 = "=RC[-4] & ""_"" & RC[-3] & ""_"" & RC[-2]"
For Each cel In rngSource
Set Hit = rngLookUp.Find(cel.Value, , xlValues, xlWhole, , , True)
If Not Hit Is Nothing Then cel.Offset(, -1) = Hit.Offset(, -1)
Next
rngSource.ClearContents
rngLookUp.ClearContents
End Sub
Notes
1) I'm using column E for temporary data storage. It can be changed to any column, if E is reserved fo other purposes.
2) I assumed data starts at cell A2 on each sheet.

HTH

Jimmy