title: VBA Object Required Error
body: <p>I've created a form to reformat a report that I receive and I'm having an issue with automating part of it. Everything works until I define and set the last variable codelength which I want to set as the length of a cell (first column, second row) in a defined range. I receive run time error 424, "Object Required". I appreciate any help!!</p>
  
  <p>Here is the code:</p>
  
  <pre><code>Private Sub CommandButton1_Click()
  
  
  Application.ScreenUpdating = False
  
  Dim rg As Range
  Dim rgg As Range
  Dim Addr1 As String
  Dim Addr2 As String
  
  'Get the address, or reference, from the RefEdit control.
  Addr1 = RefEdit1.Value
  Addr2 = RefEdit2.Value
  
  'Set the SelRange Range object to the range specified in the
  'RefEdit control.
  Set rg = Range(Addr1)
  Set rgg = Range(Addr2)
  
  ActiveWorkbook.Names.Add Name:="codes", RefersTo:=rgg
  
  'Infill
  'Copies the value from the row above into blank cells.
  Dim cel As Range, col As Range
  Set rg = Range(Addr1).Columns(1).Resize(, 2)
  On Error Resume Next
  For Each col In rg.Columns
      Set rgg = Nothing
      Set rgg = col.SpecialCells(xlCellTypeBlanks)
      If Not rgg Is Nothing Then
          rgg.FormulaR1C1 = "=R[-1]C"     'Blank cells set equal to value from row above
          rgg.Formula = rgg.Value   'Optional:  Replace the formulas with the values returned by the formulas
      End If
  Next
  Set rgg = rg.Offset(1, 0).Resize(rg.Rows.Count - 1, rg.Columns.Count)
  For Each cel In rgg.Cells
      If cel = "" Then cel.Value = cel.Offset(-1, 0).Value
  Next
  On Error GoTo 0
  
  'ColCDeleter
  Dim i As Long, n As Long
  Set rg = Intersect(ActiveSheet.UsedRange, Range(Addr1).Columns(3))
  n = rg.Rows.Count
  For i = n To 1 Step -1
  If rg.Cells(i, 1) = "" Then rg.Cells(i, 1).EntireRow.Delete
  Next
  
  'insert corresponding values
  Dim codelength As Integer
  codelength = Len(codes.Cells(2, 1).Value)
  rg.Columns(2).EntireColumn.Insert
  rg.Columns(2).EntireColumn.Insert
  rg.Columns(2).EntireColumn.Insert
  rg.Columns(2).EntireColumn.Insert
  If codelength = 6 Then
  rg.Columns(2).FormulaR1C1 = "=VLOOKUP((MID(RC1,9,9)),codes,2,FALSE)"
  rg.Columns(3).FormulaR1C1 = "=VLOOKUP((MID(RC1,9,9)),codes,3,FALSE)"
  Else
  rg.Columns(2).FormulaR1C1 = "=VLOOKUP((MID(RC1,8,9)),codes,2,FALSE)"
  rg.Columns(3).FormulaR1C1 = "=VLOOKUP((MID(RC1,8,9)),codes,3,FALSE)"
  End If
  rg.Cells(1, 2).Value = "Plan"
  rg.Cells(1, 3).Value = "Status"
  
  
  'Unload the userform.
  Unload Me
  
  
  
  End Sub
  </code></pre>