converting worksheet function into a macro

[kylefoley76]

'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

 

[Andrew Poulsom]

Maybe your problem is that you have two variables, firstcell and first_cell.

 

[MARK858]

Put the lines in the loop (which they still aren't) as I stated and the line using i after where you add a value to i which isn't until at least where it states i = 2.

Edit: and just saw Andrew's post which was something I missed

 

[MARK858]

2nd Edit: and in your formula you are using sheet time2 when your sheet is called time (it is not a variable in the evaluate it is the actual sheetname as a string. See how I had it) and then not actually using num anywhere once you get the formula correct.

Having a break from the thread for a while.

 

[kylefoley76]

Ok, look at this when I wake up. i'm going to bed now.

 

[snb_]

To me it's unclear

- are you checking for B11 or for B11, B12, B13 etc.
- where you want the result to be shown in the worksheet.

this macro calculates the sumproduct for B11, B12, B13, etc.
And writes the result into column K

Sub M_snb()
   c00 = Range("E2")
   sq = Range(Cells(11, 2), Cells(Rows.Count, 2).End(xlUp))
   
   sn = Range(Cells(1418, 5), Cells(Rows.Count, 5).End(xlUp)).Resize(, 5)
   ReDim sp(1 To UBound(sn), 1 To 1)
   
   For j = 1 To UBound(sn)
       sp(j, 1) = sp(j,1) + sn(j, 5) * Abs(sn(j, 1) = sq(j, 1)) * Abs(sn(j, 3) = c00)
   Next
  
   cells(1418,11).resize(sp)=sp
End Sub

if you are checking only for B11 it's simply:

Sub M_snb()
   sq = array(cells(11,2).value,cells(2,5).value)
   sn = Range(Cells(1418, 5), Cells(Rows.Count, 5).End(xlUp)).Resize(, 5)
   ReDim sp(1 To UBound(sn), 1 To 1)
   
   For j = 1 To UBound(sn)
       sp(j, 1) = sp(j,1) + sn(j, 5) * Abs(sn(j, 1) = sq(0)) * Abs(sn(j, 3) = sq(1))
   Next
  
   cells(1418,11).resize(sp)=sp
End Sub

 

[kylefoley76]

I'm making progress. I forgot that I was using a different module and consequently did not have the options explicit put on the new module and that I was misspelling firstcell and first_cell. I've got all the bugs removed on the VBA but now the result that is coming up is #REF which means that it is referencing deleted cells which is clearly not the case. Here's the new updated workbook.

https://drive.google.com/file/d/0B9zzW6-3m2qGeFNCdEhtem44Wnc/edit?usp=sharing

And here is the code:

Sub date_stats()


Dim first_cell As Integer
Dim time2 As Worksheet
Set time2 = ActiveWorkbook.Sheets("time")
Dim i As Integer, j As Integer, num As Integer
Dim x As String
Dim y As String
Dim z As Range




'num = Format(num, "standard")


Application.ScreenUpdating = False


With ActiveSheet
first_cell = .Range("e65000").End(xlUp).Row


'=SUMPRODUCT(time!$I$1418:time!$I$39433,--(time!$E$1418:time!$E$39433=$B11),--(time!$G$1418:time!$G$39433=E$2))


Do Until .Range("b" & first_cell).Value = ""
first_cell = first_cell + 1
i = 3
    Do Until .Cells(2, i).Value = "total"
   
    x = .Range("B" & first_cell).Address
    y = .Cells(2, i).Address
    Set z = .Cells(first_cell, i)
    z.Value = Evaluate("=SUMPRODUCT(time2!$I$2:$I$50000,--(time2!$E$2:time2!$E$50000=" & x & "),--(time2!$G$2:time2!$G$50000=" & y & "))")
     i = i + 1
    Loop
Loop


End With


Application.ScreenUpdating = True




End Sub

 

[MARK858]

I'll say it again your sheet is called time and that is what is needed in the evaluate not a variant. I'm not sure what you are doing with the loop and so I will leave that to you / someone else to sort out.

z.Value = Evaluate("=SUMPRODUCT(Time!$I$2:$I$50000,--(Time!$E$2:$E$50000=" & x & "),--(Time!$G$2:$G$50000=" & y & "))")

 

[kylefoley76]

thanks that did it. Thanks for sticking with through the thick and thin.