title: VBA: VLookup between two workbooks
body: <p>I am wondering if someone can help me out. I created a Userform with 3 comboboxes. Combobox 1 and 2 list all open workbooks. Combobox 3 lists the worksheets from Combobox 2. I now want to run a Vlookup. The lookup values are the values (in this case product codes) in each cell beginning at D9 to the last cell with a value in Column D of the first Worksheet of Combobox2's. The lookup range will be ("A5:S###"[number of rows varies depending on the file]"). 
  The Vlookup formula should be in the Column I of the first Worksheet of Combobox2's value starting at "I9" looping through each cell in I9 until all the Codes in D9 are looked up. 
  I keep getting error the major one being “Runtime-error '9'”: Subscript out of range. Here is my code.</p>
  
  <pre><code>Option Explicit
  
  Private Sub CancelButton_Click()
      Stopped = True
      Unload Me
  End Sub
  
  Private Sub ComboBox1_Change()
  Dim ScheduleA As Workbook
  Dim Termset As Worksheet
  Set ScheduleA = Workbooks(Me.ComboBox1.Value)
    With Me.ComboBox3
    For Each Termset In ScheduleA.Worksheets
              .AddItem Termset.Name
      Next Termset
      End With
  End Sub
  
  
  Private Sub FillACDButton_Click()
      Dim ACDRebateInfo As Worksheet
      Dim lastRow As Long
      Dim NewRebate As Single
      Dim NewRebateType As String
      Dim LookUp_Range As Range
      Dim ActionCode As String
      Dim ACD_NewRebate As Range
      Dim ACD_NewRebateType As Range
      Dim ACD_ActionCode As Range
      Dim SCC As Range
      Dim Cell As Range
      Set ACDRebateInfo = Workbooks(Me.ComboBox2.Value).Worksheets(1)
      Set ACD_NewRebate = ACDRebateInfo.Range("I9:I500")
      Set ACD_NewRebateType = ACDRebateInfo.Range("J9:J500")
      Set ACD_ActionCode = ACDRebateInfo.Range("B9:B500")
      Set LookUp_Range = Worksheets(Me.ComboBox3.Value).Range("A5:S400")
      Set SCC = ACDRebateInfo.Range("D9:D230")
      With ACDRebateInfo
      For Each Cell In ACD_ActionCode
      ActionCode = Application.WorksheetFunction.VLookup(SCC, LookUp_Range, 17, False)
      Next Cell
      End With
      Unload Me
  End Sub
  
  
  
  Private Sub UserForm_Initialize()
      Dim wkb As Workbook
          For Each wkb In Application.Workbooks
              Me.ComboBox1.AddItem wkb.Name
              Me.ComboBox2.AddItem wkb.Name
      Next wkb
  End Sub
  </code></pre>