converting excel worksheet function into vba

Question

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.

I've got a run-time error 1004, application defined error or object-defined error and they are highlighting the sumproduct part.

Sub date_stats()

Dim first_cell As Integer
Dim time As Worksheet
Set time = ActiveWorkbook.Sheets("time")
Dim i As Integer, j As Integer, num As Integer

firstcell = Range("e65000").End(xlUp).Row + 1

Do Until ActiveSheet.Range("b" & firstcell).Value = ""
firstcell = firstcell + 1
i = 2
    Do Until ActiveSheet.Cells(2, i).Value = "total"
    i = i + 1

    num = Application.WorksheetFunction.SumProduct(time.Range("i2:i50000"), --(time.Range("E2:E50000") = ActiveSheet.Range("B" & first_cell)), --(time.Range("G2:G50000") = ActiveSheet.Cells(i, 2)))

    Loop
Loop

FIRST UPDATE

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

Try bracketing the calculations of your macro with Application.ScreenUpdating = False and Application.ScreenUpdating = True — Pieter Geerkens
– Pieter Geerkens, Commented Sep 28, 2014 at 2:28
It's going to be a matter of breaking it down into simpler constituent parts until you isolate the problem. Of course make sure the VBA compiles with Option Explicit at the top of the module. — Smandoli
– Smandoli, Commented Sep 28, 2014 at 3:37
SumProduct worksheet function is more limited in VBA as it doesn't support array arguments. You can get around this by creating your range based on criteria first, and then pass in the range to SumProduct — Automate This
– Automate This, Commented Sep 28, 2014 at 3:56

user147178 · Accepted Answer · 2014-09-28 23:47:48Z

1

Problem solved. I changed the name of the sheet time to time2 because time was already used by Excel as a special function. But when I used the evaluation feature that was no longer necessary.

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

So the problem was due to a typo.

answered Sep 28, 2014 at 23:47

user147178

4712 gold badges8 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

converting excel worksheet function into vba

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related