Code Modify

[ayazgreat]

How should i modify mentioned below codes to take result c2 too c25 in sheet3 by matching a2 to a25 ?

Sub Commission_Total()

Dim ThisCell As Range
Dim CommTotal As Long

For Each ThisCell In Sheet2.Range("A1:A" & Range("A65536").End(xlUp).Row)
If ThisCell.Value = Sheet3.Range("A2") And ThisCell.Offset(, 1) = "sc" Then
CommTotal = CommTotal + ThisCell.Offset(, 2).Value
End If
Next ThisCell

Sheet3.Range("C2") = CommTotal

End Sub

 

[ayazgreat]

Sir

If you don't mind me asking it to be possible to be done by given code/macro and i know that this could be done by pivot table.

Lot of thanks

 

[Greg Truby]

Sir

If you don't mind me asking it to be possible to be done by given code/macro and i know that this could be done by pivot table.

Lot of thanks

Of course. You can also drive a thousand nails using just a brick. But if you have a pnuematic nail gun in your toolbox, why use the brick?

I apologize but I don't have a lot of time today to walk you through the coding. Can you elaborate on why you want to do this using VBA instead of a pivot? Perhaps we can address whatever concerns have you thinking VBA is a solution. (Or perhaps you will convince me that VBA is really the best way to go. )

 

[ayazgreat]

Sorry Sir I am not convincing you that VB is really the best way to go,

All I wanted to do is learn this methoed in said macro and nothing more but your way is best.

I am really sorry if you minded my asking in that way.

 

[Greg Truby]

No worries. I never have a problem with people wanting to learn. I only have problems if people want others to work harder at solving their problems than they do.

The difficulty that you will get into trying to use VBA to do this is that you want a unique listing. This means that you have to loop through the cells, building a collection of unique values. And then loop the collection to pull all the sub-totals. Feel free to research collections and so forth and try your had at VBA. You might learn a bit doing that.

But, in all honesty, you would probably do more good for yourself by trying to use VBA to create a PivotTable. Being able to do create pivots quickly is a *very* useful skill.

 

[ayazgreat]

Thank you very much Sir for your suggestion but I really appreciate you .

 

[xenou]

Hi, interesting post gentlemen.

A pivot table does seems called for here. I've been working on a VBA solution and already its taken me a half and hour - whereas my pivot table took me 20 seconds.

I'm not sure if I agree on the value of learning pivot table code - it seems to me awfully troublesome to do something that's so easy in native Excel and I don't use it much, though I've worked out the basics to generate reports. On the other hand, it may be quicker than what I've been trying!

It may be obvious but a SQL driven query on this data could also make short work of it - also just linking to Access and running a query in Access.

If VBA is an essential need, I'll tinker with my macro a bit more. have you made any progress yourself? Are you absolutely sure you need VBA to do this? If so, then maybe I can work out the rest - though it's mightily more work than what I've posted below.

Regards,
AB

book1.xls
	A	B	C	D	E	F
1	ID	PI	Qty		PI	sc
2	1001	sc	2
3	1025	sc	3		ID	Qty
4	1089	sc	6		1001	10
5	1236	sc	9		1025	3
6	1001	sc	8		1089	8
7	1025	jp	7		1236	9
8	1089	sc	2		1258	17
9	1236	jp	5		1892	2
10	1258	sc	11		GrandTotal	49
11	1892	sc	2
12	1258	sc	6
13	1892	jp	9
Sheet1

 

[ayazgreat]

If VBA is an essential need, I'll tinker with my macro a bit more. have you made any progress yourself? Are you absolutely sure you need VBA to do this? If so, then maybe I can work out the rest - though it's mightily more work than what I've posted below.

Regards,
AB

Yes Sir Alexander Barnes

If possible through VB please modify in given codes.

 

[Greg Truby]

I'm not sure if I agree on the value of learning pivot table code - it seems to me awfully troublesome to do something that's so easy in native Excel and I don't use it much, though I've worked out the basics to generate reports. On the other hand, it may be quicker than what I've been trying!

For the record, I was referring to learning how to create the pivots using code; not code the pivot's functionality. I pull in a couple dozen data sets every month from the mainframe. Then pop pivots (or append to existing pivots' data sources) and then use VBA to set various pivot parameters, take a snapshot and e-mail it to so-and-so; change another parameter, take another snapshot, shoot off another e-mail; doing in a couple of minutes what would take an hour or two by hand. Hope that clarifies my previous recommendation.

 

[xenou]

RE previous post,

yes, I think on reflection I should not rule out this kind of solution arbitrarily, and I may have exaggerated a little - the code to create a table is not so complicated, after all.

AB

 

[xenou]

AyazGreat,
Perhaps this will help. Here is a formula to sum up the values you need. The problem is that it we need to filter the list down so each sum appears only once for each ID.

200806214_ArraySortingAndSumming.xls
	A	B	C	D	E
1	ID	PI	Qty	Sum	Appearance
2	1001	sc	2	10	1
3	1025	sc	3	3	1
4	1089	sc	6	8	1
5	1236	sc	9	9	1
6	1001	sc	8	10	2
7	1025	jp	7	3	2
8	1089	sc	2	8	2
9	1236	jp	5	9	2
10	1258	sc	11	17	1
11	1892	sc	2	2	1
12	1258	sc	6	17	2
13	1892	jp	9	2	2
Sheet1

Formula in Cell D2 is:
=SUM(($A$2:$A$13=A2)*($B$2:$B$13="sc")*($C$2:$C$13))
Entered as an array formula with Control + Shift + Enter, not just Enter.

Formula in Cell E2 is:
=COUNTIF(OFFSET($A$2,0,0,ROW()-1,1),A2)

Now, with that formula you could do a few things...but I'm not sure which would be the best. Perhaps you could create the formula programmatically, get the values, copy or write to a new range as needed, then delete the formulas. Using the formula in Cell E2, for instance, you could delete all the 2s and higher and be left with only the unique list you need. I've been working on using arrays to do this processing but frankly it seems to be a lot of work and I'm not finished yet...probably proving Greg Truby right that I could have done this with a pivot table in code much faster It's been good practice for my array-handling though so I'm cool with it.

HTH

HTH