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
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?
