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

Hi, I've found out how to find out the row and column eg:

varRef = Target.Address
varRow = Me.Range(varRef).Row
varCol = Me.Range(varRef).Column
--------------------------------------------------------------------
The only thing I now need to find out is:
(1) how do I move down either 3 rows or 1 row (I need it relative) eg:

If row = 5 then move down 3 rows of the same column
If row = 7 then move down 1 row of the same column

(2) How do I capture this cell reference and move to it, so that I can enter
the total formula?

Many thanks
================================================== ========
"LuvMyTennis" wrote:

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

If rng.Row = 5 Then Set rng = rng.Offset(3,0)

If rng.Row = 7 Then Set rng = rng.Offset(1,0)

--
__________________________________
HTH

Bob

"LuvMyTennis" wrote in message
...
Hi, I've found out how to find out the row and column eg:

varRef = Target.Address
varRow = Me.Range(varRef).Row
varCol = Me.Range(varRef).Column
--------------------------------------------------------------------
The only thing I now need to find out is:
(1) how do I move down either 3 rows or 1 row (I need it relative) eg:

If row = 5 then move down 3 rows of the same column
If row = 7 then move down 1 row of the same column

(2) How do I capture this cell reference and move to it, so that I can
enter
the total formula?

Many thanks
================================================== ========
"LuvMyTennis" wrote:

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

Help!!! I can't get this code to work, what am I doing wrong??

varRef = Target.Address
varVal = Target.Value

varRow = Me.Range(varRef).Row
varCol = Me.Range(varRef).Column
---- this works above this line ----------
' Q.(1) am I supposed to declare rng as a Range ?
Dim rng As Range

' Q.(2) how do I populate the range, like this?
rng = Target

'Q.(3) Is this how I do this for row 5 (and then I'll do the same for the
other row)?
If rng.Row = 5 Then
Set rng = rng.Offset(3, 0)
End If

I've tried numerous combinations and Excel keeps generating error messages.
I wonder if it is in relation to declaring 'rng' and then populating it, and
then finally setting it to offset as above.
Please can anyone help with this issue?

"Bob Phillips" wrote:

If rng.Row = 5 Then Set rng = rng.Offset(3,0)

If rng.Row = 7 Then Set rng = rng.Offset(1,0)

--
__________________________________
HTH

Bob

"LuvMyTennis" wrote in message
...
Hi, I've found out how to find out the row and column eg:

varRef = Target.Address
varRow = Me.Range(varRef).Row
varCol = Me.Range(varRef).Column
--------------------------------------------------------------------
The only thing I now need to find out is:
(1) how do I move down either 3 rows or 1 row (I need it relative) eg:

If row = 5 then move down 3 rows of the same column
If row = 7 then move down 1 row of the same column

(2) How do I capture this cell reference and move to it, so that I can
enter
the total formula?

Many thanks
================================================== ========
"LuvMyTennis" wrote:

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