kylefoley76
Well-known Member
- Joined
- Mar 1, 2010
- Messages
- 1,553
'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
'=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
Code:
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