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

Question

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 .Find so that I'm using it correctly now.

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:

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").Value and then assigning Cells(currentRow, dateAddress).Value with it as above.

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

When in the debugging mode if I hover over currentRow, billAmount and dateAddress they 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 THEN statements in the last part of my code. But hopefully then it should be complete.

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

Answer 1

Prior this:

Cells(currentRow, dateAddress).Value = billAmount

Declare your dateAddress variable as an integer because it contains an integer and Cells(currentRow, dateAddresse) with dateAddress as String containing a number will throw Run-time Error 1004:

Dim dateAddress as Integer

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