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
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
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?
 
Upvote 0
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
 
Upvote 0
Do you really want row 6 in sheet 2 to repeat "1001"? Or would you prefer a unique listing of ID #'s?
 
Upvote 0

Forum statistics

Threads
1,213,556
Messages
6,114,284
Members
448,562
Latest member
Flashbond

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