我已经创建了一个表格来重新格式化我收到的报告，但是我对部分报告的自动化存在疑问。一切正常，直到我定义并设置了最后一个可变代码长度，我想将其设置为已定义范围内的单元格（第一列，第二行）的长度。我收到运行时错误424，“对象必需”。感谢您的帮助！！
这是代码：
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
当您首先使用以下语法命名范围时
Dim rng as Range
Set rng = Range("A1:A10")
ActiveWorkbook.Names.Add Name:="codes", RefersTo:=rng
然后，这仅是一个名称-它不是一个独立的对象。因此，您得到的错误会告诉您确切的情况->所需的对象。
要引用命名的Range，请将其用双引号引起来并将其粘贴为Range对象的参数。因此，Range("codes")创建一个引用Range的Range对象rng。
一种替代，省略名称将是使用的rng范围对象简单地替换Range("codes").以rng.
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句