Using Range and Offset to determine value of a field

[Guest]

Dear excel(lent) users,

I have learned from a previous thread, that it is possible to enter
something in one field and automatically have something (like a date entered
in another field) :

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
If Target.Count > 1 Then Exit Sub
Set rng = Range("B:B") '< Could be B:B for an entire column
If Intersect(Target, rng) Is Nothing Then Exit Sub
If Target <> "" Then Target.Offset(0, 6) = Now()
If Target = "" Then Target.Offset(0, 6) = ""
End Sub

Now I also want to know if it is possible to make sums where I have 'now()'.

For instance something like
If Target <> "" Then Target.Offset(0, -1) = Target.Offset(-1,-1)+1

So in the above case if I fill in something in column B a date will be
entered in Column H, with my change I would add a number in column A to the
one in the line above, similar to (IF B2<>"";A1+1;"").

Offcourse I could use the simple if statement, but people tend to overwrite
it without realising.

Thanks for helping me in this matter.

 

[Guest]

For instance something like

If Target <> "" Then Target.Offset(0, -1) = Target.Offset(-1,-1)+1

I'm not sure what you mean but the line above will work (mostly) OK so what
is the issue. Where it could fail is if you type something in B1 and it tries
to reference the offset

Mike

 

[Guest]

Hi Mike,

Thanks for your swift reply. I have come up with the following which works:
If Target <> "" Then Target.Offset(0, -1) = Target.Offset(-1, -1) + 1

Kind regards,

Jay

 

[Dave Peterson]

You may want to stop the worksheet_change event from running a second time when
you make the change to the other column:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim rng As Range

If Target.Count > 1 Then Exit Sub

Set rng = Me.Range("B:B")
If Intersect(Target, rng) Is Nothing Then Exit Sub

On Error GoTo ErrHandler:
Application.EnableEvents = False
If Target.Value <> "" Then
If IsNumeric(Target.Offset(0, 6).Value) Then
Target.Offset(0, 6).Value = Target.Offset(0, 6).Value + 1
Else
Beep
End If
Else
Target.Offset(0, 6).Value = ""
End If

ErrHandler:
Application.EnableEvents = True
End Sub