Hi Atul, Sorry about that.
Try the modified attached. Regards - Dave. Date: Wed, 27 May 2009 14:08:38 +0530 Subject: $$Excel-Macros$$ Re: $$Excel-Macros$$ From: atul.kesa...@gmail.com To: excel-macros@googlegroups.com Hello Dave, One small issue in the macro, the cell number " b2" in the formula remain the same across all the row when we run the macro, however it should changes as the row changes. Please help. Thanks and Regards Atul Kesaria On Wed, May 27, 2009 at 11:11 AM, Atul Kesaria <atul.kesa...@gmail.com> wrote: 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? _________________________________________________________________ Want to stay on top of your life online? Find out how with Windows Live! http://windowslive.ninemsn.com.au/ --~--~---------~--~----~------------~-------~--~----~ ------------------------------------------------------------------------------------- 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 ------------------------------------------------------------------------------------- -~----------~----~----~----~------~----~------~--~---
SAMPLE(1).xls
Description: MS-Excel spreadsheet
