Populating my worksheet with UserForm code

[Big Lar]

I’m building a UserForm to select data from a worksheet. These routines select a name from the sheet, populates the 2<SUP>nd</SUP> sheet of the workbook, preventing duplicates, then sorting the range alphabetically.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>

[FONT=Verdana]Private Sub UserForm_Initialize()<o:p></o:p>[/FONT]
[FONT=Verdana]<o:p></o:p>[/FONT]
[FONT=Verdana]Dim x&<o:p></o:p>[/FONT]
[FONT=Verdana]With Sheets("Roster")<o:p></o:p>[/FONT]
[FONT=Verdana] For x = 2 To .Cells(Rows.Count, "D").End(xlUp).Row<o:p></o:p>[/FONT]
[FONT=Verdana]     ComboBox1.AddItem (.Range("D" & x).Value)<o:p></o:p>[/FONT]
[FONT=Verdana] Next x<o:p></o:p>[/FONT]
[FONT=Verdana]End With<o:p></o:p>[/FONT]
[FONT=Verdana]<o:p></o:p>[/FONT]
[FONT=Verdana]End Sub<o:p></o:p>[/FONT]

&<o></o>
<o></o>

[FONT=Verdana]Private Sub ComboBox1_Change()<o:p></o:p>[/FONT]
[FONT=Verdana]<o:p></o:p>[/FONT]
[FONT=Verdana] Dim FullName As String<o:p></o:p>[/FONT]
[FONT=Verdana] FullName = Me.ComboBox1.Value<o:p></o:p>[/FONT]
[FONT=Verdana] Set WS = Worksheets("Event1")<o:p></o:p>[/FONT]
[FONT=Verdana] Dim CLoc As Range<o:p></o:p>[/FONT]
[FONT=Verdana] Set CLoc = WS.Columns("A:A").Find(What:=FullName, After:=WS.Cells(1, 1), LookIn:= _<o:p></o:p>[/FONT]
[FONT=Verdana]                         xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:= _<o:p></o:p>[/FONT]
[FONT=Verdana]                         xlNext, MatchCase:=False, SearchFormat:=False)<o:p></o:p>[/FONT]
[FONT=Verdana]<o:p></o:p>[/FONT]
[FONT=Verdana] If CLoc Is Nothing Then<o:p></o:p>[/FONT]
[FONT=Verdana]     iRow = WS.Cells(Rows.Count, 1) _<o:p></o:p>[/FONT]
[FONT=Verdana]            .End(xlUp).Offset(1, 0).Row<o:p></o:p>[/FONT]
[FONT=Verdana] Else<o:p></o:p>[/FONT]
[FONT=Verdana]     iRow = CLoc.Row<o:p></o:p>[/FONT]
[FONT=Verdana] End If<o:p></o:p>[/FONT]
[FONT=Verdana]<o:p></o:p>[/FONT]
[FONT=Verdana] WS.Cells(iRow, 1).Value = Me.ComboBox1.Value<o:p></o:p>[/FONT]
[FONT=Verdana] <o:p></o:p>[/FONT]
[FONT=Verdana] Range("A2:AZ" & iRow).Sort Key1:=Range("A2"), Order1:=xlAscending, Header:= _<o:p></o:p>[/FONT]
[FONT=Verdana]                            xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _<o:p></o:p>[/FONT]
[FONT=Verdana]                            DataOption1:=xlSortNormal<o:p></o:p>[/FONT]
[FONT=Verdana]<o:p></o:p>[/FONT]
[FONT=Verdana]Etc…etc…[/FONT]

<o></o>
<o></o>
Now, I find the need to include additional data associated with the FullName. Specifically, data from Sheet(Roster) Range “D” & “M” needs to fill Sheet(Event1) “A” & “B”.<o></o>
<o></o>
I’m afraid my brain is turning to mush as I try to figure out how to do it… Can anyone put me back on track <o></o>

 

[AlphaFrog]

This adds column M to the 2nd column in the combobox and hides the 2nd column. The data from the 2nd column is pasted to Event1 column B when the combobox value is pasted to Event1 column A

Private Sub UserForm_Initialize()
    Dim x&
    ComboBox1.ColumnCount = 2       ' The 2nd column is populated with column M
    ComboBox1.ColumnWidths = ";0"   ' Hide the 2nd column (column M)
    With Sheets("Roster")
    For x = 2 To .Cells(Rows.Count, "D").End(xlUp).Row
        ComboBox1.AddItem .Range("D" & x).Value
        ComboBox1.List(ComboBox1.ListCount - 1, 1) = .Range("M" & x).Value
    Next x
    End With
End Sub

Private Sub ComboBox1_Change()

 Dim FullName As String
 FullName = Me.ComboBox1.Value
 Set WS = Worksheets("Event1")
 Dim CLoc As Range
 Set CLoc = WS.Columns("A:A").Find(What:=FullName, After:=WS.Cells(1, 1), LookIn:= _
                         xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:= _
                         xlNext, MatchCase:=False, SearchFormat:=False)

 If CLoc Is Nothing Then
     iRow = WS.Cells(Rows.Count, 1) _
            .End(xlUp).Offset(1, 0).Row
 Else
     iRow = CLoc.Row
 End If

 WS.Cells(iRow, 1).Value = Me.ComboBox1.Value
 [COLOR="Red"]WS.Cells(iRow, "B").Value = Me.ComboBox1.List(Me.ComboBox1.ListIndex, 1)[/COLOR]
 
 Range("A2:AZ" & iRow).Sort Key1:=Range("A2"), Order1:=xlAscending, Header:= _
                            xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
                            DataOption1:=xlSortNormal