Code Modify

ayazgreat

Well-known Member
Joined
Jan 19, 2008
Messages
1,151
How should i modify mentioned below codes to take result c2 too c25 in sheet3 by matching a2 to a25 ?

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

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

ayazgreat

Well-known Member
Joined
Jan 19, 2008
Messages
1,151

ADVERTISEMENT

Is it not possible for anybody to tell about it?
 

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,014
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

Well-known Member
Joined
Jan 19, 2008
Messages
1,151

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
ABC
1IDPIQty
21001sc2
31025sc3
41089sc6
51236sc9
61001sc8
71025jp7
81089sc2
91236jp5
101258sc11
111892sc2
121258sc6
131892jp9
Sheet1


Excel Workbook
ABC
1IDRemarksSum
21001ok10
31025ok
41089ok
51236ok
61001ok
71025ok
81089ok
91236ok
101258ok
111892ok
121258ok
131892ok
Sheet2


Yes Sir I am in Karachi
 

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,014
Do you really want row 6 in sheet 2 to repeat "1001"? Or would you prefer a unique listing of ID #'s?
 

ayazgreat

Well-known Member
Joined
Jan 19, 2008
Messages
1,151
Sir

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

Watch MrExcel Video

Forum statistics

Threads
1,122,867
Messages
5,598,542
Members
414,245
Latest member
allyciv

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
Top