披露:我对大多数类型的编码都没有足够的经验,但是对它背后的逻辑有一个合理的了解,并且经常需要一点点推动就可以正确地使用语法。
我之前发布了相同的代码,但是有一个不同的问题,并且没有发现此问题,因此我认为最好为其创建一个新问题
目标:我想做的是创建一个电子表格,其中在第一行中是连续日期的列表。前几列是帐单等的数据。我想让我的宏执行的工作是查看帐单的金额,开始和结束日期以及帐单的频率(每周/每月等),然后在其中填充单元格。帐单到期日期列中的同一行。我花了最后一天来编写这段代码,直到开始运行它之前,我对此都很满意。我已经摆脱了一些我在使用variable.Value值时显然不存在的错误,并且弄乱了Cells(row,column)的语法。
我现在遇到的问题是“编译错误:此行上需要对象”:
Set dateAddress = Range("J1:AAI1").Find(currentDate, LookIn:=xlValues).Address
'find the current date within the range of dates in row 1
该行应该做的是在第一行的所有日期中搜索“ 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 '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
会Address
以字符串形式(例如:“ $ A $ 1”)返回,因此您应声明dateAddress
为String
而不是Date
。
2)
由于声明为的变量String
(如Dim dateAddress as String
)不是对象,因此不应使用Set
它进行初始化。因此,
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 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
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句