为什么下面的VBA代码出现对象必需的错误? [英] Why do I get object required error for the below VBA code?

问题描述

我是VBA的新手，我需要帮助，将下面的VBA代码应用于按地区和按型号划分的两个特定工作表.该代码仅查找名称为年份总计的最后一列，然后将前几个月的值复制到新列中.但是我在ws上得到了一个错误对象.

I am new to VBA, I need help applying the below VBA code to two specific work sheets by Region and by Model. The code simply finds the last column which has the name total for the year and copies the previous months values into a new column. But I get an error object required at ws.

   Sub Insert_New_Col()

Dim Found As Range, BeforeR As Long
Dim xSheets As Variant
Dim ws As Worksheet
Dim i As Long

xSheets = Array("By Region", "By Model") '

For i = LBound(xSheets) To UBound(xSheets)

    Set ws = xSheets(i)
    Set Found = ws.Rows(3).Find(What:="Total for the Year", Lookat:=xlWhole)
    BeforeR = R.Column - 1

    If Found Is Nothing Then
        MsgBox ("The word 'Totals' was not found in Row 5 on Sheet: " & ws.Name)
    Else
        Columns(BeforeR).Copy
        ws.Columns(R.Column).Insert Shift:=xlRight
    End If
Next i

End Sub

推荐答案

您需要 Worksheets 集合的实际成员:

You want an actual member of the Worksheets collection:

Set ws = ThisWorkbook.Worksheets(xSheets(i))

---

BeforeR = R.Column-1

什么是 R ?在模块顶部添加 Option Explicit ，并声明所有变量.

What is R? Add Option Explicit to the top of the module and declare all variables.

这篇关于为什么下面的VBA代码出现对象必需的错误?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！