CODEWITHSUNDEEP
vbaexcel-2007
kcraig23
kcraig23

Reputation: 29

VBA Object Required Error

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!!

Here is the 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

Upvotes: 1

Views: 2880

Answers (1)

user2140173
user2140173

Reputation:

When you first name a range using the following syntax

Dim rng as Range
Set rng = Range("A1:A10")

ActiveWorkbook.Names.Add Name:="codes", RefersTo:=rng

Then this becomes just a name - it's not a stand alone object. So the error you are getting tells you exactly what is happening -> Object required.

To refer to the named Range you wrap it in double quotes and stick it as the parameter for the Range object. Therefore, Range("codes") creates a Range object referring to the rng Range.

An alternative, omitting the name would be to use the rng Range object simply replacing the Range("codes"). with rng.

Upvotes: 1

Related Questions