Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 02-18-2014, 02:28 AM
PRADEEPB270's Avatar
PRADEEPB270 PRADEEPB270 is offline Required VBA Codes to automate sum-up the 2 different groups Windows Vista Required VBA Codes to automate sum-up the 2 different groups Office 2010 32bit
Novice
Required VBA Codes to automate sum-up the 2 different groups
 
Join Date: Aug 2011
Location: Gurgaon-INDIA
Posts: 17
PRADEEPB270 is on a distinguished road
Default Required VBA Codes to automate sum-up the 2 different groups

I have a file with 2 types of "Material".One is having end with "S" and rest other than ending with "S" .I want to sum all of the Materials ( Col.B ) according to Material group ( Col.D ) in Column J and K,where define material category.
Can it be possible thru VBA codes only?

Pl.refer attach file for more clarification.
Attached Files
File Type: xlsx slm.xlsx (62.1 KB, 10 views)
Reply With Quote
  #2  
Old 02-18-2014, 04:49 AM
macropod's Avatar
macropod macropod is online now Required VBA Codes to automate sum-up the 2 different groups Windows 7 32bit Required VBA Codes to automate sum-up the 2 different groups Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

You don't need VBA for that; you can do it with formulae:
Code:
In J4 =SUMPRODUCT((D:D=H4)*(RIGHT(B:B,1)<>"S"),F:F)
In K4 =SUMPRODUCT((D:D=H4)*(RIGHT(B:B,1)="S"),F:F)
Copy both formulae down to row 7
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #3  
Old 02-18-2014, 08:51 AM
PRADEEPB270's Avatar
PRADEEPB270 PRADEEPB270 is offline Required VBA Codes to automate sum-up the 2 different groups Windows Vista Required VBA Codes to automate sum-up the 2 different groups Office 2010 32bit
Novice
Required VBA Codes to automate sum-up the 2 different groups
 
Join Date: Aug 2011
Location: Gurgaon-INDIA
Posts: 17
PRADEEPB270 is on a distinguished road
Default

Thanks for the nice solution.Glad to find it.
Reply With Quote
  #4  
Old 02-18-2014, 02:44 PM
macropod's Avatar
macropod macropod is online now Required VBA Codes to automate sum-up the 2 different groups Windows 7 32bit Required VBA Codes to automate sum-up the 2 different groups Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Cross-posted at: http://www.eileenslounge.com/viewtopic.php?f=27&t=15577
For cross-posting etiquette, please read: http://www.excelguru.ca/content.php?184
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #5  
Old 02-18-2014, 09:03 PM
PRADEEPB270's Avatar
PRADEEPB270 PRADEEPB270 is offline Required VBA Codes to automate sum-up the 2 different groups Windows Vista Required VBA Codes to automate sum-up the 2 different groups Office 2010 32bit
Novice
Required VBA Codes to automate sum-up the 2 different groups
 
Join Date: Aug 2011
Location: Gurgaon-INDIA
Posts: 17
PRADEEPB270 is on a distinguished road
Default

Hi macropod,

Both question are of different nature.At eileen forum,I have asked an excel formula for my thread but at msofficeforums,I want to ask a VBA code solution and not to provide as desired . Can we link that both excel formula and VBA codes are same to each other?
Reply With Quote
  #6  
Old 02-18-2014, 09:16 PM
macropod's Avatar
macropod macropod is online now Required VBA Codes to automate sum-up the 2 different groups Windows 7 32bit Required VBA Codes to automate sum-up the 2 different groups Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

The basic issue, though, is the same - how to populate the output range with the data.

A VBA solution might consist of nothing more than output the formulae I posted to the relevant cells. Hardly a great advance on what you already have and the end result would be exactly the same.

So, unless you can explain why a formulaic approach, which will automatically take account of any changes to the data, isn't suitable, whereas a VBA one wouldn't necessarily be so flexible, I don't propose to spend any more time on this.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Custom Tabs and Groups KHenry Word 0 10-08-2013 09:37 AM
Bar Graph in Groups Alaska1 Excel 3 12-17-2012 05:20 PM
Required VBA Codes to automate sum-up the 2 different groups Contacts List groups WildBill Outlook 1 09-21-2011 09:09 PM
Required VBA Codes to automate sum-up the 2 different groups Arranging Into Groups skoz55 Excel 1 09-07-2011 12:02 AM
Some contacts are listed in groups I don't want ironwoods Outlook 0 07-29-2010 04:40 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 06:03 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft