title: Loop through an excel range and fill text boxes from range
body: <p>I'm currently working on a userform within excel. It currently pulls a list from a database and pastes this into excel then references that data to autofill in textboxes when you select someones name. </p>
  
  <p>What I am having trouble with is I also want to autofill the access to certain systems a staff member will have. Basically the spreadsheet contains all staff within the company and the access they have to certain systems consisting of 2 cells (System and entitlement, columns K and L). I have defined the range I wish to work with but I am now stuck.</p>
  
  <p>How do I get excel to loop through the range and "Copy and paste" the data from each cell (eg K2 and l2) into text boxes in the userform. So what I want to happen is select someones name and it will automatically pull through all their access details and autofill some textboxes with that access and entitlement.</p>
  
  <p>Current code I have is as per below.
      Private Sub cboStaffNumber_Change()</p>
  
  <pre><code>Dim rngCell As Range
  Dim rngNumber As Range
  Dim lngRow As Long
  Dim lngRangeStart As Long
  Dim lngRangeEnd As Long
  Dim lngLastRow As Long
  Dim rngColumn As Range
  Dim rngEntitlement As Range
  Dim rngAccess As Range
  
  Set rngNumber = Range("A2:A" &amp; lngStaffDataLastRow)
  
  'Fills in the Staff Name, OIA Template, Division, Job Title and WAP Code fields when a staff member is selected
      If bCboBool = False Then
          If Me.cboStaffNumber.ListIndex &gt; 0 Then
              For Each rngCell In rngNumber.Cells
                  If rngCell.Value = Val(cboStaffNumber.Value) Then
  '                    lngRangeStart = rngCell.Row
                      bCboBool = True
                      Me.cboStaffName = rngCell.Offset(0, 1)
                      Me.txtOIATemplate = rngCell.Offset(0, 9)
                      Me.txtDivision = rngCell.Offset(0, 7)
                      Me.txtJobTitle = rngCell.Offset(0, 2)
                      Me.txtWAP = rngCell.Offset(0, 3)
                      Exit For
                  End If
              Next rngCell
          Else
              Me.cboStaffName.Value = ""
              Me.txtOIATemplate.Value = ""
              Me.txtDivision.Value = ""
              Me.txtJobTitle.Value = ""
              Me.txtWAP.Value = ""
          End If
      End If
  
  For lngRow = 2 To lngLastRow
      If rngNumber.Cells(lngRow, 1).Value = Val(cboStaffNumber.Value) Then
          lngRangeStart = lngRow
          Exit For
      End If
  Next lngRow
  
  '    For lngRow = lngRangeStart To lngLastRow + 1
  '        If rngNumber.Cells(lngRow, 1).Value &lt;&gt; Val(cboStaffNumber.Value) Then
  '            lngRangeEnd = lngRow
  '            Exit For
  '        End If
  '    Next lngRow
  '
  '    If lngRow &lt;&gt; 0 Then
  '        lngRangeEnd = lngRangeEnd - 1
  '    End If
  '
  '    For rngAccess = lngRangeStart To lngRangeEnd
  '        Set rngCell = lngRangeStart.Cells(rngCell, 11)
  '            For Each rngCell In rngAccess
  '                Set txtAccess1 = rngCell
  '                Exit For
  '    Next rngAccess
  
  
  
      bCboBool = False
  
  
  
  
  End Sub`
  </code></pre>
  
  <p>Any help would be greatly appreciated.</p>
  
  <p>Thanks</p>