Niek - It worked.

I think there was something wrong with the module in the workbook. I double
checked the entire function and it was exactly like Toppers. I then ran the
ISTEXT() function on all of the cells and everything came back "False",
However I was still getting the #VALUE! error. I then created a new test
workbook and entered Toppers function exactly as he gave it to me and the
function worked perfect. I went back to my competition workbook and
removed/deleted the module and inserted a new one. I then copied the function
from the test book to the competition book and changed the cell references
that needed to be changed and it work just like it was suppose to.
I can't thank you guys enough for taking the time to help me out on this I
know there are a lot of other people that need help and you took the time to
work with me.

Niek you are right also about Excel not being able to calculate the function
when I change an value in the cells (M20,M21,M22,M23,AT5) however this
function is going to be inserted into 444 cells on 22 worksheets.
Do you have any suggestions how I can have this function set up so that when
I change a value in the INDEX it will change the values in the function I
don't know if that is possible, but that is why I was trying to automate this
in the first place because there are so many slots that times would have to
be entered into. Not that I will be using all 22 worksheets at a time but I
will be using at least 12 to 15 and there are 22 cells in each worksheet that
need to have times entered in them.

If you have a way that I can get the above cells to change in the function
then I will be finished with the project.
This will probably be the last time we will be communicating on this subject
so again I want to thank you and Toppers so very much for your help. I never
could have completed this without it.

"Niek Otten" wrote:

There is one more thing I'd like to point out: you refer to cells in a worksheet directly from within the function (M20:M23, AT5).
That is not a good practice. You should include all input to the function in the argument list, just like Time1 and Time2. If you
don't, Excel will not be aware of the need for recalculation if you change any of those cells (how could it?). Very tricky!

--
Kind regards,

Niek Otten


