$$Excel-Macros$$ Help on Macro-Atul
Hello Guys, Need a help on writing macro. I am preparing a sheet which has three coloumn. The first coloumn would have number ranging from 1 to 3. The Second coloumn would have certain number varying from 0 to 6( inclusive of demical to 2 digit).What I need in third coloumn is a formula which is based on value in coloumn first. For eg. If coloumn first has 1 then formula would be =IF(AND(B20,B2=1,0),1%,IF(AND(ROUND(b2=1.1,0),ROUND(b2=2,0)),3%,IF(AND(ROUND(b2=2.1,0),ROUND(b2=3,0)),5%,IF(AND(ROUND(b2=3.1,0),ROUND(b2=4,0)),7.5%,IF(AND(ROUND(b2=4.1,0),ROUND(b2=5,0)),8%,IF(AND(ROUND(b2=5.1,0),ROUND(b2=6,0)),10%,0)) If coloumn first has 2 then formula would be =IF(AND(b20,b2=1,0),11%,IF(AND(ROUND(b2=1.1,0),ROUND(b2=2,0)),13%,IF(AND(ROUND(b2=2.1,0),ROUND(b2=3,0)),15%,IF(AND(ROUND(b2=3.1,0),ROUND(b2=4,0)),17.5%,IF(AND(ROUND(b2=4.1,0),ROUND(b2=5,0)),18%,IF(AND(ROUND(b2=5.1,0),ROUND(b2=6,0)),20%,0)) If coloumn first has 3 then formula would be =IF(AND(b20,b2=1,0),21%,IF(AND(ROUND(b2=1.1,0),ROUND(b2=2,0)),23%,IF(AND(ROUND(b2=2.1,0),ROUND(b2=3,0)),25%,IF(AND(ROUND(b2=3.1,0),ROUND(b2=4,0)),27.5%,IF(AND(ROUND(b2=4.1,0),ROUND(b2=5,0)),28%,IF(AND(ROUND(b2=5.1,0),ROUND(b2=6,0)),20%,0)) The number of rows are not fixed and hence the macro should be able to run till the last record in that coloumn. It would be great if some one can provide me with a macro for the same. Thanks and Regards Atul Kesaria --~--~-~--~~~---~--~~ - 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 - -~--~~~~--~~--~--~---
$$Excel-Macros$$ Help on Macro-Atul
Hello Guys, Need a help on writing macro. I am preparing a sheet which has three coloumn. The first coloumn would have number ranging from 1 to 3. The Second coloumn would have certain number varying from 0 to 6( inclusive of demical to 2 digit).What I need in third coloumn is a formula which is based on value in coloumn first. For eg. If coloumn first has 1 then formula would be =IF(AND(B20,B2=1,0),1%,IF(AND(ROUND(b2=1.1,0),ROUND(b2=2,0)),3%,IF(AND(ROUND(b2=2.1,0),ROUND(b2=3,0)),5%,IF(AND(ROUND(b2=3.1,0),ROUND(b2=4,0)),7.5%,IF(AND(ROUND(b2=4.1,0),ROUND(b2=5,0)),8%,IF(AND(ROUND(b2=5.1,0),ROUND(b2=6,0)),10%,0)) If coloumn first has 2 then formula would be =IF(AND(b20,b2=1,0),11%,IF(AND(ROUND(b2=1.1,0),ROUND(b2=2,0)),13%,IF(AND(ROUND(b2=2.1,0),ROUND(b2=3,0)),15%,IF(AND(ROUND(b2=3.1,0),ROUND(b2=4,0)),17.5%,IF(AND(ROUND(b2=4.1,0),ROUND(b2=5,0)),18%,IF(AND(ROUND(b2=5.1,0),ROUND(b2=6,0)),20%,0)) If coloumn first has 3 then formula would be =IF(AND(b20,b2=1,0),21%,IF(AND(ROUND(b2=1.1,0),ROUND(b2=2,0)),23%,IF(AND(ROUND(b2=2.1,0),ROUND(b2=3,0)),25%,IF(AND(ROUND(b2=3.1,0),ROUND(b2=4,0)),27.5%,IF(AND(ROUND(b2=4.1,0),ROUND(b2=5,0)),28%,IF(AND(ROUND(b2=5.1,0),ROUND(b2=6,0)),20%,0)) The number of rows are not fixed and hence the macro should be able to run till the last record in that coloumn. It would be great if some one can provide me with a macro for the same. Thanks and Regards Atul Kesaria --~--~-~--~~~---~--~~ - 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 - -~--~~~~--~~--~--~---