VBA Excel - 需要编译错误对象 [英] VBA Excel - Compile Error Object Required

问题描述

披露：对于大多数类型的编码来说，我的经验是相当缺乏经验的，但是对它背后的逻辑有一个合理的理解，通常只需要稍微推一下，获得语法正确等。

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

目标：我想要做的是创建一个电子表格，其中顶行是连续日期的列表。在前几列是账单数据等。我想要我的宏做什么是看帐单的金额，帐单的开始和结束日期和频率（每周/每月等），然后填充单元格在账单到期日期的列中的同一行。我花了最后一天提出这个代码，我很高兴，直到我去运行它。我已经摆脱了几个错误，我使用的是一个变量.Value，显然不存在，我已经搞砸了Cells（row，column）的语法。

我现在遇到的问题是编译错误：对象此行上必需：

 设置dateAddress = Range（J1：AAI1）Find（currentDate，LookIn：= xlValues）.Address 
'查找行1中日期范围内的当前日期
  / pre> 
 
 
该行应该做的是在第一行的所有日期搜索该currentDate，然后将其存储为dateAddress，以便我可以然后在下一行代码中使用dateAddress.Column和currentRow，找到要填充的帐单金额的正确单元格。
 
 
 
我是足够清楚我的代码在下面。
 
 
 
我的代码：
  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'第一行条目
 repeatuntilRow = Cells.Find（*，SearchOrder = = xlByRows，SearchDirection：= xlPrevious）.Row'最后一行条目
当currentRow<重复循环从第一行到最后一行条目
 currentDate = Cells（currentRow，G）。值'设置开始日期
 repeatuntilDate = Cells（currentRow，H）。日期
 while currentDate< = repeatuntilDate'从开始日期到结束日期循环
设置dateAddress =范围（J1：AAI1）。Find（currentDate，LookIn：= xlValues）.Address'查找当前日期在行1的日期范围内
单元格（dateAddress.Column，currentRow）Value = Cells（D，currentRow）。Value'填充单元格
'将currentDate增加选择的频率
如果Cells（currentRow，E）。Value =Weekly然后
 currentDate = DateAdd（ww，1，currentDate）
 ElseIf Cells（currentRow，E ）.Value =Fortnightlythen 
 currentDate = DateAdd（ww，2，currentDate）
 ElseIf Cells（currentRow，E）。Value =MonthlyThen 
 currentDate = DateAdd（m，1，currentDate） 
 ElseIf Cells（currentRow，E）。Value =Quarterly然后
 currentDate = DateAdd（q，1，currentDatee）
 ElseIf Cells（currentRow，E）。 Value =6 Monthly然后
 currentDate = DateAdd（m，6，currentDate）
 ElseIf Cells（currentRow，E）。Value =AnnuallyThen 
 currentDate = DateAdd （y，1，currentDate）
'ElseIf Cells（currentRow，E）。Value =一次关闭然后
'退出而
结束如果
 Wend 
 currentRow = currentRow + 1'一行完成后，递增到下一行
 Wend 
 End Sub 
  
 
 
解决方案
  1） 
 
 
 
 范围（J1：AAI1）Find（currentDate，LookIn：= xlValues）.Address 将以字符串的形式返回一个地址例如：$ A $ 1）因此，您应该将 dateAddress 声明为 String 而不是日期。
 
 
 
 
 
 
  2） 
 
 
 
由于声明为 String （如 Dim dateAddress as String ）不是一个对象，你不应该使用 Set 来初始化它。因此，
 设置dateAddress = Range（J1：AAI1）Find（currentDate，LookIn：= xlValues）.Address 
  
成为
  dateAddress = Range（J1：AAI1）Find（currentDate，LookIn：= xlValues）.Address 
  
您应该检查 这个 。
 
 
 
 
 
 
  3） 
 
 
 
按照发布的链接逻辑，您可以声明一个变量：
  Dim dateRange as Range'Range是一个对象
'然后设置你的范围，如：
设置dateRange = Range（J1：AAI1）Find（currentDate，LookIn：= xlValues）'因为Find 返回一个范围对象
 
'然后与你的dateAddress：
 Dim dateAddress as String 
 dateAddress = dateRange.Address 
  
 
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).

The problem that I'm coming up against now is Compile Error: Object Required on this line: 
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.

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

解决方案
1)

Range("J1:AAI1").Find(currentDate, LookIn:=xlValues).Address will return an Address in the form of a string (ex.: "$A$1") therefore you should declare dateAddress as a String instead of Date.



2)

Since a variable declared as a String (as in Dim dateAddress as String) is not an object, you should not use Set to initialize it.  Hence,
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)

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


                        
这篇关于VBA Excel  - 需要编译错误对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！