title: Three cascading Comboboxes
body: <p>I have a relatively large amount of data that I am trying to convert into three <code>comboboxes</code> in an <code>excel userform</code>. Basically I am trying to create a purchase order system for construction products. This involves the three comboboxes, basically broken down into "Category", "Sub Category" and "Product". </p>
  
  <p>I found some code that uses the <code>.offset</code> function, however the only way I found to use this was very time consuming. I am very new to VBA. </p>
  
  <p>Is there a way to use this code or another code to effectively sort through my data rather than me manually changing the offset each time?</p>
  
  <pre><code>Private Sub UserForm_Initialize()
  
  Dim ws As Worksheet
  Dim LR As Long
  Dim Cell As Range
  Dim List As New Collection
  Dim Item As Variant
  Set ws = ActiveSheet
  
  With ws
  LR = Sheet1.Cells(.Rows.Count, 1).End(xlUp).Row
  For Each Cell In .Range("A2:A" &amp; LR)
      With Cell
          On Error Resume Next
          List.Add .Text, CStr(.Value)
          On Error GoTo 0
      End With
  Next Cell
  For Each Item In List
      ComboBox1.AddItem Item
      Next Item
  End With
  End Sub
  
  Private Sub ComboBox1_Change()
  
  Dim ws As Worksheet
  Dim LR As Long
  Dim Cell As Range
  Dim List As New Collection
  Dim Item As Variant
  Set ws = ActiveSheet
  
  
  With ws
      LR = Sheet1.Cells(.Rows.Count, 1).End(xlUp).Row
      ComboBox2.Clear
      For Each Cell In .Range("A2:A" &amp; LR)
          With Cell
              If .Text = ComboBox1.Value Then
                  On Error Resume Next
                  List.Add .Offset(0, 1).Text, CStr(.Offset(0, 1).Value)
                  On Error GoTo 0
              End If
          End With
      Next Cell
      For Each Item In List
          ComboBox2.AddItem Item
      Next Item
  End With
  
  
  End Sub
  
  Private Sub ComboBox2_Change()
  
  Dim ws As Worksheet
  Dim LR As Long
  Dim Cell As Range
  Dim List As New Collection
  Dim Item As Variant
  Set ws = ActiveSheet
  
  With ws
      LR = Sheet1.Cells(.Rows.Count, 1).End(xlUp).Row
      ComboBox3.Clear
      For Each Cell In .Range("A2:A" &amp; LR)
          With Cell
              If .Text = ComboBox1.Value Then
                  If .Offset(0, 1).Text = ComboBox2.Value Then
                  On Error Resume Next
                  List.Add .Offset(0, 2).Text, CStr(.Offset(0, 2).Value)
                  On Error GoTo 0
              End If
          End If
          End With
      Next Cell
      For Each Item In List
          ComboBox3.AddItem Item
      Next Item
  End With
  
  End Sub
  </code></pre>
  
  <p>Any help would be awesome! </p>