Hello Dave, You are a rock Star.......thanks a ton!!!!!!!!!!!!!!!!!!!!!!!!!!!
The macro is awesome.this is what i wanted. Thanks and Regards Atul Kesaria On Wed, May 27, 2009 at 10:10 AM, Dave Bonallack <davebonall...@hotmail.com>wrote: > Hi Atul, > Have a look at the attached workbook, and see if it does what you want. > I've added a button to run the macro from the sheet, but you may want to run > it some other way. > I've left your formulas as you had them. > Regards - Dave. > > ------------------------------ > Date: Tue, 26 May 2009 18:33:57 +0530 > Subject: $$Excel-Macros$$ Re: $$Excel-Macros$$ > From: atul.kesa...@gmail.com > To: excel-macros@googlegroups.com > > > Hello Dave, > > Thanks for the below formulas. > > The reason I am asking for the macro is that we have more then 25 sheet in > a file and more then 200 line items in each sheet, macro would help to > reduce the file size and minimize the changes of error. > > I have attached a file in which i have tried to write a macro. Two things > that i am unable to add. 1 )The macro should able to apply the formula till > the last line > 2) It should copy and paste special value all the formula applied. > > Thanking you once again in advance. > > Thanks and Regards > Atul Kesaria > > > On Tue, May 26, 2009 at 2:47 PM, Dave Bonallack <davebonall...@hotmail.com > > wrote: > > Hi Atul, > Just as a by-the-way, those formulas are much longer than needed. There are > lots of redundant parts. The following would do the same thing: > > > =IF(AND(B2>0,B2<=1,0),1%,IF(b2<=2,3%,IF(b2<=3,5%,IF(b2<=4,7.5%,IF(b2<=5,8%,IF(b2<=6,10%,0)))))) > > =IF(AND(b2>0,b2<=1,0),11%,IF(b2<=2,13%,IF(b2<=3,15%,IF(b2<=4,17.5%,IF(b2<=5,18%,IF(b2<=6,20%,0)))))) > > =IF(AND(b2>0,b2<=1,0),21%,IF(b2<=2,23%,IF(b2<=3,25%,IF(b2<=4,27.5%,IF(b2<=5,28%,IF(b2<=6,20%,0)))))) > A multiple IF statement stops when it finds the first true statement, so > you don't need to define the lower level of each range. > Also, this sort of thing is done better using a table with VLOOKUP. You can > change the values more easily, and the formulas are much simpler. > As to a macro, if no one else answers, I could provide you with that. > Regards - Dave. > > > ------------------------------ > Check out the new Windows Live Messenger Looking for a fresh way to share > your photos? <http://windowslive.ninemsn.com.au/article.aspx?id=792335> > > > > > > > --~--~---------~--~----~------------~-------~--~----~ ------------------------------------------------------------------------------------- Some important links for excel users: 1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 2. Excel tutorials at http://www.excel-macros.blogspot.com 3. Learn VBA Macros at http://www.vbamacros.blogspot.com 4. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com or Ashish Jain @ 26may.1...@gmail.com ------------------------------------------------------------------------------------- -~----------~----~----~----~------~----~------~--~---
