• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 158
  • Last Modified:

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.

0
Eddie Shipman
Asked:
Eddie Shipman
1 Solution
 
SteinerCommented:
Try the following:

Sub Comp()
    Dim rSource As Range, rTarget As Range, SourceRow%, TargetRow%
   
    Set rSource = ActiveWorkbook.Sheets(2).Range("A1").CurrentRegion
    Set rTarget = ActiveWorkbook.Sheets(1).Range("A1").CurrentRegion
   
    For SourceRow = 2 To rSource.Rows.Count
        For TargetRow = 2 To rTarget.Rows.Count
            If rSource.Cells(SourceRow, 1).Value = rTarget.Cells(TargetRow, 1).Value And _
                rSource.Cells(SourceRow, 2).Value = rTarget.Cells(TargetRow, 2).Value And _
                rSource.Cells(SourceRow, 5).Value = rTarget.Cells(TargetRow, 5).Value Then
                    rTarget.Cells(TargetRow, 3).Value = rSource.Cells(SourceRow, 3).Value
                    rTarget.Cells(TargetRow, 4).Value = rSource.Cells(SourceRow, 4).Value
            End If
        Next TargetRow
    Next SourceRow
End Sub

Comments:
rSource is your worksheet2, rTarget is worksheet1
I assume that the columns stay the same, so I only compare columns 1, 2 and 5. If they contain the same values, columns 3 and 4 are copied.

Greets
Steiner
0
 
Eddie ShipmanAll-around developerAuthor Commented:
One fellow on the newsgroups suggested I use a vlookup but
it was too complicated. I had to renumber the columns to
fit the sheet but it worked brilliantly.

Thanks...
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now