How do I move to a specified row and insert a value?

[LuvMyTennis]

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

 

[LuvMyTennis]

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

 

[Bob Phillips]

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

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

 

[CJ]

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?