Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to replace Formula
=IF(AND(C5<"Non
U/G",C5<"Delta",C7V2,C7<W2,C8W2),"0.75",IF(AND(C5 ="Delta",C7V2,C7<W2,C8W2),"0.5",IF(AND(C5<"N on U/G",C5<"Delta",C7<V2,C8<V2),"1.5",IF(AND(,C5="Delt a",C7<V2,C8<V2),"1",IF(AND(C5<"Non U/G",C5<"Delta",C7<V2,C8=W2),"0.75",IF(AND(C5="Del ta",C7<V2,C8=W2),"0.5",IF(AND(C5<"Non U/G",C5<"Delta",C7<V2,C8V2,C8<=W2),"0.75",IF(AND(C 7<V2,C8V2,C8<=W2),"0.5","0")))))))) I think i am at the end of the fomula limit. How to i enter this as code? -- Regards Corey |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to replace Formula
Corey, Display the Excel worksheet. Press Alt-F11 to display the code
editor. Select Insert from the menubar, then Module. Copy this code and paste it there. Function BigIf() If [c5] < "Non U/G" And [c5] < "Delta" And [c7] [v2] And [c7] < [w2] And [c8] [w2] Then BigIf = "0.75" ElseIf [c5] = "Delta" And [c7] [v2] And [c7] < [w2] And [c8] [w2] Then BigIf = "0.5" ElseIf [c5] < "Non U/G" And [c5] < "Delta" And [c7] < [v2] And [c8] < [v2] Then BigIf = "1.5" ElseIf [c5] = "Delta" And [c7] < [v2] And [c8] < [v2] Then BigIf = "1" ElseIf [c5] < "Non U/G" And [c5] < "Delta" And [c7] < [v2] And [c8] = [w2] Then BigIf = "0.75" ElseIf [c5] = "Delta" And [c7] < [v2] And [c8] = [w2] Then BigIf = "0.5" ElseIf [c5] < "Non U/G" And [c5] < "Delta" And [c7] < [v2] And [c8] [v2] And [c8] <= [w2] Then BigIf = "0.75" ElseIf [c7] < [v2] And [c8] [v2] And [c8] [v2] And [c8] <= [w2] Then BigIf = "0.05" Else BigIf = "0" End If End Function Press Alt-F11 to return to the worksheet. In the cell where you want to call this function, type BigIf() If I haven't missed anything (whew) this should work. Check the code for obvious errors! James Corey wrote: =IF(AND(C5<"Non U/G",C5<"Delta",C7V2,C7<W2,C8W2),"0.75",IF(AND(C5 ="Delta",C7V2,C7<W2,C8W2),"0.5",IF(AND(C5<"N on U/G",C5<"Delta",C7<V2,C8<V2),"1.5",IF(AND(,C5="Delt a",C7<V2,C8<V2),"1",IF(AND(C5<"Non U/G",C5<"Delta",C7<V2,C8=W2),"0.75",IF(AND(C5="Del ta",C7<V2,C8=W2),"0.5",IF(AND(C5<"Non U/G",C5<"Delta",C7<V2,C8V2,C8<=W2),"0.75",IF(AND(C 7<V2,C8V2,C8<=W2),"0.5","0")))))))) I think i am at the end of the fomula limit. How to i enter this as code? -- Regards Corey |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to replace Formula
Corey, I did not make allowance for the lines of code wrapping in the
newsgroup. Each line should be continuous on one line of code between If (or ElseIf) and Then. James Zone wrote: Corey, Display the Excel worksheet. Press Alt-F11 to display the code editor. Select Insert from the menubar, then Module. Copy this code and paste it there. Function BigIf() If [c5] < "Non U/G" And [c5] < "Delta" And [c7] [v2] And [c7] < [w2] And [c8] [w2] Then BigIf = "0.75" ElseIf [c5] = "Delta" And [c7] [v2] And [c7] < [w2] And [c8] [w2] Then BigIf = "0.5" ElseIf [c5] < "Non U/G" And [c5] < "Delta" And [c7] < [v2] And [c8] < [v2] Then BigIf = "1.5" ElseIf [c5] = "Delta" And [c7] < [v2] And [c8] < [v2] Then BigIf = "1" ElseIf [c5] < "Non U/G" And [c5] < "Delta" And [c7] < [v2] And [c8] = [w2] Then BigIf = "0.75" ElseIf [c5] = "Delta" And [c7] < [v2] And [c8] = [w2] Then BigIf = "0.5" ElseIf [c5] < "Non U/G" And [c5] < "Delta" And [c7] < [v2] And [c8] [v2] And [c8] <= [w2] Then BigIf = "0.75" ElseIf [c7] < [v2] And [c8] [v2] And [c8] [v2] And [c8] <= [w2] Then BigIf = "0.05" Else BigIf = "0" End If End Function Press Alt-F11 to return to the worksheet. In the cell where you want to call this function, type BigIf() If I haven't missed anything (whew) this should work. Check the code for obvious errors! James Corey wrote: =IF(AND(C5<"Non U/G",C5<"Delta",C7V2,C7<W2,C8W2),"0.75",IF(AND(C5 ="Delta",C7V2,C7<W2,C8W2),"0.5",IF(AND(C5<"N on U/G",C5<"Delta",C7<V2,C8<V2),"1.5",IF(AND(,C5="Delt a",C7<V2,C8<V2),"1",IF(AND(C5<"Non U/G",C5<"Delta",C7<V2,C8=W2),"0.75",IF(AND(C5="Del ta",C7<V2,C8=W2),"0.5",IF(AND(C5<"Non U/G",C5<"Delta",C7<V2,C8V2,C8<=W2),"0.75",IF(AND(C 7<V2,C8V2,C8<=W2),"0.5","0")))))))) I think i am at the end of the fomula limit. How to i enter this as code? -- Regards Corey |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to replace Formula
Thanks for the reply James.
I managed to enter the code with no problems. However i get a result initially, but if i change one of the cell values in the worksheet the CODED CELL Value does not change. I changed the Code name and modified it a bit to suit as below: Function TravelOT() If [c5] = "Appin" Or [c5] = "Douglas" Or [c5] = "Metro" And [c7] < [v2] And [c8] <= [w2] Then TravelOT = "0.75" ElseIf [c5] = "Appin" Or [c5] = "Douglas" Or [c5] = "Metro" And [c7] < [v2] And [c8] [w2] Then TravelOT = "1.5" ElseIf [c5] = "Delta" And [c7] < [v2] And [c8] <= [w2] Then TravelOT = "0.5" ElseIf [c5] = "Delta" And [c7] < [v2] And [c8] [w2] Then TravelOT = "1" Else: TravelOT = "0" End If End Function Does it need a REFRESH step?? Is it possible to have MANY(50) Coded Formulas in Cells, in a single sheet? I tried another code as below but the cell would not allow me to plave it without an error"THAT NAME IS NOT VALID"??? Although the name i was using was in the same Module as the TravelOT code as below: Sub Normal_Time() If [c5] = "Non U/G" And ([c7] = [v2] And [c8] = [w2]) * "24" Then Normal_Time = "8" Else: Normal_Time = "0" ' =IF(AND(C5="Non U/G"),C8-C7,"0")*(24) <====== Original Formula used, but Now to be CODED End Sub Am i doing something wrong here? -- Regards Corey |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to replace Formula
Corey,
1. I should have cast the function as a string, since you are returning strings, as follows: Function TravelOT() As String If ([c5] = "Appin" Or [c5] = "Douglas" Or [c5] = "Metro") _ And [c7] < [v2] And [c8] <= [w2] Then TravelOT = "0.75" ElseIf ([c5] = "Appin" Or [c5] = "Douglas" Or [c5] = "Metro") _ And [c7] < [v2] And [c8] [w2] Then TravelOT = "1.5" ElseIf [c5] = "Delta" And [c7] < [v2] And [c8] <= [w2] Then TravelOT = "0.5" ElseIf [c5] = "Delta" And [c7] < [v2] And [c8] [w2] Then TravelOT = "1" Else TravelOT = "0" End If End Function I don't know why you want to return strings rather than numbers, but since you are, the function should be told it is to return a string. Also, if you're going to break a line of code into 2 lines, you must put a line continuation character at the break, as I've done here. Put in a space, an underscore, and then [Enter]. You'll need to delete the end-of-line (carriage-return) you've already got in there to put both sections of the line on one line, then put in the line continuation character. In addition, you should group the Or part of the condition with parentheses as I've done here to show you want (This Or This Or This) And This and This. You've set up Normal_Time as a Sub. Change Sub to Function. These are called user-defined functions, or UDFs. A sub cannot return a value, but a function can. And don't forget to put in your End If statement. You should be able to create as many UDFs as you want. Just be careful not to give them a name that conflicts with a built-in function or one that Excel could interpret as a cell address or range name. If you set up the Function properly, the sheet should recalculate when you change one of the cells it refers to. No "refresh" step is necessary. Just be sure calculation is set to automatic. (In the worksheet view, select Tools, then Options. On the Calculation tab, set Calculation to Automatic.) If you loaded the VBA help files, you should be able to get help with functions. Just go to the code editor and put the cursor anywhere in the word Function (do not highlight the whole word) and press F1. James Function Normal_Time() as String Corey wrote: Thanks for the reply James. I managed to enter the code with no problems. However i get a result initially, but if i change one of the cell values in the worksheet the CODED CELL Value does not change. I changed the Code name and modified it a bit to suit as below: Function TravelOT() If [c5] = "Appin" Or [c5] = "Douglas" Or [c5] = "Metro" And [c7] < [v2] And [c8] <= [w2] Then TravelOT = "0.75" ElseIf [c5] = "Appin" Or [c5] = "Douglas" Or [c5] = "Metro" And [c7] < [v2] And [c8] [w2] Then TravelOT = "1.5" ElseIf [c5] = "Delta" And [c7] < [v2] And [c8] <= [w2] Then TravelOT = "0.5" ElseIf [c5] = "Delta" And [c7] < [v2] And [c8] [w2] Then TravelOT = "1" Else: TravelOT = "0" End If End Function Does it need a REFRESH step?? Is it possible to have MANY(50) Coded Formulas in Cells, in a single sheet? I tried another code as below but the cell would not allow me to plave it without an error"THAT NAME IS NOT VALID"??? Although the name i was using was in the same Module as the TravelOT code as below: Sub Normal_Time() If [c5] = "Non U/G" And ([c7] = [v2] And [c8] = [w2]) * "24" Then Normal_Time = "8" Else: Normal_Time = "0" ' =IF(AND(C5="Non U/G"),C8-C7,"0")*(24) <====== Original Formula used, but Now to be CODED End Sub Am i doing something wrong here? -- Regards Corey |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Replace html code - formula too long | Excel Discussion (Misc queries) | |||
Need code to replace part of a range within a formula with a defined name | Excel Programming | |||
Replace code | Excel Programming | |||
Replace Symbol "Code(63)" | Excel Worksheet Functions | |||
Replace Code Programmatically | Excel Programming |