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