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]

Can anyone please help ?

 

[ayazgreat]

Can anybody please help

 

[ayazgreat]

Please help if possible

 

[ayazgreat]

ADVERTISEMENT

Is it not possible for anybody to tell about it?

 

[Greg Truby]

I'm afraid I don't have the time to try to use the few hints you've given about your data layout to see if I can construct what your worksheet must look like.

But if looks to me like you do not even need VBA. My quick read on this is that you ought to be able to plug a couple of dynamic ranges into a SUMPRODUCT formula and get what you need.

Below is my "boilerplate" on DNR's. Search here for more.

Also search here for threads by Aladin Akyurek, Barry Houdini or Domenic using SUMPRODUCT and you should start to see what's possible.


<HR>

Dynamic Named Ranges
Dynamic named ranges can be used just like normal named ranges in formulas, or as sources for things like pivot tables and charts. However instead of a normal named range where the group of cells being referred to is static, dynamic ranges typically take advantage of the Offset() and CountA() [or Count()] functions to create a range that resizes to include new data appended to the bottom of the range.


To Create a Dynamic Range

1. From the menu Insert | Name | Define...
2. In the Names in Workbook textbox, type in the name for the Named Range - probably something like "Data" or "DataRange".
3. In the Refers to textbox type in a formula like the following (note: you may find it helpful to hit the F2 key while in the textbox to put it into "edit mode"):
   • =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),9) to start at A1, include all non-blank rows and 9 columns. (Good example for pivot tables where you want to include headers.)
   • =OFFSET(Sheet1!$B$2,0,0,COUNT(Sheet1!$B:$B),1) to start at B2, include all numeric rows and 1 column. (Good example for a chart data series.)
   • =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1,1) to start at A2, include one less than the count of all non-blank rows and 1 column. (Good example for a chart category series where values are not numeric.)
4. You're ready to reference the Named Dynamic Range like you would a normal Named Range.

Karachi, seriously? Or you're in Peoria and Karachi just sounded more interesting?

 

[ayazgreat]

ADVERTISEMENT

Thanks Sir for your reply

Please see mentioned below detail and this macro gives me sum only of ID 1001 in Sheet2 in range C2 but my question is that how to modify this macro to take sum all ID in Sheet2 crateria based on sheet1.Please help if possible.

Excel Workbook
	A	B	C
1	ID	PI	Qty
2	1001	sc	2
3	1025	sc	3
4	1089	sc	6
5	1236	sc	9
6	1001	sc	8
7	1025	jp	7
8	1089	sc	2
9	1236	jp	5
10	1258	sc	11
11	1892	sc	2
12	1258	sc	6
13	1892	jp	9
Sheet1

Excel Workbook
	A	B	C
1	ID	Remarks	Sum
2	1001	ok	10
3	1025	ok
4	1089	ok
5	1236	ok
6	1001	ok
7	1025	ok
8	1089	ok
9	1236	ok
10	1258	ok
11	1892	ok
12	1258	ok
13	1892	ok
Sheet2

Yes Sir I am in Karachi

 

[Greg Truby]

Do you really want row 6 in sheet 2 to repeat "1001"? Or would you prefer a unique listing of ID #'s?

 

[ayazgreat]

Sir

I want unique IDs Sum in sheet2 from from sheet1 not only ID 1001 repeated sum.

 

[Greg Truby]

Then I would use a pivot table and wham-bam, you're done.

Deb & Jon each have good stuff on their sites about pivots.

Deb's = www.contextures.com
Jon's = www.peltiertech.com