Excel: converting worksheet function into a macro

'i'm having trouble with the following macro. I'm trying to get the following function which works converted in VBA. '=SUMPRODUCT(time!$I$1418:time!$I$39433,--(time!$E$1418:time!$E$39433=$B11),--(time!$G$1418:time!$G$39433=E$2)) 'this is because that function slows Excel down too much if it exists in too many cells. Here's what I got so far. I'm trying to loop ' from column c to the column where the second row = "total", then I go down the next row and I repeat the process until the cell in column B is blank. 'in the worksheet function below the i and j are not mistakes and they might be confused with column i and columns j but that is not meant. 'i also can't get the first cell select. The following syntax is not legal: 'firstcell.Offset(1).Select

Sub date_stats()
Dim first_cell As Range
Dim sheet As Worksheet
Set sheet = ActiveWorkbook.Sheets("time")
Dim i As Integer, j As Integer, num As Integer
firstcell = Range("c65000").End(xlUp).Row
Do Until ActiveSheet.Cells("b", j).Value = ""
j = j + 1
i = 0
    Do Until ActiveSheet.Cells(2, i).Value = "total"
    i = i + 1
    num =application.worksheetfunction.SUMPRODUCT(time.cells("i",2):time.cells("i",50000),--(time.cells("E",2):time.cells("E",50000)=activesheet.cells("B",j),--(time.cells("G",2):time.cells("G",50000)=activesheet.cells(i,2))
End Sub

This question generated 15 answers. To proceed to the answers, click here.

This thread is current as of September 27, 2014.

For more resources for Microsoft Excel