VBA Excel - 尝试为单元格分配值时出现运行时错误“1004”

VBA Excel - Run-time Error '1004' when trying to assign a value to a Cell

提问by Bzmek

For the background, see my previous question. ((I have posted the same code earlier but with different problems. However, this is a new problem that I have encountered.)

有关背景，请参阅我之前的问题。（（我之前发布了相同的代码，但有不同的问题。但是，这是我遇到的一个新问题。）

I've gotten rid of a few bugs where I was using a variable.Value, had messed up the syntax for Cells(row, column) and I've just fixed up the .Findso that I'm using it correctly now.

我已经摆脱了使用变量的一些错误。值，弄乱了单元格（行，列）的语法，我刚刚修复了它，.Find以便我现在可以正确使用它。

I had thought that I was just about there in having this code complete however now I receive a Run-time error '1004': Application-defined or Object-defined error message on this line:

我原以为我已经完成了这段代码，但是现在我在这一行收到一条运行时错误“1004”：应用程序定义或对象定义的错误消息：

Cells(currentRow, dateAddress).Value = billAmount 'Cells(currentRow, "D").Value 
'Populate cell with amount

I have tried using Set. I have tried creating a variable billAmount, assigning it the value from Cells(currentRow, "D").Valueand then assigning Cells(currentRow, dateAddress).Valuewith it as above.

我试过使用Set. 我曾尝试创建一个变量billAmount，为其分配值Cells(currentRow, "D").Value，然后Cells(currentRow, dateAddress).Value按上述方式分配。

I have tried just having the one cell equal the other cell Cells(currentRow, dateAddress).Value = Cells(currentRow, "D").Valuebut that doesn't work either.

我曾尝试让一个单元格与另一个单元格相等，Cells(currentRow, dateAddress).Value = Cells(currentRow, "D").Value但这也不起作用。

When in the debugging mode if I hover over currentRow, billAmountand dateAddressthey all have the expected values in them. However I just can't seem to get the final cell to populate. Once I've got this solved, I'm sure I'll have to apply it to all the IF THENstatements in the last part of my code. But hopefully then it should be complete.

在调试模式下，如果我将鼠标悬停在 上currentRow，billAmount并且dateAddress它们都具有预期值。但是，我似乎无法填充最后一个单元格。一旦我解决了这个问题，我确定我必须将它应用于IF THEN我代码最后一部分中的所有语句。但希望它应该是完整的。

Here is 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 String
   Dim dateRange As Range
   Dim lastDate As Range
   Dim billAmount As Integer

   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
    billAmount = Cells(currentRow, "D").Value
    While currentDate <= repeatuntilDate 'Loop from Start Date until End Date
        With Range("J1:AAI1")
            Set lastDate = .Cells(.Cells.Count) 'Setup for the upcoming Find to begin at the lastDate
        End With
        Set dateRange = Range("J1:AAI1").Find(What:=currentDate, After:=lastDate)
        dateAddress = dateRange.Column 'Obtain column of the found Date
        Cells(currentRow, dateAddress).Value = billAmount 'Cells(currentRow, "D").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

Prior this:

在此之前：

Cells(currentRow, dateAddress).Value = billAmount

Declare your dateAddressvariable as an integer because it contains an integer and Cells(currentRow, dateAddresse)with dateAddressas Stringcontaining a number will throw Run-time Error 1004:

将您的dateAddress变量声明为整数，因为它包含一个整数，Cells(currentRow, dateAddresse)而dateAddressasString包含一个数字将抛出Run-time Error 1004：

Dim dateAddress as Integer

Last thing, I suggest you change dateAddressto dateColor something so it is clearer for others or even you if you recheck your code a while after you wrote it.

最后一件事，我建议您更改dateAddress为dateCol或其他内容，以便其他人甚至您在编写代码后重新检查代码时更清楚。