Easy Excel VBA script needed...

I have the following data in two worksheets...

{Worksheet1}
Date Time Home Team Visiting Team Field
-------------------- -------- ------------- ------------- ---------
Sunday, Apr-06-2003 10:00 AM Anderson
Sunday, Apr-06-2003 1:00 PM Anderson
Sunday, Apr-06-2003 4:00 PM Anderson
Sunday, Apr-06-2003 1:00 PM Concordia
Sunday, Apr-06-2003 4:00 PM Concordia
Sunday, Apr-06-2003 10:00 AM Del Valle
Sunday, Apr-06-2003 10:00 AM Del Valle
Sunday, Apr-06-2003 4:00 PM Del Valle
Sunday, Apr-06-2003 7:00 PM Del Valle
Sunday, Apr-06-2003 10:00 AM Downs
Sunday, Apr-06-2003 1:00 PM Downs
Sunday, Apr-06-2003 4:00 PM Downs
(702 rows)

{Worksheet2}
Date Time Home Team Visiting Team Field
-------------------- -------- ------------- ------------- --------
Sunday, Apr-06-2003 10:00 AM Red Wings Bombers Anderson
Sunday, Apr-06-2003 1:00 PM Angels Indians Anderson
Sunday, Apr-06-2003 4:00 PM Reds Red Sox Anderson
Sunday, Apr-06-2003 10:00 AM White Sox Pirates Downs
Sunday, Apr-06-2003 1:00 PM Diamondbacks Curve Downs
Sunday, Apr-06-2003 4:00 PM Devil Rays Bandits Downs
(510 rows)


I need a VBA script that will take the values in Worksheet2 and
copy them into the corresponding slot in Worksheet1 leaving the
empty slots.

In the examples above the ones for Concordia and Del Valle should
be left empty while the rest will be copied over from Worksheet2.

Anyone help out here?

Copy (Cntrl C)
Paste (Cntrl V)
Then Sort by date....

Why dont you parse through Sheet 1 trying to find a match for each record in Sheet2.

ie compare all rows in Sheet 1 with row 1 of sheet 2 and when you find a match or End Of List, increment the row you are using in Sheet 2 and do the comparisons all over again.

or even merge the cells that are going to be changing ie date time and field on both sheets, do a freakin huge vlookup and then demerge them all?

just two suggestions from the local idiot.

Cheers,

Goog

Copy (Cntrl C)
Paste (Cntrl V)
Then Sort by date....

But then, I'd have to delete all the rows that were empty that corresponded to games that were scheduled...

Fat chance!!!

Solution found at Experts-Exchange:


Sub Comp()
Dim rSource As Range, rTarget As Range, SourceRow%, TargetRow%

Set rSource = ActiveWorkbook.Sheets(2).Range("A2").CurrentRegion
Set rTarget = ActiveWorkbook.Sheets(1).Range("A2").CurrentRegion

For SourceRow = 2 To rSource.Rows.Count
For TargetRow = 2 To rTarget.Rows.Count
If rSource.Cells(SourceRow, 8).Value = rTarget.Cells(TargetRow, 8).Value And _
rSource.Cells(SourceRow, 3).Value = rTarget.Cells(TargetRow, 3).Value And _
rSource.Cells(SourceRow, 6).Value = rTarget.Cells(TargetRow, 6).Value Then
rTarget.Cells(TargetRow, 4).Value = rSource.Cells(SourceRow, 4).Value
rTarget.Cells(TargetRow, 5).Value = rSource.Cells(SourceRow, 5).Value
rTarget.Cells(TargetRow, 7).Value = rSource.Cells(SourceRow, 7).Value
End If
Next TargetRow
Next SourceRow
End Sub