VBA to copy and match to master list

[scoha]

I have a list of names and scores (Col A and B)

The list of names is a subset of the master list of names on another sheet.

Each time I run an event I want to copy the score for each name to the respective name in the master list and the next column. There is a new column for each event.

The subllist of names will vary with each event but will always comprise names that are in the master list.

Top 3 rows of each new column in the master list contain the event date, location and name.

For each name in the sublist, the code needs to copy the score for that event and name, and then paste it into the respective name in the master list, in the next most right column corresponding to the event column.

Can anyone help me in my quest?

 

[VHS01]

socha,
<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> CompareSheets()<br>    <SPAN style="color:#00007F">Dim</SPAN> a <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>, b <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>, x <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>, LastCol <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br>    <SPAN style="color:#007F00">'Determine the last column used in the Master</SPAN><br>    LastCol = Sheets("Master").Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column<br>    <br>    <SPAN style="color:#007F00">'I needed a little more info here as to where the name of the event was found on the "Event" sheet</SPAN><br>    <SPAN style="color:#007F00">'I made the assumption it will be just like the Master "Top 3 rows" in order of "date, location and name"</SPAN><br>    <SPAN style="color:#007F00">'So this looks for the Event name in A3</SPAN><br>    <br>    <SPAN style="color:#007F00">'Next, identify which column in the Master is the correct Event</SPAN><br>    <SPAN style="color:#00007F">For</SPAN> x = 1 <SPAN style="color:#00007F">To</SPAN> LastCol<br>        <SPAN style="color:#00007F">If</SPAN> Sheets("Master").Cells(3, x).Value = Sheets("Event1").Range("A3").Value <SPAN style="color:#00007F">Then</SPAN><br>            <SPAN style="color:#007F00">'x now becomes the column in the Master that matches A3 in the Event</SPAN><br>            EventCol = x<br>            <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">For</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> x<br>    <SPAN style="color:#007F00">'Next Match a name in the Event to the Master List</SPAN><br>    <SPAN style="color:#00007F">For</SPAN> a = 4 <SPAN style="color:#00007F">To</SPAN> Sheets("Event1").Range("A65536").End(xlUp).Row<br>        <SPAN style="color:#00007F">For</SPAN> b = 4 <SPAN style="color:#00007F">To</SPAN> Sheets("Master").Range("A65536").End(xlUp).Row<br>            <SPAN style="color:#00007F">If</SPAN> Sheets("Event1").Range("A" & a).Value = Sheets("Master").Range("A" & b).Value <SPAN style="color:#00007F">Then</SPAN><br>                <SPAN style="color:#007F00">'Finally in the Master, write the score for the person in the next column over from the event</SPAN><br>                Sheets("Master").Cells(b, x + 1).Value = Sheets("Event1").Range("B" & a).Value<br>                <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">For</SPAN><br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">Next</SPAN> b<br>    <SPAN style="color:#00007F">Next</SPAN> a<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>
Gary

 

[scoha]

Thanks a lot VHS01 - I was kind of heading in the right track but seeing your code it would have been a long way to go for me (VBA L plater).

Ive used your code and added a few bits and used the right cell references for the master sheet ("Roubaix") and the Event1 sheet ("Race sheet").

I get a Type Mismatch error which I cant trace -was hoping it might be obvious to you??

Anyway thanks for the help,

Here is my finished code

Sub SubmitResults()

Dim LastCol As Integer
Dim EventCol As Integer
Dim a As Integer
Dim b As Integer
Dim x As Integer



'Determine next available column in Roubaix
LastCol = Sheets("Roubaix").Cells.Find(What:="*", After:=[A9], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

'Identify correct column in Roubaix that matches event
For x = 1 To LastCol
    If Sheets("Roubaix").Cells(3, x).Value = Sheets("Race sheet").Range("c2").Value Then
    EventCol = x
    Exit For
    End If
Next x

'write event course and distance info to event column
Sheets("Roubaix").Cells(4, x + 1).Value = Sheets("Race sheet").Range("c3").Value
Sheets("Roubaix").Cells(5, x + 1).Value = Sheets("Race sheet").Range("e3").Value

'Match rider name from Race Sheet with Roubaix master list
For a = 7 To Sheets("Race sheet").Range("a65536").End(xlUp).Row
    For b = 9 To Sheets("Roubaix").Range("a65536").End(xlUp).Row
        If Sheets("Race sheet").Range("a" & a).Value = Sheets("Roubaix").Range("a" & b).Value Then
            Sheets("Roubaix").Cells(b, x + 1).Value = Sheets("Race sheet").Range("k" & a).Value
            
    Exit For
   End If
  Next b
Next a

End Sub

 

[VHS01]

scoha,

I have been sitting here for awhile trying to create a "dummy" workbook to simulate yours, but I have too many questions. I think I can help you alot easier if I see the actual workbook.

If you can, sanitize the data and email me the actual book with any other instructions, it probably won't take me long to finish it up.

Lets use the site email function to keep our addresses anonymous.
Click on my name to get to the info page and under the "Contact Info" tab select "Send a private message to VHS01". Once I hear from you I will reply with an email address where you can send the actual book.

For any others interested in this thread, I will post the answer back here.

Gary

 

[VHS01]

Schoa,
I made a dummy workbook and your code seemed to work fine.

On the dummy, the only change I made was how to find LastCol:

LastCol = Sheets("Roubaix").Cells(3, Sheets("Roubaix").Columns.Count).End(xlToLeft).Column

After that, your code worked fine.

If you still have problem email me as described above.

Gary