Re: $$Excel-Macros$$ How to make dynamic formula for value change on fix date every month
On Sat, 3/4/17, Sunil Kumar Yadav wrote: Subject: Re: $$Excel-Macros$$ How to make dynamic formula for value change on fix date every month To: excel-macros@googlegroups.com Date: Saturday, March 4, 2017, 7:10 AM Dear Paul, Thank you so much for your support but I don't have need formula calculation start from today. It should be start from my provided date...pls recheck that time my file i am sure you will be got my query. On Mar 4, 2017 1:58 AM, "Paul Schreiner" wrote: First of all, your IF() statement:A nested if statement will evaluate each section until it finds a true() option.So, in the first case:IF(TODAY()<=42750evaluates false, that means today is > 42750So the second part: IF(AND(TODAY()>=42751 HAS to be true, so there's no reason to test it!Your IF statement could be simplified to:IF(TODAY()<=42750,0, IF(TODAY()<=42781,1.5, IF(TODAY()<=42809,3, IF(TODAY()<=42840,4.5, IF(TODAY()<=42870,6, IF(TODAY()<=42901,7.5, IF(TODAY()<=42931,9, IF(TODAY()<=42962,10.5, IF(TODAY()<=42993,12, IF(TODAY()<=43023,13.5, IF(TODAY()<=43054,15, IF(TODAY()<=43084,16.5, IF(TODAY()>=43085,18,0 ) But, you can simplify it even more.You're using the 15th of the month as the "break point".So, if you were to subtract 15 days, you'd be in the "same" month if the day is > 15, and the PREVIOUS month if it is before the 15th.Basically, by subtracting 15, you're setting the "break point" as the first of the month.So, if you determine the month number: (MONTH(A5-15)) you could say you want 1.5 for each month, or:MONTH(A6-15) * 15Now, the issue is with the first half of January.subtracting 15 makes it December of the PREVIOUS year, and therefore should be 0 instead of 18. so, you need to check to see if the offset year is the same as the current year:=IF(YEAR(A5-15) <> YEAR(TODAY()),0, MONTH(A5-15)* 1.5) This seems match the same as your sample values for rows 5-9,but I'm not sure what you were doing with rows 12-16Why does 16-Mar in row 8 result in 4.5, but 16-Mar in row 13 is supposed to be 1.5 ???I'm not sure what the rules you're using here. as for your second query: You're basically wanting to round off your number to the nearest 1/2.You can accomplish this by first doubling the number and removing the decimal portion(extract just the integer portion)then divide it by 2:=INT(A23*2)/2 this works for your samples. hope this helps. Paul-- --- “Do all the good you can, By all the means you can, In all the ways you can, In all the places you can, At all the times you can, To all the people you can, As long as ever you can.” - John Wesley -- --- On Friday, March 3, 2017 12:08 PM, Sunil Kumar Yadav wrote: Dear All, I have two query for automatically update value on one fixed date, have created formula but need dynamic formula. Formula:IF(TODAY()<=42750,0,IF(AND( TODAY()>=42751,TODAY()<=42781) ,1.5,IF(AND(TODAY()>=42782, TODAY()<=42809),3,IF(AND( TODAY()>=42810,TODAY()<=42840) ,4.5,IF(AND(TODAY()>=42841, TODAY()<=42870),6,IF(AND( TODAY()>=42871,TODAY()<=42901) ,7.5,IF(AND(TODAY()>=42902, TODAY()<=42931),9,IF(AND( TODAY()>=42932,TODAY()<=42962) ,10.5,IF(AND(TODAY()>=42963, TODAY()<=42993),12,IF(AND( TODAY()>=42994,TODAY()<=43023) ,13.5,IF(AND(TODAY()>=43024, TODAY()<=43054),15,IF(AND( TODAY()>=43055,TODAY()<=43084) ,16.5,IF(AND(TODAY()>=43085, TODAY()>=43085),18,0)) ))) Please check excel sheet...Thanks in advance for help! -- Sky "Good, Better, Best!Always listen to your heart, because there lives your parents. -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/ discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscribe@ googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at https://groups.google.com/ group/excel-macros. For
Re: $$Excel-Macros$$ How to make dynamic formula for value change on fix date every month
Dear Paul, Thank you so much for your support but I don't have need formula calculation start from today. It should be start from my provided date...pls recheck that time my file i am sure you will be got my query. On Mar 4, 2017 1:58 AM, "Paul Schreiner" wrote: > First of all, your IF() statement: > A nested if statement will evaluate each section until it finds a true() > option. > So, in the first case: > IF(TODAY()<=42750 > evaluates false, that means today is > 42750 > So the second part: > IF(AND(TODAY()>=42751 HAS to be true, so there's no reason to test it! > Your IF statement could be simplified to: > IF(TODAY()<=42750,0, > IF(TODAY()<=42781,1.5, > IF(TODAY()<=42809,3, > IF(TODAY()<=42840,4.5, > IF(TODAY()<=42870,6, > IF(TODAY()<=42901,7.5, > IF(TODAY()<=42931,9, > IF(TODAY()<=42962,10.5, > IF(TODAY()<=42993,12, > IF(TODAY()<=43023,13.5, > IF(TODAY()<=43054,15, > IF(TODAY()<=43084,16.5, > IF(TODAY()>=43085,18,0) > > But, you can simplify it even more. > You're using the 15th of the month as the "break point". > So, if you were to subtract 15 days, you'd be in the "same" month if the > day is > 15, and the PREVIOUS month if it is before the 15th. > Basically, by subtracting 15, you're setting the "break point" as the > first of the month. > So, if you determine the month number: (MONTH(A5-15)) you could say you > want 1.5 for each month, or: > MONTH(A6-15) * 15 > Now, the issue is with the first half of January. > subtracting 15 makes it December of the PREVIOUS year, and therefore > should be 0 instead of 18. > > so, you need to check to see if the offset year is the same as the current > year: > =IF(YEAR(A5-15) <> YEAR(TODAY()),0, MONTH(A5-15)*1.5) > > This seems match the same as your sample values for rows 5-9, > but I'm not sure what you were doing with rows 12-16 > Why does 16-Mar in row 8 result in 4.5, but 16-Mar in row 13 is supposed > to be 1.5 ??? > I'm not sure what the rules you're using here. > > as for your second query: > > You're basically wanting to round off your number to the nearest 1/2. > You can accomplish this by first doubling the number and removing the > decimal portion > (extract just the integer portion) > then divide it by 2: > =INT(A23*2)/2 > > this works for your samples. > > hope this helps. > > > > *Paul* > - > > > > > > > > *“Do all the good you can,By all the means you can,In all the ways you > can,In all the places you can,At all the times you can,To all the people > you can,As long as ever you can.” - John Wesley* > - > > > On Friday, March 3, 2017 12:08 PM, Sunil Kumar Yadav < > sk.yadav7...@gmail.com> wrote: > > > Dear All, > > I have two query for automatically update value on one fixed date, have > created formula but need dynamic formula. > > Formula: > IF(TODAY()<=42750,0,IF(AND(TODAY()>=42751,TODAY()<=42781) > ,1.5,IF(AND(TODAY()>=42782,TODAY()<=42809),3,IF(AND( > TODAY()>=42810,TODAY()<=42840),4.5,IF(AND(TODAY()>=42841, > TODAY()<=42870),6,IF(AND(TODAY()>=42871,TODAY()<=42901) > ,7.5,IF(AND(TODAY()>=42902,TODAY()<=42931),9,IF(AND( > TODAY()>=42932,TODAY()<=42962),10.5,IF(AND(TODAY()>=42963, > TODAY()<=42993),12,IF(AND(TODAY()>=42994,TODAY()<=43023) > ,13.5,IF(AND(TODAY()>=43024,TODAY()<=43054),15,IF(AND( > TODAY()>=43055,TODAY()<=43084),16.5,IF(AND(TODAY()>=43085, > TODAY()>=43085),18,0) > > Please check excel sheet...Thanks in advance for help! > > -- > *Sky* > "Good, Better, Best > ! > *Always listen to your heart* > *, because there lives your parents.* > > > -- > Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s > =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ > https://www.facebook.com/discussexcel > > FORUM RULES > > 1) Use concise, accurate thread titles. Poor thread titles, like Please > Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice > will not get quick attention or may not be answered. > 2) Don't post a question in the thread of another member. > 3) Don't post questions regarding breaking or bypassing any security > measure. > 4) Acknowledge the responses you receive, good or bad. > 5) Jobs posting is not allowed. > 6) Sharing copyrighted material and their links is not allowed. > > NOTE : Don't ever post confidential data in a workbook. Forum owners and > members are not responsible for any loss. > --- > You received this message because you are subscribed to the Google Groups > "MS EXCEL AND VBA MACROS" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to excel-macros+unsubscr...@googlegroups.com. > To post to this group, send email to excel-macros@googlegroups.com. > Visit this group at https://groups.google.com/group/excel-macros. > For more options, visit https://groups.google.com/d/optout. > > > -- > Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s > =TIME(2,DO:IT,N:OW) ! Join official Faceboo
Re: $$Excel-Macros$$ How to make dynamic formula for value change on fix date every month
On Sat, 3/4/17, silvers.r via MS EXCEL AND VBA MACROS wrote: Subject: Re: $$Excel-Macros$$ How to make dynamic formula for value change on fix date every month To: excel-macros@googlegroups.com Date: Saturday, March 4, 2017, 5:04 AM On Sat, 3/4/17, martinez.david533 via MS EXCEL AND VBA MACROS wrote: Subject: Re: $$Excel-Macros$$ How to make dynamic formula for value change on fix date every month To: excel-macros@googlegroups.com Date: Saturday, March 4, 2017, 4:03 AM On Fri, 3/3/17, libertystringer via MS EXCEL AND VBA MACROS wrote: Subject: Re: $$Excel-Macros$$ How to make dynamic formula for value change on fix date every month To: excel-macros@googlegroups.com Date: Friday, March 3, 2017, 11:35 PM On Fri, 3/3/17, Paul Schreiner wrote: Subject: Re: $$Excel-Macros$$ How to make dynamic formula for value change on fix date every month To: "excel-macros@googlegroups.com" Date: Friday, March 3, 2017, 10:27 PM First of all, your IF() statement:A nested if statement will evaluate each section until it finds a true() option.So, in the first case:IF(TODAY()<=42750evaluates false, that means today is > 42750So the second part: IF(AND(TODAY()>=42751 HAS to be true, so there's no reason to test it!Your IF statement could be simplified to:IF(TODAY()<=42750,0, IF(TODAY()<=42781,1.5, IF(TODAY()<=42809,3, IF(TODAY()<=42840,4.5, IF(TODAY()<=42870,6, IF(TODAY()<=42901,7.5, IF(TODAY()<=42931,9, IF(TODAY()<=42962,10.5, IF(TODAY()<=42993,12, IF(TODAY()<=43023,13.5, IF(TODAY()<=43054,15, IF(TODAY()<=43084,16.5, IF(TODAY()>=43085,18,0) But, you can simplify it even more.You're using the 15th of the month as the "break point".So, if you were to subtract 15 days, you'd be in the "same" month if the day is > 15, and the PREVIOUS month if it is before the 15th.Basically, by subtracting 15, you're setting the "break point" as the first of the month.So, if you determine the month number: (MONTH(A5-15)) you could say you want 1.5 for each month, or:MONTH(A6-15) * 15Now, the issue is with the first half of January.subtracting 15 makes it December of the PREVIOUS year, and therefore should be 0 instead of 18. so, you need to check to see if the offset year is the same as the current year:=IF(YEAR(A5-15) <> YEAR(TODAY()),0, MONTH(A5-15)*1.5) This seems match the same as your sample values for rows 5-9,but I'm not sure what you were doing with rows 12-16Why does 16-Mar in row 8 result in 4.5, but 16-Mar in row 13 is supposed to be 1.5 ???I'm not sure what the rules you're using here. as for your second query: You're basically wanting to round off your number to the nearest 1/2.You can accomplish this by first doubling the number and removing the decimal portion(extract just the integer portion)then divide it by 2:=INT(A23*2)/2 this works for your samples. hope this helps. Paul- “Do all the good you can, By all the means you can, In all the ways you can, In all the places you can, At all the times you can, To all the people you can, As long as ever you can.” - John Wesley - On Friday, March 3, 2017 12:08 PM, Sunil Kumar Yadav wrote: Dear All, I have two query for automatically update value on one fixed date, have created formula but need dynamic formula. Formula:IF(TODAY()<=42750,0,IF(AND(TODAY()>=42751,TODAY()<=42781),1.5,IF(AND(TODAY()>=42782,TODAY()<=42809),3,IF(AND(TODAY()>=42810,TODAY()<=42840),4.5,IF(AND(TODAY()>=42841,TODAY()<=42870),6,IF(AND(TODAY()>=42871,TODAY()<=42901),7.5,IF(AND(TODAY()>=42902,TODAY()<=42931),9,IF(AND(TODAY()>=42932,TODAY()<=42962),10.5,IF(AND(TODAY()>=42963,TODAY()<=42993),12,IF(AND(TODAY()>=42994,TODAY()<=43023),13.5,IF(AND(TODAY()>=43024,TODAY()<=43054),15,IF(AND(TODAY()>=43055,TODAY()<=43084),16.5,IF(AND(TODAY()>=43085,TODAY()>=43085),18,0) Please check excel sheet...Thanks in advance for help! -- Sky "Good, Better, Best!Always listen to your heart, because there lives your parents. -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, acc
Re: $$Excel-Macros$$ How to make dynamic formula for value change on fix date every month
On Sat, 3/4/17, martinez.david533 via MS EXCEL AND VBA MACROS wrote: Subject: Re: $$Excel-Macros$$ How to make dynamic formula for value change on fix date every month To: excel-macros@googlegroups.com Date: Saturday, March 4, 2017, 4:03 AM On Fri, 3/3/17, libertystringer via MS EXCEL AND VBA MACROS wrote: Subject: Re: $$Excel-Macros$$ How to make dynamic formula for value change on fix date every month To: excel-macros@googlegroups.com Date: Friday, March 3, 2017, 11:35 PM On Fri, 3/3/17, Paul Schreiner wrote: Subject: Re: $$Excel-Macros$$ How to make dynamic formula for value change on fix date every month To: "excel-macros@googlegroups.com" Date: Friday, March 3, 2017, 10:27 PM First of all, your IF() statement:A nested if statement will evaluate each section until it finds a true() option.So, in the first case:IF(TODAY()<=42750evaluates false, that means today is > 42750So the second part: IF(AND(TODAY()>=42751 HAS to be true, so there's no reason to test it!Your IF statement could be simplified to:IF(TODAY()<=42750,0, IF(TODAY()<=42781,1.5, IF(TODAY()<=42809,3, IF(TODAY()<=42840,4.5, IF(TODAY()<=42870,6, IF(TODAY()<=42901,7.5, IF(TODAY()<=42931,9, IF(TODAY()<=42962,10.5, IF(TODAY()<=42993,12, IF(TODAY()<=43023,13.5, IF(TODAY()<=43054,15, IF(TODAY()<=43084,16.5, IF(TODAY()>=43085,18,0) But, you can simplify it even more.You're using the 15th of the month as the "break point".So, if you were to subtract 15 days, you'd be in the "same" month if the day is > 15, and the PREVIOUS month if it is before the 15th.Basically, by subtracting 15, you're setting the "break point" as the first of the month.So, if you determine the month number: (MONTH(A5-15)) you could say you want 1.5 for each month, or:MONTH(A6-15) * 15Now, the issue is with the first half of January.subtracting 15 makes it December of the PREVIOUS year, and therefore should be 0 instead of 18. so, you need to check to see if the offset year is the same as the current year:=IF(YEAR(A5-15) <> YEAR(TODAY()),0, MONTH(A5-15)*1.5) This seems match the same as your sample values for rows 5-9,but I'm not sure what you were doing with rows 12-16Why does 16-Mar in row 8 result in 4.5, but 16-Mar in row 13 is supposed to be 1.5 ???I'm not sure what the rules you're using here. as for your second query: You're basically wanting to round off your number to the nearest 1/2.You can accomplish this by first doubling the number and removing the decimal portion(extract just the integer portion)then divide it by 2:=INT(A23*2)/2 this works for your samples. hope this helps. Paul- “Do all the good you can, By all the means you can, In all the ways you can, In all the places you can, At all the times you can, To all the people you can, As long as ever you can.” - John Wesley - On Friday, March 3, 2017 12:08 PM, Sunil Kumar Yadav wrote: Dear All, I have two query for automatically update value on one fixed date, have created formula but need dynamic formula. Formula:IF(TODAY()<=42750,0,IF(AND(TODAY()>=42751,TODAY()<=42781),1.5,IF(AND(TODAY()>=42782,TODAY()<=42809),3,IF(AND(TODAY()>=42810,TODAY()<=42840),4.5,IF(AND(TODAY()>=42841,TODAY()<=42870),6,IF(AND(TODAY()>=42871,TODAY()<=42901),7.5,IF(AND(TODAY()>=42902,TODAY()<=42931),9,IF(AND(TODAY()>=42932,TODAY()<=42962),10.5,IF(AND(TODAY()>=42963,TODAY()<=42993),12,IF(AND(TODAY()>=42994,TODAY()<=43023),13.5,IF(AND(TODAY()>=43024,TODAY()<=43054),15,IF(AND(TODAY()>=43055,TODAY()<=43084),16.5,IF(AND(TODAY()>=43085,TODAY()>=43085),18,0) Please check excel sheet...Thanks in advance for help! -- Sky "Good, Better, Best!Always listen to your heart, because there lives your parents. -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed.
Re: $$Excel-Macros$$ How to make dynamic formula for value change on fix date every month
On Fri, 3/3/17, libertystringer via MS EXCEL AND VBA MACROS wrote: Subject: Re: $$Excel-Macros$$ How to make dynamic formula for value change on fix date every month To: excel-macros@googlegroups.com Date: Friday, March 3, 2017, 11:35 PM On Fri, 3/3/17, Paul Schreiner wrote: Subject: Re: $$Excel-Macros$$ How to make dynamic formula for value change on fix date every month To: "excel-macros@googlegroups.com" Date: Friday, March 3, 2017, 10:27 PM First of all, your IF() statement:A nested if statement will evaluate each section until it finds a true() option.So, in the first case:IF(TODAY()<=42750evaluates false, that means today is > 42750So the second part: IF(AND(TODAY()>=42751 HAS to be true, so there's no reason to test it!Your IF statement could be simplified to:IF(TODAY()<=42750,0, IF(TODAY()<=42781,1.5, IF(TODAY()<=42809,3, IF(TODAY()<=42840,4.5, IF(TODAY()<=42870,6, IF(TODAY()<=42901,7.5, IF(TODAY()<=42931,9, IF(TODAY()<=42962,10.5, IF(TODAY()<=42993,12, IF(TODAY()<=43023,13.5, IF(TODAY()<=43054,15, IF(TODAY()<=43084,16.5, IF(TODAY()>=43085,18,0) But, you can simplify it even more.You're using the 15th of the month as the "break point".So, if you were to subtract 15 days, you'd be in the "same" month if the day is > 15, and the PREVIOUS month if it is before the 15th.Basically, by subtracting 15, you're setting the "break point" as the first of the month.So, if you determine the month number: (MONTH(A5-15)) you could say you want 1.5 for each month, or:MONTH(A6-15) * 15Now, the issue is with the first half of January.subtracting 15 makes it December of the PREVIOUS year, and therefore should be 0 instead of 18. so, you need to check to see if the offset year is the same as the current year:=IF(YEAR(A5-15) <> YEAR(TODAY()),0, MONTH(A5-15)*1.5) This seems match the same as your sample values for rows 5-9,but I'm not sure what you were doing with rows 12-16Why does 16-Mar in row 8 result in 4.5, but 16-Mar in row 13 is supposed to be 1.5 ???I'm not sure what the rules you're using here. as for your second query: You're basically wanting to round off your number to the nearest 1/2.You can accomplish this by first doubling the number and removing the decimal portion(extract just the integer portion)then divide it by 2:=INT(A23*2)/2 this works for your samples. hope this helps. Paul- “Do all the good you can, By all the means you can, In all the ways you can, In all the places you can, At all the times you can, To all the people you can, As long as ever you can.” - John Wesley - On Friday, March 3, 2017 12:08 PM, Sunil Kumar Yadav wrote: Dear All, I have two query for automatically update value on one fixed date, have created formula but need dynamic formula. Formula:IF(TODAY()<=42750,0,IF(AND(TODAY()>=42751,TODAY()<=42781),1.5,IF(AND(TODAY()>=42782,TODAY()<=42809),3,IF(AND(TODAY()>=42810,TODAY()<=42840),4.5,IF(AND(TODAY()>=42841,TODAY()<=42870),6,IF(AND(TODAY()>=42871,TODAY()<=42901),7.5,IF(AND(TODAY()>=42902,TODAY()<=42931),9,IF(AND(TODAY()>=42932,TODAY()<=42962),10.5,IF(AND(TODAY()>=42963,TODAY()<=42993),12,IF(AND(TODAY()>=42994,TODAY()<=43023),13.5,IF(AND(TODAY()>=43024,TODAY()<=43054),15,IF(AND(TODAY()>=43055,TODAY()<=43084),16.5,IF(AND(TODAY()>=43085,TODAY()>=43085),18,0) Please check excel sheet...Thanks in advance for help! -- Sky "Good, Better, Best!Always listen to your heart, because there lives your parents. -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To
Re: $$Excel-Macros$$ How to make dynamic formula for value change on fix date every month
On Fri, 3/3/17, Paul Schreiner wrote: Subject: Re: $$Excel-Macros$$ How to make dynamic formula for value change on fix date every month To: "excel-macros@googlegroups.com" Date: Friday, March 3, 2017, 10:27 PM First of all, your IF() statement:A nested if statement will evaluate each section until it finds a true() option.So, in the first case:IF(TODAY()<=42750evaluates false, that means today is > 42750So the second part: IF(AND(TODAY()>=42751 HAS to be true, so there's no reason to test it!Your IF statement could be simplified to:IF(TODAY()<=42750,0, IF(TODAY()<=42781,1.5, IF(TODAY()<=42809,3, IF(TODAY()<=42840,4.5, IF(TODAY()<=42870,6, IF(TODAY()<=42901,7.5, IF(TODAY()<=42931,9, IF(TODAY()<=42962,10.5, IF(TODAY()<=42993,12, IF(TODAY()<=43023,13.5, IF(TODAY()<=43054,15, IF(TODAY()<=43084,16.5, IF(TODAY()>=43085,18,0) But, you can simplify it even more.You're using the 15th of the month as the "break point".So, if you were to subtract 15 days, you'd be in the "same" month if the day is > 15, and the PREVIOUS month if it is before the 15th.Basically, by subtracting 15, you're setting the "break point" as the first of the month.So, if you determine the month number: (MONTH(A5-15)) you could say you want 1.5 for each month, or:MONTH(A6-15) * 15Now, the issue is with the first half of January.subtracting 15 makes it December of the PREVIOUS year, and therefore should be 0 instead of 18. so, you need to check to see if the offset year is the same as the current year:=IF(YEAR(A5-15) <> YEAR(TODAY()),0, MONTH(A5-15)*1.5) This seems match the same as your sample values for rows 5-9,but I'm not sure what you were doing with rows 12-16Why does 16-Mar in row 8 result in 4.5, but 16-Mar in row 13 is supposed to be 1.5 ???I'm not sure what the rules you're using here. as for your second query: You're basically wanting to round off your number to the nearest 1/2.You can accomplish this by first doubling the number and removing the decimal portion(extract just the integer portion)then divide it by 2:=INT(A23*2)/2 this works for your samples. hope this helps. Paul- “Do all the good you can, By all the means you can, In all the ways you can, In all the places you can, At all the times you can, To all the people you can, As long as ever you can.” - John Wesley - On Friday, March 3, 2017 12:08 PM, Sunil Kumar Yadav wrote: Dear All, I have two query for automatically update value on one fixed date, have created formula but need dynamic formula. Formula:IF(TODAY()<=42750,0,IF(AND(TODAY()>=42751,TODAY()<=42781),1.5,IF(AND(TODAY()>=42782,TODAY()<=42809),3,IF(AND(TODAY()>=42810,TODAY()<=42840),4.5,IF(AND(TODAY()>=42841,TODAY()<=42870),6,IF(AND(TODAY()>=42871,TODAY()<=42901),7.5,IF(AND(TODAY()>=42902,TODAY()<=42931),9,IF(AND(TODAY()>=42932,TODAY()<=42962),10.5,IF(AND(TODAY()>=42963,TODAY()<=42993),12,IF(AND(TODAY()>=42994,TODAY()<=43023),13.5,IF(AND(TODAY()>=43024,TODAY()<=43054),15,IF(AND(TODAY()>=43055,TODAY()<=43084),16.5,IF(AND(TODAY()>=43085,TODAY()>=43085),18,0) Please check excel sheet...Thanks in advance for help! -- Sky "Good, Better, Best!Always listen to your heart, because there lives your parents. -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at https://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout. -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread
Re: $$Excel-Macros$$ How to make dynamic formula for value change on fix date every month
On Fri, 3/3/17, Paul Schreiner wrote: Subject: Re: $$Excel-Macros$$ How to make dynamic formula for value change on fix date every month To: "excel-macros@googlegroups.com" Date: Friday, March 3, 2017, 10:27 PM First of all, your IF() statement:A nested if statement will evaluate each section until it finds a true() option.So, in the first case:IF(TODAY()<=42750evaluates false, that means today is > 42750So the second part: IF(AND(TODAY()>=42751 HAS to be true, so there's no reason to test it!Your IF statement could be simplified to:IF(TODAY()<=42750,0, IF(TODAY()<=42781,1.5, IF(TODAY()<=42809,3, IF(TODAY()<=42840,4.5, IF(TODAY()<=42870,6, IF(TODAY()<=42901,7.5, IF(TODAY()<=42931,9, IF(TODAY()<=42962,10.5, IF(TODAY()<=42993,12, IF(TODAY()<=43023,13.5, IF(TODAY()<=43054,15, IF(TODAY()<=43084,16.5, IF(TODAY()>=43085,18,0) But, you can simplify it even more.You're using the 15th of the month as the "break point".So, if you were to subtract 15 days, you'd be in the "same" month if the day is > 15, and the PREVIOUS month if it is before the 15th.Basically, by subtracting 15, you're setting the "break point" as the first of the month.So, if you determine the month number: (MONTH(A5-15)) you could say you want 1.5 for each month, or:MONTH(A6-15) * 15Now, the issue is with the first half of January.subtracting 15 makes it December of the PREVIOUS year, and therefore should be 0 instead of 18. so, you need to check to see if the offset year is the same as the current year:=IF(YEAR(A5-15) <> YEAR(TODAY()),0, MONTH(A5-15)*1.5) This seems match the same as your sample values for rows 5-9,but I'm not sure what you were doing with rows 12-16Why does 16-Mar in row 8 result in 4.5, but 16-Mar in row 13 is supposed to be 1.5 ???I'm not sure what the rules you're using here. as for your second query: You're basically wanting to round off your number to the nearest 1/2.You can accomplish this by first doubling the number and removing the decimal portion(extract just the integer portion)then divide it by 2:=INT(A23*2)/2 this works for your samples. hope this helps. Paul- “Do all the good you can, By all the means you can, In all the ways you can, In all the places you can, At all the times you can, To all the people you can, As long as ever you can.” - John Wesley - On Friday, March 3, 2017 12:08 PM, Sunil Kumar Yadav wrote: Dear All, I have two query for automatically update value on one fixed date, have created formula but need dynamic formula. Formula:IF(TODAY()<=42750,0,IF(AND(TODAY()>=42751,TODAY()<=42781),1.5,IF(AND(TODAY()>=42782,TODAY()<=42809),3,IF(AND(TODAY()>=42810,TODAY()<=42840),4.5,IF(AND(TODAY()>=42841,TODAY()<=42870),6,IF(AND(TODAY()>=42871,TODAY()<=42901),7.5,IF(AND(TODAY()>=42902,TODAY()<=42931),9,IF(AND(TODAY()>=42932,TODAY()<=42962),10.5,IF(AND(TODAY()>=42963,TODAY()<=42993),12,IF(AND(TODAY()>=42994,TODAY()<=43023),13.5,IF(AND(TODAY()>=43024,TODAY()<=43054),15,IF(AND(TODAY()>=43055,TODAY()<=43084),16.5,IF(AND(TODAY()>=43085,TODAY()>=43085),18,0) Please check excel sheet...Thanks in advance for help! -- Sky "Good, Better, Best!Always listen to your heart, because there lives your parents. -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at https://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout. -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread
Re: $$Excel-Macros$$ How to make dynamic formula for value change on fix date every month
First of all, your IF() statement:A nested if statement will evaluate each section until it finds a true() option.So, in the first case:IF(TODAY()<=42750evaluates false, that means today is > 42750So the second part: IF(AND(TODAY()>=42751 HAS to be true, so there's no reason to test it!Your IF statement could be simplified to:IF(TODAY()<=42750,0, IF(TODAY()<=42781,1.5, IF(TODAY()<=42809,3, IF(TODAY()<=42840,4.5, IF(TODAY()<=42870,6, IF(TODAY()<=42901,7.5, IF(TODAY()<=42931,9, IF(TODAY()<=42962,10.5, IF(TODAY()<=42993,12, IF(TODAY()<=43023,13.5, IF(TODAY()<=43054,15, IF(TODAY()<=43084,16.5, IF(TODAY()>=43085,18,0) But, you can simplify it even more.You're using the 15th of the month as the "break point".So, if you were to subtract 15 days, you'd be in the "same" month if the day is > 15, and the PREVIOUS month if it is before the 15th.Basically, by subtracting 15, you're setting the "break point" as the first of the month.So, if you determine the month number: (MONTH(A5-15)) you could say you want 1.5 for each month, or:MONTH(A6-15) * 15Now, the issue is with the first half of January.subtracting 15 makes it December of the PREVIOUS year, and therefore should be 0 instead of 18. so, you need to check to see if the offset year is the same as the current year:=IF(YEAR(A5-15) <> YEAR(TODAY()),0, MONTH(A5-15)*1.5) This seems match the same as your sample values for rows 5-9,but I'm not sure what you were doing with rows 12-16Why does 16-Mar in row 8 result in 4.5, but 16-Mar in row 13 is supposed to be 1.5 ???I'm not sure what the rules you're using here. as for your second query: You're basically wanting to round off your number to the nearest 1/2.You can accomplish this by first doubling the number and removing the decimal portion(extract just the integer portion)then divide it by 2:=INT(A23*2)/2 this works for your samples. hope this helps. Paul- “Do all the good you can, By all the means you can, In all the ways you can, In all the places you can, At all the times you can, To all the people you can, As long as ever you can.” - John Wesley - On Friday, March 3, 2017 12:08 PM, Sunil Kumar Yadav wrote: Dear All, I have two query for automatically update value on one fixed date, have created formula but need dynamic formula. Formula:IF(TODAY()<=42750,0,IF(AND(TODAY()>=42751,TODAY()<=42781),1.5,IF(AND(TODAY()>=42782,TODAY()<=42809),3,IF(AND(TODAY()>=42810,TODAY()<=42840),4.5,IF(AND(TODAY()>=42841,TODAY()<=42870),6,IF(AND(TODAY()>=42871,TODAY()<=42901),7.5,IF(AND(TODAY()>=42902,TODAY()<=42931),9,IF(AND(TODAY()>=42932,TODAY()<=42962),10.5,IF(AND(TODAY()>=42963,TODAY()<=42993),12,IF(AND(TODAY()>=42994,TODAY()<=43023),13.5,IF(AND(TODAY()>=43024,TODAY()<=43054),15,IF(AND(TODAY()>=43055,TODAY()<=43084),16.5,IF(AND(TODAY()>=43085,TODAY()>=43085),18,0) Please check excel sheet...Thanks in advance for help! -- Sky "Good, Better, Best!Always listen to your heart, because there lives your parents. -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at https://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout. -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not
Re: $$Excel-Macros$$ VBA Run time error 13 type mismatch in Excel 2010 with Datavalidation & VBA
On Fri, 3/3/17, margaretatolliver via MS EXCEL AND VBA MACROS wrote: Subject: Re: $$Excel-Macros$$ VBA Run time error 13 type mismatch in Excel 2010 with Datavalidation & VBA To: excel-macros@googlegroups.com Date: Friday, March 3, 2017, 3:43 PM On Fri, 3/3/17, keisha.fry via MS EXCEL AND VBA MACROS wrote: Subject: Re: $$Excel-Macros$$ VBA Run time error 13 type mismatch in Excel 2010 with Datavalidation & VBA To: excel-macros@googlegroups.com Date: Friday, March 3, 2017, 1:58 PM On Fri, 3/3/17, Maatangi M. Karthik wrote: Subject: Re: $$Excel-Macros$$ VBA Run time error 13 type mismatch in Excel 2010 with Datavalidation & VBA To: "MS EXCEL AND VBA MACROS" Cc: schreiner_p...@att.net Date: Friday, March 3, 2017, 7:16 AM Hi there are a few more things I want from the excel. 1. There are some formulae in my excel sheet, I would like to convert them to the backend so that the users cannot change them at any cost 2. There are some fields that affect these formulae in succession, that is, two or three cell values together will make up the formula for another cell. I want to be able to capture the input event and the formula creation based on this input. For example, there is a field where a due date gets calculated. And this column is dependent on 2 other columns' values. so I want to capture those 2 columns' input events and invoke the formula once they are filled. 3. In the excel there are 3 fields, Tax,sales price with tax and sales price without tax. At present I have given the option of entering / choosing the Tax % and entering the without tax price. However, there may be cases where the user will know only the with tax price and the tax %. this creates double work for them as they have to compute the without tax price and then paste it in this excel. I want to avoid this double work and give the option for the users to enter price in either of the columns and then be able to compute based on which one is input. 4. Last query for now, I tried Locking / hiding the formulae for certain fields; however, they don't take effect unless the excel sheet is protected and if I protect the sheet, then users are unable to edit / input their values where necessary. Is there a VBA workaround for this? Most of the date fields have to be locked as well and should not be allowed to be changed by users. On Friday, 3 March 2017 01:02:22 UTC+5:30, Paul Schreiner wrote:I deleted a row and received the "Type Mismatch" error.If you select "Debug", you'll see that the offending line of code is: If Target.Value = "OrderStatus" ThenPutting a "watch" on the Target variable, you'll see that it isn't a single cell, but an array of cells! When you change multiple cells (even by deleting, or inserting, or copying), then the Change event is passed an range ARRAY rather than a single range. In your case, you have a choice.You can add an if() statement like:if (Target.count > 1) then exit sub Or, you can process each cell in the target array like: dim Targ as rangefor each Targ in Target... (replace each occurrence of "Target" with "Targ") ...Next Targ Paul-- --- “Do all the good you can, By all the means you can, In all the ways you can, In all the places you can, At all the times you can, To all the people you can, As long as ever you can.” - John Wesley -- --- On Thursday, March 2, 2017 12:33 PM, Maatangi M. Karthik wrote: I am absolutely new to excel VBA. I have a requirement to create an excel to maintain status of orders (there are about 6 / 7 order statuses (2 of which are OrderStatus(no status), Enquiry and taken as samples here ) and based on each status, a specific set of actions have to be performed. I have created the excel in which there are multiple columns & rows, some of the columns have data validations either from a reference sheet or entered as list in the Data validation part and some have formula references. What little coding I could understand, I have done that based on my teeny weeny bit of knowledge + the ideas that I got from checking on various websites. As a result, I am not sure if I have a robust code, although, I have some piece of coding to perform certain actions based on values change in certain columns. Following are the issues that I want help with:
$$Excel-Macros$$ How to make dynamic formula for value change on fix date every month
Dear All, I have two query for automatically update value on one fixed date, have created formula but need dynamic formula. Formula: IF(TODAY()<=42750,0,IF(AND(TODAY()>=42751,TODAY()<=42781),1.5,IF(AND(TODAY()>=42782,TODAY()<=42809),3,IF(AND(TODAY()>=42810,TODAY()<=42840),4.5,IF(AND(TODAY()>=42841,TODAY()<=42870),6,IF(AND(TODAY()>=42871,TODAY()<=42901),7.5,IF(AND(TODAY()>=42902,TODAY()<=42931),9,IF(AND(TODAY()>=42932,TODAY()<=42962),10.5,IF(AND(TODAY()>=42963,TODAY()<=42993),12,IF(AND(TODAY()>=42994,TODAY()<=43023),13.5,IF(AND(TODAY()>=43024,TODAY()<=43054),15,IF(AND(TODAY()>=43055,TODAY()<=43084),16.5,IF(AND(TODAY()>=43085,TODAY()>=43085),18,0) Please check excel sheet...Thanks in advance for help! -- *Sky* "Good, Better, Best ! *Always listen to your heart* *, because there lives your parents.* -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at https://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout. Query.xlsx Description: MS-Excel 2007 spreadsheet
Re: $$Excel-Macros$$ VBA Run time error 13 type mismatch in Excel 2010 with Datavalidation & VBA
On Fri, 3/3/17, keisha.fry via MS EXCEL AND VBA MACROS wrote: Subject: Re: $$Excel-Macros$$ VBA Run time error 13 type mismatch in Excel 2010 with Datavalidation & VBA To: excel-macros@googlegroups.com Date: Friday, March 3, 2017, 1:58 PM On Fri, 3/3/17, Maatangi M. Karthik wrote: Subject: Re: $$Excel-Macros$$ VBA Run time error 13 type mismatch in Excel 2010 with Datavalidation & VBA To: "MS EXCEL AND VBA MACROS" Cc: schreiner_p...@att.net Date: Friday, March 3, 2017, 7:16 AM Hi there are a few more things I want from the excel. 1. There are some formulae in my excel sheet, I would like to convert them to the backend so that the users cannot change them at any cost 2. There are some fields that affect these formulae in succession, that is, two or three cell values together will make up the formula for another cell. I want to be able to capture the input event and the formula creation based on this input. For example, there is a field where a due date gets calculated. And this column is dependent on 2 other columns' values. so I want to capture those 2 columns' input events and invoke the formula once they are filled. 3. In the excel there are 3 fields, Tax,sales price with tax and sales price without tax. At present I have given the option of entering / choosing the Tax % and entering the without tax price. However, there may be cases where the user will know only the with tax price and the tax %. this creates double work for them as they have to compute the without tax price and then paste it in this excel. I want to avoid this double work and give the option for the users to enter price in either of the columns and then be able to compute based on which one is input. 4. Last query for now, I tried Locking / hiding the formulae for certain fields; however, they don't take effect unless the excel sheet is protected and if I protect the sheet, then users are unable to edit / input their values where necessary. Is there a VBA workaround for this? Most of the date fields have to be locked as well and should not be allowed to be changed by users. On Friday, 3 March 2017 01:02:22 UTC+5:30, Paul Schreiner wrote:I deleted a row and received the "Type Mismatch" error.If you select "Debug", you'll see that the offending line of code is: If Target.Value = "OrderStatus" ThenPutting a "watch" on the Target variable, you'll see that it isn't a single cell, but an array of cells! When you change multiple cells (even by deleting, or inserting, or copying), then the Change event is passed an range ARRAY rather than a single range. In your case, you have a choice.You can add an if() statement like:if (Target.count > 1) then exit sub Or, you can process each cell in the target array like: dim Targ as rangefor each Targ in Target... (replace each occurrence of "Target" with "Targ") ...Next Targ Paul-- --- “Do all the good you can, By all the means you can, In all the ways you can, In all the places you can, At all the times you can, To all the people you can, As long as ever you can.” - John Wesley -- --- On Thursday, March 2, 2017 12:33 PM, Maatangi M. Karthik wrote: I am absolutely new to excel VBA. I have a requirement to create an excel to maintain status of orders (there are about 6 / 7 order statuses (2 of which are OrderStatus(no status), Enquiry and taken as samples here ) and based on each status, a specific set of actions have to be performed. I have created the excel in which there are multiple columns & rows, some of the columns have data validations either from a reference sheet or entered as list in the Data validation part and some have formula references. What little coding I could understand, I have done that based on my teeny weeny bit of knowledge + the ideas that I got from checking on various websites. As a result, I am not sure if I have a robust code, although, I have some piece of coding to perform certain actions based on values change in certain columns. Following are the issues that I want help with: When inserting / deleting row / rows, get error "Type Mismatch error 13" The same error appears while certain columns are extended down to the cells. Same error while cut copy pasting a row Although the run time error occurs, the action is partially completed Earliest response would be much appreciated. My sample excel is attached -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel?
Re: $$Excel-Macros$$ VBA Run time error 13 type mismatch in Excel 2010 with Datavalidation & VBA
First of all (last of all?):You can lock some cells and leave others available for editing:- select all cells- right click on the selection - Select "Format Cells"- select "Protection" tab- Make sure "Locked" is checked.- click "OK" When the sheet is protected, all cells with this "locked" attribute will be.. um... "locked"...Now select the cells you want to allow users to enter data into.then:- Select "Format Cells"- select "Protection" tab- Make sure "Locked" is NOT checked.- click "OK" Now, depending on what options you choose when you protect the sheet, the cells with the formulas cannot even be selected!while the user entry cells are left unchanged. If you want to be able to copy and paste all the data INCLUDING the locked cells to another sheet,make the appropriate selection when protecting the sheet. I think it "SOUNDS" like you want to accomplish your other items using Excel functions rather than VBA.(however, VBA is ALWAYS an option) But it would be a LOT easier for both of us if you could share a sample file(even with "dummy" data) I'd be glad to help. Paul- “Do all the good you can, By all the means you can, In all the ways you can, In all the places you can, At all the times you can, To all the people you can, As long as ever you can.” - John Wesley - On Friday, March 3, 2017 12:16 AM, Maatangi M. Karthik wrote: Hi there are a few more things I want from the excel. 1. There are some formulae in my excel sheet, I would like to convert them to the backend so that the users cannot change them at any cost 2. There are some fields that affect these formulae in succession, that is, two or three cell values together will make up the formula for another cell. I want to be able to capture the input event and the formula creation based on this input. For example, there is a field where a due date gets calculated. And this column is dependent on 2 other columns' values. so I want to capture those 2 columns' input events and invoke the formula once they are filled. 3. In the excel there are 3 fields, Tax,sales price with tax and sales price without tax. At present I have given the option of entering / choosing the Tax % and entering the without tax price. However, there may be cases where the user will know only the with tax price and the tax %. this creates double work for them as they have to compute the without tax price and then paste it in this excel. I want to avoid this double work and give the option for the users to enter price in either of the columns and then be able to compute based on which one is input. 4. Last query for now, I tried Locking / hiding the formulae for certain fields; however, they don't take effect unless the excel sheet is protected and if I protect the sheet, then users are unable to edit / input their values where necessary. Is there a VBA workaround for this? Most of the date fields have to be locked as well and should not be allowed to be changed by users. On Friday, 3 March 2017 01:02:22 UTC+5:30, Paul Schreiner wrote: I deleted a row and received the "Type Mismatch" error.If you select "Debug", you'll see that the offending line of code is: If Target.Value = "OrderStatus" ThenPutting a "watch" on the Target variable, you'll see that it isn't a single cell, but an array of cells! When you change multiple cells (even by deleting, or inserting, or copying), then the Change event is passed an range ARRAY rather than a single range. In your case, you have a choice.You can add an if() statement like:if (Target.count > 1) then exit sub Or, you can process each cell in the target array like: dim Targ as rangefor each Targ in Target... (replace each occurrence of "Target" with "Targ") ...Next Targ Paul-- --- “Do all the good you can, By all the means you can, In all the ways you can, In all the places you can, At all the times you can, To all the people you can, As long as ever you can.” - John Wesley -- --- On Thursday, March 2, 2017 12:33 PM, Maatangi M. Karthik wrote: I am absolutely new to excel VBA. I have a requirement to create an excel to maintain status of orders (there are about 6 / 7 order statuses (2 of which are OrderStatus(no status), Enquiry and taken as samples here ) and based on each status, a specific set of actions have to be performed. I have created the excel in which there are multiple columns & rows, some of the columns have data validations either from a reference sheet or entered as list in the Data validation part and some have formula references. What little coding I could understand, I have done that based on my teeny weeny bit of knowledge + the ideas that I got from checking on various websites. As a result, I am not sure if I have a robust code, although, I have some piece of coding to perform certain actions based on va
Re: $$Excel-Macros$$ VBA Run time error 13 type mismatch in Excel 2010 with Datavalidation & VBA
On Fri, 3/3/17, Maatangi M. Karthik wrote: Subject: Re: $$Excel-Macros$$ VBA Run time error 13 type mismatch in Excel 2010 with Datavalidation & VBA To: "MS EXCEL AND VBA MACROS" Cc: schreiner_p...@att.net Date: Friday, March 3, 2017, 7:16 AM Hi there are a few more things I want from the excel. 1. There are some formulae in my excel sheet, I would like to convert them to the backend so that the users cannot change them at any cost 2. There are some fields that affect these formulae in succession, that is, two or three cell values together will make up the formula for another cell. I want to be able to capture the input event and the formula creation based on this input. For example, there is a field where a due date gets calculated. And this column is dependent on 2 other columns' values. so I want to capture those 2 columns' input events and invoke the formula once they are filled. 3. In the excel there are 3 fields, Tax,sales price with tax and sales price without tax. At present I have given the option of entering / choosing the Tax % and entering the without tax price. However, there may be cases where the user will know only the with tax price and the tax %. this creates double work for them as they have to compute the without tax price and then paste it in this excel. I want to avoid this double work and give the option for the users to enter price in either of the columns and then be able to compute based on which one is input. 4. Last query for now, I tried Locking / hiding the formulae for certain fields; however, they don't take effect unless the excel sheet is protected and if I protect the sheet, then users are unable to edit / input their values where necessary. Is there a VBA workaround for this? Most of the date fields have to be locked as well and should not be allowed to be changed by users. On Friday, 3 March 2017 01:02:22 UTC+5:30, Paul Schreiner wrote:I deleted a row and received the "Type Mismatch" error.If you select "Debug", you'll see that the offending line of code is: If Target.Value = "OrderStatus" ThenPutting a "watch" on the Target variable, you'll see that it isn't a single cell, but an array of cells! When you change multiple cells (even by deleting, or inserting, or copying), then the Change event is passed an range ARRAY rather than a single range. In your case, you have a choice.You can add an if() statement like:if (Target.count > 1) then exit sub Or, you can process each cell in the target array like: dim Targ as rangefor each Targ in Target... (replace each occurrence of "Target" with "Targ") ...Next Targ Paul-- --- “Do all the good you can, By all the means you can, In all the ways you can, In all the places you can, At all the times you can, To all the people you can, As long as ever you can.” - John Wesley -- --- On Thursday, March 2, 2017 12:33 PM, Maatangi M. Karthik wrote: I am absolutely new to excel VBA. I have a requirement to create an excel to maintain status of orders (there are about 6 / 7 order statuses (2 of which are OrderStatus(no status), Enquiry and taken as samples here ) and based on each status, a specific set of actions have to be performed. I have created the excel in which there are multiple columns & rows, some of the columns have data validations either from a reference sheet or entered as list in the Data validation part and some have formula references. What little coding I could understand, I have done that based on my teeny weeny bit of knowledge + the ideas that I got from checking on various websites. As a result, I am not sure if I have a robust code, although, I have some piece of coding to perform certain actions based on values change in certain columns. Following are the issues that I want help with: When inserting / deleting row / rows, get error "Type Mismatch error 13" The same error appears while certain columns are extended down to the cells. Same error while cut copy pasting a row Although the run time error occurs, the action is partially completed Earliest response would be much appreciated. My sample excel is attached -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/ discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post question
