Code to replace Formula

[Corey]

=IF(AND(C5<>"Non
U/G",C5<>"Delta",C7>V2,C7<W2,C8>W2),"0.75",IF(AND(C5="Delta",C7>V2,C7<W2,C8>W2),"0.5",IF(AND(C5<>"Non
U/G",C5<>"Delta",C7<V2,C8<V2),"1.5",IF(AND(,C5="Delta",C7<V2,C8<V2),"1",IF(AND(C5<>"Non
U/G",C5<>"Delta",C7<V2,C8>=W2),"0.75",IF(AND(C5="Delta",C7<V2,C8>=W2),"0.5",IF(AND(C5<>"Non
U/G",C5<>"Delta",C7<V2,C8>V2,C8<=W2),"0.75",IF(AND(C7<V2,C8>V2,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

[Zone]

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",C7>V2,C7<W2,C8>W2),"0.75",IF(AND(C5="Delta",C7>V2,C7<W2,C8>W2),"0.5",IF(AND(C5<>"Non
> U/G",C5<>"Delta",C7<V2,C8<V2),"1.5",IF(AND(,C5="Delta",C7<V2,C8<V2),"1",IF(AND(C5<>"Non
> U/G",C5<>"Delta",C7<V2,C8>=W2),"0.75",IF(AND(C5="Delta",C7<V2,C8>=W2),"0.5",IF(AND(C5<>"Non
> U/G",C5<>"Delta",C7<V2,C8>V2,C8<=W2),"0.75",IF(AND(C7<V2,C8>V2,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

[Zone]

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",C7>V2,C7<W2,C8>W2),"0.75",IF(AND(C5="Delta",C7>V2,C7<W2,C8>W2),"0.5",IF(AND(C5<>"Non
> > U/G",C5<>"Delta",C7<V2,C8<V2),"1.5",IF(AND(,C5="Delta",C7<V2,C8<V2),"1",IF(AND(C5<>"Non
> > U/G",C5<>"Delta",C7<V2,C8>=W2),"0.75",IF(AND(C5="Delta",C7<V2,C8>=W2),"0.5",IF(AND(C5<>"Non
> > U/G",C5<>"Delta",C7<V2,C8>V2,C8<=W2),"0.75",IF(AND(C7<V2,C8>V2,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

[Corey]

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

[Zone]

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