macro to mimic sumproduct

  • Thread starter Thread starter icestationzbra
  • Start date Start date
I

icestationzbra

hello good samaritan,

i have two sheets in my workbook. i would like to mimic sumproduc
and/or sumif by using a macro. this would help me to create a shee
(sheet2) thru macro, show the output and delete it afterwards. i hav
written a piece which is giving me erratic results, as my algorithm ma
not be right. help will be appreciated.

**************************************************
*
Sheet1
Item Status Defects
A Rejected 0
A Accepted 5
B Accepted 0
C Accepted 0
D Rejected 5
D Accepted 0

*****
Sheet2
Item Accepted Count Rejected Count
A
B
C
D
**************************************************
*
CODE:

Option Explicit

Public intAcc As Integer
Public intRows1 As Integer
Public intRej As Integer
Public intRows2 As Integer

Dim m As Integer
Dim n As Integer

Sub calcStatus()

m = 2
n = 2
intRows2 = 0
intRows1 = 0
intAcc = 0
intRej = 0

Sheet1.Select
Range("A1").Activate
ActiveCell.CurrentRegion.Select
intRows1 = Selection.Rows.Count - 1

Sheet2.Select
Range("A1").Activate
ActiveCell.CurrentRegion.Select
intRows2 = Selection.Rows.Count - 1

For m = 2 To intRows1

For n = 2 To intRows2

If (Sheet1.Range("A" + Trim(Str(m))) = Sheet2.Range("A"
Trim(Str(n)))) Then

If (Sheet1.Range("B" + Trim(Str(m))) = "Accepted") Then

intAcc = intAcc + 1

Else

If (Sheet1.Range("B" + Trim(Str(m))) = "Rejected") Then

intRej = intRej + 1

End If

End If

End If

Sheet2.Range("B" & n).Value = intAcc

Sheet2.Range("C" & n).Value = intRej

Next n

Next m

End Sub

**************************************************
*

thanks,

mac
 
icestationzbra said:
hello good samaritan,

i have two sheets in my workbook. i would like to mimic sumproduct
and/or sumif by using a macro. this would help me to create a sheet
(sheet2) thru macro, show the output and delete it afterwards. i have
written a piece which is giving me erratic results, as my algorithm may
not be right. help will be appreciated.
Click to expand...

I can offer 2 suggestions:

1) Use the formula (or formular1c1) property of the range object to
enter the =SUMPRODUCT and/or =SUMIF formulas in the respective cells.
2) use the Application.WorksheetFunction property to execute the
functions in the macro itself
 
Instead of mimicking the sumproduct function, why not just put the sumproduct
formulas in the cells? If you want values, then convert them to values before
the macro ends.

(or take a look at the data|pivottable stuff)
 
hi dave,

thanks for the suggestion.

i have used pivottables extensively in the workbook. there are certai
places and situations in there which are not conducive to pivottabl
being used.

one reason, i do not want to use pivottable is that - i would like t
be able to create a sheet, dump data onto it, provide that formula, an
delete it at the end of the operation - all using a macro. i have th
rest of the code ready, i am just not getting the results right throug
my algorithm.

i have another grouse. if i use formulas on the sheets, i have to dra
the formula down to several rows in order to accommodate for furthe
addition in data along the rows. this however, increases the filesize
hence i came up with the idea of creating a temporary sheet, dump data
manipulating it with a formula and deleting it at the end of th
operation.

mac
 
Back
Top