VBA Excel - Problems with a simple macro to auto-f

Question

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.

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 but now I'm up against some issues outside of my grasp. And I've had no luck searching the internet.

The first problem that the debug finds is on this line:

currentDate = Cells(G, currentRow).Value 'Set Start Date

I'm getting a Run-Time Error '1004': Application-defined or Object-defined error. I'm sure that it's just a simple syntax problem or something. What that line is supposed to be doing (in the first instance) is looking at the Start Date in Cell "G3" which is formated as a date (04-Feb-14) and assigning the variable currentDate to that date so that I can then do a search across all the dates in the first row for that 'currentDate'.

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
   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(G, currentRow).Value 'Set Start Date
   repeatuntilDate = Cells("H,currentRow").Value 'Set End Date
       While currentDate <= repeatuntilDate 'Loop from Start Date until End Date
       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("E,currentRow").Value = "Weekly" Then
           currentDate = DateAdd("ww", 1, currentDate)
       ElseIf Cells("E,currentRow").Value = "Fortnightly" Then
           currentDate = DateAdd("ww", 2, currentDate)
       ElseIf Cells("E,currentRow").Value = "Monthly" Then
           currentDate = DateAdd("m", 1, currentDate)
       ElseIf Cells("E,currentRow").Value = "Quarterly" Then
           currentDate = DateAdd("q", 1, currentDatee)
       ElseIf Cells("E,currentRow").Value = "6 Monthly" Then
           currentDate = DateAdd("m", 6, currentDate)
       ElseIf Cells("E,currentRow").Value = "Annually" Then
           currentDate = DateAdd("y", 1, currentDate)
       ' ElseIf Cells("E,currentRow").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

to answer why you get Object Defined Error you missed the Cells property syntax which is:

Cells(row_index, col_index) which accepts long data type (numbers).

So it should be:

currentDate = Cells(currentRow, 7).Value 'G is the 7th column

But similarly you can refer to the col_index argument like this:

currentDate = Cells(currentRow, "G").Value 'take note of the qoutation marks ""