Hi, For the life of me I cannot solve this one despite searching for it.

I am trying to create a Timesheet which spans two fortnights (Monday to
Friday). Also it will have:
Morning:
* a start & end Time x 2 (to record if the user clocked on and off twice)
Afternoon:
* a start & end time x 2 (to record if the user clocked on and off twice)

I can capture the address(string) of where the user entered the data into
the cell, but then based on that row(which will be either row 5 or row 7) I
then need to move down to row 8, of the same column, and then enter the
result (eg endTime - startTime). Can someone please help me.
----------------------------------------------------------------------------
It all works fine so far, I just don't know how to:
If target.address = row 5 then move down 3 rows (to row 8) of the same column
If target.address = row 7 then move down 1 row (to row 8) of the same column.
----------------------------------------------------------------------------
* I have read about INDIRECT (which converts a text string into a cell
reference) but I can't get that to work!

* I've also tried using SPLIT to split the row from the column but I cannot
get that to work either!

* I don't want to use either LEFT MID or RIGHT to extract the cell
reference as I want to learn how to separate the rows from the columns (eg A6
or AB66 etc.)


So far my code is:
================================================== ========

Private Sub Worksheet_Change(ByVal Target As Range)
Dim varInputReference As Variant
Dim varInputValue As Variant
Dim varTimeAnswer As Variant
Dim arrayTimeStart As Variant
Dim arrayTimeEnd As Variant

' this is an array (for a Timesheet) which spans two fortnights Monday to
Friday
arrayTimeStart = Array("$C$4", "$D$4", "$E$4", "$F$4", "$G$4", "$J$4",
"$K$4", "$L$4", "$M$4", "$N$4", _
"$C$6", "$D$6", "$E$6", "$F$6", "$G$6", "$J$6", "$K$6", "$L$6", "$M$6",
"$N$6", _
"$C$9", "$D$9", "$E$9", "$F$9", "$G$9", "$J$9", "$K$9", "$L$9", "$M$9",
"$N$9", _
"$C$11", "$D$11", "$E$11", "$F$11", "$G$11", "$J$11", "$K$11", "$L$11",
"$M$11", "$N$11")

arrayTimeEnd = Array("$C$5", "$D$5", "$E$5", "$F$5", "$G$5", "$J$5", "$K$5",
"$L$5", "$M$5", "$N$5", _
"$C$7", "$D$7", "$E$7", "$F$7", "$G$7", "$J$7", "$K$7", "$L$7", "$M$7",
"$N$7", _
"$C$10", "$D$10", "$E$10", "$F$10", "$G$10", "$J$10", "$K$10", "$L$10",
"$M$10", "$N$10", _
"$C$12", "$D$12", "$E$12", "$F$12", "$G$12", "$J$12", "$K$12", "$L$12",
"$M$12", "$N$12")

' get the cell's reference & text value.
varInputReference = Target.Address
varInputValue = Target.Text

' check if time is end time
If varInputValue < "" Then
For i = 0 To 39
If varInputReference = arrayTimeEnd(i) Then
Call TimeEnd(varInputReference, varInputValue, Target)
End If
Next
End If
End Sub

Private Sub calcSubTotal(ByVal Target As Range, varStart1am As Variant,
varEnd1am As Variant, Optional varStart2am As Variant = "", Optional
varEnd2am As Variant = "")
Dim varStart As Variant
Dim varEnd As Variant
Dim varTotal As Variant
Dim varRef As Variant

varStart = TimeValue(varStart1am)
varEnd = TimeValue(varEnd1am)
varTotal = varEnd - varStart

If varStart2am < "" And varEnd2am < "" Then
varStart = TimeValue(varStart2am)
varEnd = TimeValue(varEnd2am)
varTotal = varTotal + (varEnd - varStart)
End If

varTotal = Format(varTotal, "h:mm")

varRef = Target.Address

================================================== ========
* for instance, start times commence on C4 and go across the row &
end times commence on C5 and go across the row.
Therefore, if I just entered an end time (say 11:00 AM) into C5, the above
varRef comes up as $C$5 - which is correct. I just can't move on from here
(based on the above issues).

Any help would be apreciated.
Crystal