'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.