Using variables within a predefined function

[RhodeRider]

There is probably a more elegant way to do this, but what I am trying to do is take counts of bicycles in 15-minute intervals from one sheet and sum them into hourly totals and place them onto another sheet. Within the while-loop, the first two lines take the date and the hour (for instance, hour is listed as 0:00, 0:15, 0:30 etc. so I take every four so that my final column reads 0:00, 1:00 etc.). They work properly. The third line returns a #name error. When I click on the cells in the third column of the "hour" sheet, I see this: =SUM('2010 15min'!Cells(8,18):Cells(11,18)). So it seems to be reading c and b properly. Any ideas on the #name error?

Thanks in advance. Let me know if my description isn't clear enough.

Sub Aggregate()

'variables
Dim c As Integer
Dim v As Integer
Dim b As Integer

v = 2
c = 8
b = 11

While IsEmpty(Worksheets("2010 15min").Cells(c, 18)) = False

   Worksheets("Hour").Cells(v, 1) = Worksheets("2010 15min").Cells((c), 1)
   Worksheets("Hour").Cells(v, 2) = Worksheets("2010 15min").Cells((c), 2)
   [COLOR=Red]Worksheets("Hour").Cells(v, 3).Formula = "=SUM('2010 15min'!Cells(" & c & ",18):Cells(" & b & ",18))"[/COLOR]

   c = c + 4
   b = b + 4
   v = v + 1
Wend

End Sub

 

[GTO]

"I see this: =SUM('2010 15min'!Cells(8,18):Cells(11,18)). "

Cells() is vba and won't be recognized in a worksheet function/formula. Try stringing together the formula string using the range's .Address

 

[VoG]

Try

Worksheets("Hour").Cells(v, 3).Formula = "=SUM('2010 15min'!" & Range(Cells(c, 18), Cells(b, 18)).Address & ")"

 

[RhodeRider]

Ah GTO that makes sense. ANd VoG, thanks, your code worked perfectly. I have a tough time with syntax in VBA. Do you guys happen to know of a good resource? I usually just google my problem and read forums until I've found somethin.

 

[VoG]

Do you guys happen to know of a good resource? I usually just google my problem and read forums until I've found somethin.

Take a look at Hiker's list http://www.mrexcel.com/forum/showpost.php?p=2676997&postcount=5

 

[RhodeRider]

OH Awesome, Thanks a lot