title: Excel Workbook Trouble
body: <p>Everything is good up until I get to the loop. </p>
  
  <p>Once I get to the loop it runs through it's cycle adjusting the first name and last names into two different columns, but then, for some reason, it gives me an error. I can't figure out why.</p>
  
  <p>Code below:</p>
  
  <pre><code>Option Explicit
  
  Public Sub BreakNameApart()
      'declare variables and assign address to Worksheet object variable
      Dim intLocation As Integer, shtConsult As Worksheet, rngCell As Range
      Set shtConsult = _
          Application.Workbooks("Franklin Consultants.xls").Worksheets("Consultants")
      shtConsult.Columns("b").Insert
      shtConsult.Range("a4").Value = "Last Name"
      shtConsult.Range("b4").Value = "First Name"
      shtConsult.Range("a4:b4").Font.Bold = True
      'beginning in cell A5, seperate each full name into last and first name
      Set rngCell = shtConsult.Range("a5")
      Do Until rngCell.Value = ""
          'find location of space
          intLocation = InStr(1, rngCell.Value, " ")
          'assign first name to appropriate cell in column B
          rngCell.Offset(columnoffset:=1).Value = _
                  Left(String:=rngCell.Value, Length:=intLocation - 1)
          'assign last name to current cell
          rngCell.Value = Mid(String:=rngCell.Value, Start:=intLocation + 1)
          'assign the address of the cell in the next row to the rngCell variable
          Set rngCell = rngCell.Offset(rowoffset:=1)
      Loop
     'adjust the width of columns A and B
     shtConsult.Columns("a:b").AutoFit
  
  
  End Sub
  </code></pre>
  
  <hr>
  
  <p>Most notably it gives me an error here:</p>
  
  <pre><code>rngCell.Offset(columnoffset:=1).Value = _
                  Left(String:=rngCell.Value, Length:=intLocation - 1)
  </code></pre>