Formula disappears when values are entered into referring cell

[twind]

I have a company time entry template in Excel 2013, which is macro-enabled. In Cell A1 I manually enter my first task start time. All cells A2 and below automatically equal the end time of the previous task form the line above, which I manually enter into column B. In all cells of column C, I have a simple formula of B minus A, to determine the number of hours for each task.

My issue is that, as soon as I enter a value into B1 (or any B cell), the formula in C1 (or the adjacent column C cell) disappears (cell and formula bar are completely blank). So I typically copy the formula from cells below C1 back into it, and the formula then remains in tact, unless I again update the value in the adjacent B cell.

	A	B	C
1	[manual entry]	[manual entry]	=B1-A1 (this formula disappears once I enter a value into B1)
2	=B1	[manual entry]	=B2-A2 (this formula disappears once I enter a value into B2)
3	=B2	[manual entry]	=B3-A3 (this formula disappears once I enter a value into B3)

<tbody>
</tbody>

I'm just not sure why this formula would simply disappear, but I suspect that it has to do with the fact that this is a macro-enabled workbook. If it helps to know what the macros are, the first macro consolidates all entries for each job function onto the second tab (so sums up the number of hours worked for each function and concatenates all the "notes" from the notes column). The second macro is on the second tab, and copies the consolidated entries into a text file format to upload into our time entry software. I have only basic knowledge of macros, so I'm not sure how these would be connected, but I believe the company that built the time entry software also built this template (without the formulas - I added those in myself).

Has anyone else encountered an issue like this? I found a thread with a similar issue that was also unsolved (from July 2013), but don't know if that's the same issue I'm having. Thank you in advance for looking into this and let me know if you need any further info!

 

[HalfAce]

as soon as I enter a value into B1 (or any B cell), the formula in C1 (or the adjacent column C cell) disappears

I would check to see if there is any code in the worksheet code module (for instance a Worksheet_Change routine) that may be telling it to do something like that.

What do you see if you right click the sheet tab and choose View code?

 

[twind]

Hmm it looks like there is a "Change" procedure associated with the worksheet. Have to admit that I've build simple macros before but the code is a little above my head, but it appears there is some sort of If this cell = something then this cell=0 type language, though not exactly. Would it help for me to copy and paste the sheet code into this thread? I'm not really sure what I'm looking for, sorry.

 

[HalfAce]

Yeah, I suspect that's what's deleting your value(s). If you can copy that code onto hear we can take a look at it & make sure of what it's doing & see if it can be removed without destroying the rest of the world.

 

[twind]

Thank you for offering to look into this. Please note for simplicity's sake I listed the columns as A, B, and C, but the columns I was actually referring to are B (start time), C (end time), and D (total hours) respectively. Column A is the date, not tied to any formula. After column D, the columns are as follows if this helps: E (Client), F (Division), G (Product), H (Job #), I (Billing component [1 or 2]), J (Job Function), and K (Notes). Then Column J values depend on dropdowns refering either to column L (no client) or column K (client). Here is the code for this sheet and thanks again!


Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range


' The variable KeyCells contains the cells that will
' cause an alert when they are changed.
Set KeyCells = Range("B10:D409")

If Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing Then


' Display a message when one of the designated cells has been
' changed.
' Place your code here.
'MsgBox "Cell " & Target.Address & " has changed."



If (Target.Column = 2 Or Target.Column = 3) And Target.Value <> "" Then
Cells(Target.Row, 4) = ""
If (Cells(Target.Row, 2) = "" Or Cells(Target.Row, 2) = "") Then
Else
'calculate the minutes now!
Dim A As String
Dim B As String
Dim X As String
Dim Y As String
Dim Stime As String
Dim Etime As String
Dim Slen As Integer
Dim Elen As Integer
Dim Shour As Integer
Dim Smin As Integer
Dim Ehour As Integer
Dim Emin As Integer
Dim t As Integer
Dim u As Integer
Dim total As Integer


A = Cells(Target.Row, 2)
B = Cells(Target.Row, 3)

X = UCase(Right(A, 1))
Y = UCase(Right(B, 1))

Slen = Len(A) - 1
Elen = Len(B) - 1



If (X <> "A" And X <> "P") Or (Y <> "A" And Y <> "P") Or Slen = 0 Or Elen = 0 Then



Else
Stime = Left(A, Slen)
Etime = Left(B, Elen)


t = InStr(Stime, ":") - 1
u = InStr(Etime, ":") - 1
If u = 0 Or t = 0 Then
Else
Shour = Left(Stime, t)
Ehour = Left(Etime, u)
Smin = Right(Stime, Len(Stime) - t - 1)
Emin = Right(Etime, Len(Etime) - u - 1)


If Shour = 12 Then

If X = "A" Then
Shour = 0
End If
Else
If X = "P" Then
Shour = Shour + 12
End If
End If

If Ehour = 12 Then

If Y = "A" Then
Ehour = 0
End If
Else
If Y = "P" Then
Ehour = Ehour + 12
End If
End If




End If

total = (Ehour - Shour) * 60
total = total + Emin - Smin



Cells(Target.Row, 4) = total / 60

End If


End If
End If




End If
End Sub

 

[HalfAce]

I guess it's going to take a bit of going through this.
It's written somewhat cryptically and (at first glance) doesn't seem to be working as designed.
(Hard to tell yet with all those If statements that are open down to the end and nothing is indented to make things easier to find.)

What I notice right off though, are the lines:

If (Target.Column = 2 Or Target.Column = 3) And Target.Value <> "" Then
        Cells(Target.Row, 4) = ""

which is saying "If the target column is B or the target column is C then make column D of that row be blank". (Which is not happening for me when I add or change a value in columns B or C.)

Another line that has me wondering is this:

If (Cells(Target.Row, 2) = "" Or Cells(Target.Row, 2) = "") Then

which is saying "If column B of the target row is blank or if column B of the target row is blank then..."

Give me a bit to re-arrange the code and then try & wrap my head around what the author intended it to do and see where we stand from there.

 

[twind]

Thanks! Feel free to take your time as my issue isn't time-sensitive (just a bit frustrating).