VBA to output Vendor questionnaire results to variables columns based on input selection

[hanjunxi80]

Greetings,

This is my first post and I hope the result is as helpful to others as it will be to me. Attached is a questionnaire built to evaluate proposal provide by potential vendors. The questionnaire was built using the Fibonacci scale to weight the importance of each statements to the organization. That is the "behind the scenes" bit.

Participants select the vendor they are rating from the list of potential vendors (variable) then are asked to rate how well the proposal answers our request for proposals (RFPs) questions. To do this they will rate 1-3 (poor to good) or answer "yes" or "no" for each statement. This range is static.

My challenge is the variable output. When the user of the form selects the vendor they will grade, and once they are complete, they will push submit. When they push submit, I'm working have their outputs saved to another sheet under the corresponding vendor name. This will all the user, once all vendors are review, to review their ratings side by side. I've attached file I am working with and what I've established so far. I'm no VBA pro...id the variable input field "Select_Vendor" and define the output columns based on vendor selection. I'm not quite sure how to handle each participants input field which are static but not in sequential rows. I appreciate anyone's input and I hope this is enough to give you an idea of what I'm trying to accomplish. Thanks you.

Questionaire - .xlsm

Sub Submit()
'
Dim Select_Vendor As String
Dim colVar As Integer


'Set Vendor Columns

    If Selection_Vendor = "1" Then colVar = Sheets("RFP Composite").Range("d11:d120")
                  
    ElseIf Selection_Vendor = "2" Then colVar = Sheets("RFP Composite").Range("f11:f120")
    
    ElseIf Selection_Vendor = "3" Then colVar = Sheets("RFP Composite").Range("h11:h120")
    
    ElseIf Selection_Vendor = "4" Then colVar = Sheets("RFP Composite").Range("j11:j120")
   
    ElseIf Selection_Vendor = "5" Then colVar = Sheets("RFP Composite").Range("l11:l120")
    
    ElseIf Selection_Vendor = "6" Then colVar = Sheets("RFP Composite").Range("n11:n120")
    
    ElseIf Selection_Vendor = "7" Then colVar = Sheets("RFP Composite").Range("p11:p120")
    
        Loop
    
            colvar.row = source cell
    
    Sheets("RFP Composite").Select Range("D7").Select ActiveCell.FormulaR1C1 = "='RFP Review'!R[2]C[-1]"
    Range("D8").Select
    ActiveCell.FormulaR1C1 = "='RFP Review'!R[2]C[-1]"
    Range("D9").Select
    ActiveCell.FormulaR1C1 = "='RFP Review'!R[2]C[-1]"
    Range("D10").Select
    ActiveCell.FormulaR1C1 = "='RFP Review'!R[2]C[-1]"
    Range("D16").Select
    ActiveCell.FormulaR1C1 = "='RFP Review'!R[2]C[-1]"
    Range("D17").Select
    ActiveWindow.SmallScroll Down:=6
    Range("D20").Select
    ActiveCell.FormulaR1C1 = "='RFP Review'!R[2]C[-1]"
    Range("D21").Select