Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using Range and Offset to determine value of a field
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using Range and Offset to determine value of a field
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 "The Fool on the Hill" wrote: 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using Range and Offset to determine value of a field
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 "Mike H" wrote: 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 "The Fool on the Hill" wrote: 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using Range and Offset to determine value of a field
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 The Fool on the Hill wrote: 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 "Mike H" wrote: 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 "The Fool on the Hill" wrote: 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. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Determine if a value in a range is close to the value of another c | Excel Worksheet Functions | |||
Determine if a value in a range is close to the value of another c | Excel Worksheet Functions | |||
Determine if Cell Address is within a Range | Excel Worksheet Functions | |||
Determine whether a value is in a range of cells | Excel Discussion (Misc queries) | |||
How do you determine if a field is blank in a logical expression. | Excel Discussion (Misc queries) |