Could you send me the entire function? (you could send it directly) Is there a relationship between the variables that influences the rate? I think I'd like to look at nested Select statements.
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 ----------------------------------------- >________________________________ > From: Sara C <saracatlingu...@gmail.com> >To: excel-macros@googlegroups.com >Sent: Tuesday, February 25, 2014 3:45 PM >Subject: $$Excel-Macros$$ Long if-then function? HELP > > > >Hi, > > >I'm using a VBA macro for an invoice for a service, where the price per hour >of the service depends on 4 variables: location, type of worker, time of >service, and type of service. Each of these variables only has a few possible >values which are contained in cells the function references. > > >Recently we had 2 types of workers, but now we have 3, and I adjusted the >macro for it. There are now 59 total ElseIf conditions. However, in 3/5 of the >locations (each of which have their own Excel sheet), I am getting a #VALUE! >error, and I have no idea why. Here's a modified example of the code I'm using >(1 is location, 2 is time, 3 is type of worker, and 4 is type of service): > > >Function HrlyRate(pVal1 As String, pVal2 As String, pVal3 As String, pVal4 As >String) > >If pVal1 = "LOC1" And pVal2 = "OT" And pVal3 = "0-12M" And pVal4 = "REG" Then >HrlyRate = 5.95 > > >ElseIf pVal1 = "LOC2" And pVal2 = "OT" And pVal3 = "0-12M" And pVal4 = "REG" >Then >HrlyRate = 6.95 > >... > > >Else >HrlyRate = 0 > > >End If > > >End Function > > >I have no idea why it doesn't work for LOC3,4,5 (if it makes a difference, I >actually list all the LOC1s together, then all the LOC2s, etc.)... and I'm a >very amateur programmer as I'm sure you can tell, haha. I checked the format >(general/text/date) of the input cells and they're exactly the same between >the locations that work and the ones that don't. > >Any suggestions? > > > -- >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 http://groups.google.com/group/excel-macros. >For more options, visit https://groups.google.com/groups/opt_out. > > > -- 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 http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/groups/opt_out.
