converting worksheet function into a macro

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

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
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Kyle, a few things just looking quickly....

1)
The following syntax is not legal:'firstcell.Offset(1).Select
You are using
Code:
firstcell = Range("c65000").End(xlUp).Row
the .Row makes it a number not a Range so remove the .Row at the end then your offset in the next row should be ok.

2) You are using Cells incorrectly. The syntax is Cells(Row.number, Column number/letter) in
Code:
Do Until ActiveSheet.Cells("b", j).Value = ""
and various places in your function you have the columns and rows the wrong way round.

3) In your function you are using time as the sheet name i.e.
Code:
 [COLOR="#FF0000"]time[/COLOR].cells("i",2):[COLOR="#FF0000"]time[/COLOR].cells("i",50000)
but you don't define time anywhere. I assume it was supposed to be sheet as in
Code:
Set [COLOR="#FF0000"]sheet[/COLOR] = ActiveWorkbook.Sheets("time")
but even then you really shouldn't use names for variables that VBA can confuse with its functions (use something like Sht).

Get the points above sorted out and post the code back then I am sure someone will look at any other issues you have as I haven't looked to see if your loop is working correctly
 
Upvote 0
ok, I've got things in better order now, but I've got a run-time error 1004, application defined error or object-defined error and they are highlighting the sumproduct part.

Code:
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


End Sub
 
Upvote 0
First of all Time is a reserved word (see what I mean by using variables that can confuse Excel.

then try replacing
Code:
  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)))

with

Code:
x = ActiveSheet.Range("B" & first_cell).Address
y = ActiveSheet.Cells(i, 2).Address

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

Dim both x and y as String
 
Upvote 0
Don't see how you can if you aren't getting an error on
Code:
Do Until ActiveSheet.Range("b" & firstcell).Value = ""
as it uses the same range so post your code as you currently have it (and state what the runtime error actually says)
 
Upvote 0
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


x = ActiveSheet.Range("B" & first_cell).Address
y = ActiveSheet.Cells(i, 2).Address


num = Format(num, Fixed)


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




Application.ScreenUpdating = False


With ActiveSheet


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


End With


Application.ScreenUpdating = True




End Sub

The run time error is 1004 and it highlights "x = etc etc"
and says application defined or object defined error.
 
Upvote 0
You have put the line before you have allocated anything to firstcell so obviously you will get an error and then you will get an error on y as the you have put the line before you have assigned anything to i.

Put them in the correct places (which is in the loops)
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,034
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top