Easy Excel VBA script needed...

Posted on 2003-03-17
Medium Priority
Last Modified: 2010-05-03
I have the following data in two worksheets...
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)

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.

Question by:EddieShipman
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 11

Accepted Solution

Steiner earned 500 total points
ID: 8157601
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

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.

LVL 26

Author Comment

ID: 8159214
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.


Featured Post

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses
Course of the Month10 days, 21 hours left to enroll

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question