VBA Excel - 需要编译错误对象

VBA Excel - Compile Error Object Required

提问by Bzmek

Disclosure: I'm fairly inexperienced at coding of most sorts but have a reasonable understanding of the logic behind it and quite often just need a little push with getting syntax's right etc.

披露：我对大多数类型的编码都相当缺乏经验，但对其背后的逻辑有合理的理解，而且通常只需要一点点推动语法正确等。

I posted the same code earlier but with a different problem and have no discovered this issue so I thought it best to create a new question for it

我之前发布了相同的代码，但有一个不同的问题，并且没有发现这个问题，所以我认为最好为它创建一个新问题

Objective:What I'm trying to do is create a spreadsheet where across the top row is a list of consecutive dates. In the first few columns is data for bills etc. What I want my macro to do is look at the amount of a bill, the start and end dates and the frequency of the bill (weekly/monthly etc) and then populate the cells in that same row in the columns of the dates where the bill is due. I've spent the last day coming up with this code and I was pretty happy with it until I went to run it. I've already got rid of a few bugs where I was using a variable.Value which apparently doesn't exist and I had messed up the syntax for Cells(row, column).

目标：我想要做的是创建一个电子表格，其中顶行是连续日期的列表。前几列是账单等数据。我希望我的宏做的是查看账单金额、开始和结束日期以及账单频率（每周/每月等），然后填充单元格账单到期日期列中的同一行。我花了最后一天的时间想出了这段代码，在运行它之前我对它非常满意。我已经摆脱了一些错误，其中我使用了一个显然不存在的 variable.Value，而且我搞砸了 Cells(row, column) 的语法。

The problem that I'm coming up against now is Compile Error: Object Required on this line:

我现在遇到的问题是 Compile Error: Object Required 在这一行：

Set dateAddress = Range("J1:AAI1").Find(currentDate, LookIn:=xlValues).Address 
'find the current date within the range of dates in row 1

What that line is supposed to be doing is searching across all the dates in the first row for that 'currentDate' and then storing that as dateAddress so that I can then use the dateAddress.Column in the next line of code along with the currentRow, to find the right cell that is to be populated with the bill amount.

该行应该做的是在第一行中搜索该“currentDate”的所有日期，然后将其存储为 dateAddress，以便我可以在下一行代码中使用 dateAddress.Column 和 currentRow，找到要填充账单金额的正确单元格。

Am I being clear enough? My code is below.

我说得够清楚了吗？我的代码如下。

My Code:

我的代码：

Private Sub CommandButton1_Click()
Dim currentDate As Date
Dim currentRow As Integer
Dim repeatuntilDate As Date
Dim repeatuntilRow As Integer
Dim dateAddress As Date
currentRow = 3 'First row of entries
repeatuntilRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row 'Last row of entries
While currentRow < repeatuntilRow 'Loop from first row until last row of entries
currentDate = Cells(currentRow, "G").Value 'Set Start Date
repeatuntilDate = Cells(currentRow, "H").Value 'Set End Date
    While currentDate <= repeatuntilDate 'Loop from Start Date until End Date
        Set dateAddress = Range("J1:AAI1").Find(currentDate, LookIn:=xlValues).Address 'find the current date within the range of dates in row 1
        Cells("dateAddress.Column,currentRow").Value = Cells("D,currentRow").Value 'Populate cell with amount
        'Increment the currentDate by the chosen frequency
        If Cells(currentRow, "E").Value = "Weekly" Then
            currentDate = DateAdd("ww", 1, currentDate)
        ElseIf Cells(currentRow, "E").Value = "Fortnightly" Then
            currentDate = DateAdd("ww", 2, currentDate)
        ElseIf Cells(currentRow, "E").Value = "Monthly" Then
            currentDate = DateAdd("m", 1, currentDate)
        ElseIf Cells(currentRow, "E").Value = "Quarterly" Then
            currentDate = DateAdd("q", 1, currentDatee)
        ElseIf Cells(currentRow, "E").Value = "6 Monthly" Then
            currentDate = DateAdd("m", 6, currentDate)
        ElseIf Cells(currentRow, "E").Value = "Annually" Then
            currentDate = DateAdd("y", 1, currentDate)
       ' ElseIf Cells(currentRow,"E").Value = "Once off" Then
           ' Exit While
        End If
    Wend
    currentRow = currentRow + 1 'Once row is complete, increment to next row down
Wend
End Sub

回答by simpLE MAn

1)

1)

Range("J1:AAI1").Find(currentDate, LookIn:=xlValues).Addresswill return an Addressin the form of a string (ex.: "$A$1") therefore you should declare dateAddressas a Stringinstead of Date.

Range("J1:AAI1").Find(currentDate, LookIn:=xlValues).Address将以Address字符串的形式返回 an （例如：“$A$1”），因此您应该声明dateAddress为 aString而不是Date。

---

---

2)

2)

Since a variable declared as a String(as in Dim dateAddress as String) is not an object, you should not use Setto initialize it. Hence,

由于声明为 a String(as in Dim dateAddress as String)的变量不是对象，因此不应使用Set来初始化它。因此，

Set dateAddress = Range("J1:AAI1").Find(currentDate, LookIn:=xlValues).Address

becomes

变成

dateAddress = Range("J1:AAI1").Find(currentDate, LookIn:=xlValues).Address

You should check THIS.

你应该检查这个。

---

---

3)

3)

Following the posted link logic, you could have declared a variable as:

按照发布的链接逻辑，您可以将变量声明为：

Dim dateRange as Range 'Range is an object
'And then Set your Range like:
Set dateRange = Range("J1:AAI1").Find(currentDate, LookIn:=xlValues) 'Because "Find" returns a range object

'And then with your dateAddress:
Dim dateAddress as String
dateAddress = dateRange.Address

回答by Floris

The addressproperty is not an object, so you don't need to use set.

该address属性不是对象，因此您不需要使用set.

Instead of

代替

Set dateAddress = Range("J1:AAI1").Find(currentDate, LookIn:=xlValues).Address

Use

用

dateAddress = Range("J1:AAI1").Find(currentDate, LookIn:=xlValues).Address