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
firstcell.Offset(1).Select
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))
    
    Loop
Loop
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