"Niek Otten" wrote in message ...
|I tested Topper's code too, and it give results, not an error.
|
| 1. Did you copy and paste Topper's code, not just retype it? A typo can be easily overseen
| 2. Enter these formulas in empty cells on the INDEX sheet:
| =ISTEXT(M20)
| =ISTEXT(M21)
| =ISTEXT(M22)
| =ISTEXT(M23)
| =ISTEXT(AT5)
|
| They should all give FALSE as a result.
|
| I'm sure we will get this right!
| --
| Kind regards,
|
| Niek Otten
|
| "Bill_De" wrote in message ...
|| Thanks for the quick response. However I am very new at this Excel stuff and
|| am just learning about functions and writing code and all that stuff and I
|| have to say you have lost me some what.
|| I would like to reply to your response;
|| First I set the format when I created the document so the formating wasn't
|| done afterward it was done before I created the custom function. As far as
|| the times go they are not copied from another application the are
|| transferred/copied from another worksheet in the same workbook. I have a page
|| titled Index where I can enter all of the begining times and intervals and
|| then those time are sent to the first time slot of each game page.
|| Second there is a space between the numbers and the AM/PM but this is done
|| in the formating not by me. If I type in 7:30AM when I tab to another cell
|| the entry changes to 7:30 AM. Also if I overwrite the formating and enter
|| 7:30AM then all of the other entries on the work sheet change to #Value!, and
|| when I change the entry back to 7:30 AM the formula works correct.
|| Next you lost me with the ISTEXT() function. Where do I insert the function
|| and what arguments do I put in the ().
|| You say that I should check to see that there are no spaces in the cell or
|| non-printable characters. Each cell has a formula in it for example the first
|| cell that I mentioned M3 when I click on the cell the formula is: =INDEX!F4
|| and what is viewed is 7:30 AM. I don't know if this is what you are talking
|| about when you say non-printable characters.
||
|| When I run the LEN function on cell M3 I get a result of "6" for the entry
|| 7:30 AM. The result is 0.3125. When I run the LEN function on Cell M11 I get
|| a result of "7" for the entry 8:15 AM. The result is 0.34375.
||
|| I got the numbers 0.3125 and 0.34375 when I ran the TRIM() function. I
|| really am not sure what you are talking about with the CHAR(160) and replace
|| with"". Where do I enter this function when I entered it into Cell M3 the
|| result was: =INDEX!F4+CHAR(160).
||
|| Also when I used the ISTEXT(), TRIM() functions I ran them in empty cells I
|| don't know if I was suppose to run them in the cell where the problem is
|| M3,M11.
||
|| If you could help clarify this for me that would be great. Toppers said that
|| he ran this function and he had no problem and I don't know how he did that .
||
|| Please help
|| Thanks
|| Bill_De
||
||
||
||
|| "Niek Otten" wrote:
||
|| Even though tour cells are formatted as time and look like time, they are actually text. Formatting afterwards doesn't help.
| You
|| can test with the ISTEXT() function. Right? You probably copied the times from another application.
|| Check that there are no spaces or other non-printable characters in the cell. Use the LEN function to count the # of
| characters
|| and compare that with what you see. Use TRIM() or CLEAN() functions to clean the data and Find and replace any CHAR(160)
| character
|| with "" (nothing).
|| Re-enter your data (F2, ENTER) (try that first, anyway; maybe you're lucky).
||
|| --
|| Kind regards,
||
|| Niek Otten
||
|| "Bill_De" wrote in message ...
|| | Hi Toppers
|| | I rewrote the function just as you wrote it and I still get the #Value!
|| | error message. When I clicked on the trace error the blue line went to the
|| | two cells in my main worksheet (M3,M11) with the message "A value in the
|| | formula is of the wrong data type". I checked the format for the two cells
|| | (M3,M11) and they are set at Custom H:mm AM/PM. In the error box it showed
|| | Worksheet DE2 M3= worng data type and M11= wrong data type even though both
|| | are set for time.
|| | I also tried some addition to see and I added M3 (time1, 7:30 AM) plus AT5
|| | (0:20 cell located in worksheet DE2 named Gametm) plus Index!M20 (0:15 named
|| | Gameint) the result on enter was 8:05 AM.
|| |
|| | I checked the possible causes in help and I can't figure out what I am doing
|| | wrong based on what help showed. Here are the possible causes:
|| | -Entering text when the formula requires a number or a logical value, such
|| | as true or false.
|| | -entering or editing an array formula and then pressing enter.
|| | -Entering a cell reference, a formula, or a function as an array constant.
|| | -Supplying a range to an operator or a function that requires a single
|| | value, not a range.
|| | -Using a matrix that is not valid in one of the matrix worksheet functions.
|| | -Running a macro that enters a function that returns #value!
|| |
|| | Any suggestions on which way to go next. I just don't know which one of the
|| | possible problems it could be. By the way sorry to be such a bother,
|| | hopefully you can help me solve this problem and I will be out of your hair.
|| |
|| | Thanks again for all the help
|| | Bill_De
|| |
|| | "Toppers" wrote:
|| |
|| | Hi,
|| |
|| | TRY ....
|| |
|| | Function Evtime(Time1, Time2)
|| |
|| |
|| | Dim gameint As Variant
|| | Dim Lunchbk As Variant
|| | Dim Addtm As Variant
|| | Dim Gametm As Variant
|| | Dim Pmstart As Variant
|| | Dim Result1 As Variant
|| | With Worksheets("Index")
|| | gameint = .Range("M20") '(0:05 cell format custom [h]:mm)
|| | Lunchbk = .Range("M21") ' (11:00 AM cell format custom h:mm AM/PM)
|| | Addtm = .Range("M22") '(0:20 cell format custom [h]:mm)
|| | Pmstart = .Range("M23") '(1:00 PM cell format custom h:mm AM/PM)
|| | Gametm = .Range("AT5") ' (0:20 cell format custom [h]:mm)
|| | End With
|| | Exp1 = (Time1 + Gametm + gameint)
|| | Exp2 = (Lunchbk - Gametm)
|| | Exp3 = (Time2 + Gametm + gameint)
|| | Exp4 = (Time1 + Gametm + gameint + Addtm)
|| |
|| | If Exp1 < Exp2 Then Result1 = Exp1 Else: Result1 = Pmstart
|| | If Exp1 Exp2 Then Result1 = Pmstart Else: Result1 = Exp1
|| | If Exp1 Exp3 Then Evtime = Result1 Else: Evtime = Exp4
|| | End Function
|| |
|| | "Bill_De" wrote:
|| |
|| | I am fairly new at Excel and Access doing formulas and I
|| | have a problem which after an hour I am stuck and Help doesn't give
|| | enough information for me to work through this.
|| |
|| | I have created an Excel document for scoring tournements. The entire book is
|| | automated and I am now trying to automate the times for the events using a
|| | custom function to change the start times.
|| |
|| | I finished the custom function and inserted it into a cell I then got the
|| | function arguments box which asks for entries for Time1 and Time2. I enter 2
|| | cell locations i.e. M3, M15 and when I click ok I get the following: #Value!
|| |
|| | I have checkd the entire worksheet where I have time entries and all of the
|| | cells are formated to "time". I have also taken cells with time entries ie
|| | cell M3 is 1:00 PM, and Cell AT5 is 0:20 and when I add the two cells I get a
|| | result of 1:20 PM and when I subtract the cells I get a result of 12:50 PM.
|| |
|| | I cannot figure out why I am getting the #Value error message as I check
|| | with help and all of the entries are in the same formate.
|| |
|| | Here is the entire function with explinations on cell formate and entries I
|| | hope you can give me some advice as to what I need to change to get this
|| | function to work I am totally lost on this one.
|| |
|| | Function Evtime(Time1, Time2) (Times in cells ie M3,M15 cell formats are
|| | custom h:mn AM/PM
|| |
|| | Dim gameint as Variant
|| | Dim Lunchbk as Variant
|| | Dim Addtm as Variant
|| | Dim Gametm as Variant
|| | Dim Pmstart as Variant
|| | Dim Result1 as Variant
|| |
|| | Gameint=Index!M20 (0:05 cell format custom [h]:mm)
|| | Lunchbk=Index!M21 (11:00 AM cell format custom h:mm AM/PM)
|| | Addtm=Index!M22 (0:20 cell format custom [h]:mm)
|| | Pmstart=Index!M23 (1:00 PM cell format custom h:mm AM/PM)
|| | Gametm=AT5 (0:20 cell format custom [h]:mm)
|| | Exp1=(Time1+Gametm+Gameint)
|| | Exp2=(Lunchbk-Gametm)
|| | Exp3=(Time2+Gametm+Gameint)
|| | Exp4=(Time1+Gametm+Gameint+Addtm)
|| |
|| | If Exp1< Exp2 Then Result1=Exp1 Else: Result1= Pmstart
|| | If Exp1 Exp2 Then Result1=Pmstart Else: Result1=Exp1
|| | If Exp1 Exp3 Then Evtime=Result1 Else: Evtime= Exp4
|| | End Function
|| |
|| | I hope you can help me as I am getting close to where I will be needing this
|| | for some tournements.
|| | Thanks again
|| | Bill_De
|| |
||
||
||
|
|