Need help using data from UserForm

[joshhud]

Hi all, multi part question. I have a UserForm, where the user selects 1 or more of 52 fields and i need those fields pasted as headers on another file. Originally i accomplished this with the code below, but i fee like there is a better way.

Private Sub CommandButton1_Click()
Dim index As Integer
Dim cell As Range
Set ls = ActiveWorkbook.Worksheets("List")
Set ASR = ActiveWorkbook.Worksheets("Combined ASR")
Set cell = ls.Range("C1")
For index = 0 To UserForm1.ListBox1.ListCount - 1
If UserForm1.ListBox1.Selected(index) Then
cell.Value = UserForm1.ListBox1.List(index)
Set cell = cell.Offset(1)
End If
Next



Unload UserForm1
Worksheets("List").Range("C1", Cells(Rows.Count, "C").End(xlUp)).Copy
Worksheets("Combined ASR").Range("A1").PasteSpecial Transpose:=True

End Sub

Secondly, i need to perform a vlookup to another dataset based on the fields they select. Currently i cannot think of a way to dynamically have it identify which field was selected and perform the vlookup from there. My only thought is to do something like this. But again i feel like there is a better way since field BA could be any of the available 52 fields. ( i only included 7 of the 52 fields below as an example).

Sub fields()

Set ASR = ActiveWorkbook.Worksheets("Combined ASR")

Dim F1 As String
Dim F2 As String
Dim F3 As String
Dim F4 As String
Dim F5 As String
Dim F6 As String
Dim F7 As String

F1 = "=VLOOKUP('Combined ASR'!A2,HR_Data!A:BA,2,0)"
F2 = "=VLOOKUP('Combined ASR'!A2,HR_Data!A:BA,3,0)"
F3 = "=VLOOKUP('Combined ASR'!A2,HR_Data!A:BA,4,0)"
F4 = "=VLOOKUP('Combined ASR'!A2,HR_Data!A:BA,5,0)"
F5 = "=VLOOKUP('Combined ASR'!A2,HR_Data!A:BA,6,0)"
F6 = "=VLOOKUP('Combined ASR'!A2,HR_Data!A:BA,7,0)"
F7 = "=VLOOKUP('Combined ASR'!A2,HR_Data!A:BA,8,0)"

If ASR.Range("BA1") = "EMP_Status_CD" Then
ASR.Range("BA2") = F1
ElseIf ASR.Range("BA1") = "EMP_DEPARTMENT_ID" Then
ASR.Range("BA2") = F2
ElseIf ASR.Range("BA1") = "EMP_DEPARTMENT_NAME" Then
ASR.Range("BA2") = F3
ElseIf ASR.Range("BA1") = "LEVEL5_ID" Then
ASR.Range("BA2") = F4
ElseIf ASR.Range("BA1") = "LEVEL5_NAME" Then
ASR.Range("BA2") = F5
ElseIf ASR.Range("BA1") = "LEVEL6_ID" Then
ASR.Range("BA2") = F6
Else
ASR.Range("BA1") = "LEVEL6_NAME"
ASR.Range("BA2") = F7
End If

End Sub

 

[joshhud]

Sorry should have read FAQ first

Private Sub CommandButton1_Click()
Dim index As Integer
Dim cell As Range
Set ls = ActiveWorkbook.Worksheets("List")
Set ASR = ActiveWorkbook.Worksheets("Combined ASR")
Set cell = ls.Range("C1")
For index = 0 To UserForm1.ListBox1.ListCount - 1
If UserForm1.ListBox1.Selected(index) Then
cell.Value = UserForm1.ListBox1.List(index)
Set cell = cell.Offset(1)
End If
Next



Unload UserForm1
Worksheets("List").Range("C1", Cells(Rows.Count, "C").End(xlUp)).Copy
Worksheets("Combined ASR").Range("A1").PasteSpecial Transpose:=True

End Sub

Sub fields()

Set ASR = ActiveWorkbook.Worksheets("Combined ASR")

Dim F1 As String
Dim F2 As String
Dim F3 As String
Dim F4 As String
Dim F5 As String
Dim F6 As String
Dim F7 As String

F1 = "=VLOOKUP('Combined ASR'!A2,HR_Data!A:BA,2,0)"
F2 = "=VLOOKUP('Combined ASR'!A2,HR_Data!A:BA,3,0)"
F3 = "=VLOOKUP('Combined ASR'!A2,HR_Data!A:BA,4,0)"
F4 = "=VLOOKUP('Combined ASR'!A2,HR_Data!A:BA,5,0)"
F5 = "=VLOOKUP('Combined ASR'!A2,HR_Data!A:BA,6,0)"
F6 = "=VLOOKUP('Combined ASR'!A2,HR_Data!A:BA,7,0)"
F7 = "=VLOOKUP('Combined ASR'!A2,HR_Data!A:BA,8,0)"

If ASR.Range("BA1") = "EMP_Status_CD" Then
ASR.Range("BA2") = F1
ElseIf ASR.Range("BA1") = "EMP_DEPARTMENT_ID" Then
ASR.Range("BA2") = F2
ElseIf ASR.Range("BA1") = "EMP_DEPARTMENT_NAME" Then
ASR.Range("BA2") = F3
ElseIf ASR.Range("BA1") = "LEVEL5_ID" Then
ASR.Range("BA2") = F4
ElseIf ASR.Range("BA1") = "LEVEL5_NAME" Then
ASR.Range("BA2") = F5
ElseIf ASR.Range("BA1") = "LEVEL6_ID" Then
ASR.Range("BA2") = F6
Else
ASR.Range("BA1") = "LEVEL6_NAME"
ASR.Range("BA2") = F7
End If

End Sub