ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   refer to cell relative to range (https://www.excelbanter.com/excel-discussion-misc-queries/163536-refer-cell-relative-range.html)

Horatio J. Bilge, Jr.
refer to cell relative to range
 
I have a worksheet shere I periodically update the data in a range. The
column to the right of the range displays the date when I last updated that
row of data (see code below). I would like to change the code so it will do
the same thing with whatever named range I include.

This is what I have:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim WS As Worksheet
Set WS = Worksheets("Sheet1")
If Not (Application.Intersect(Target, Range("Skill1")) Is Nothing) Then
With WS.Cells(Target.Row, 13)
.Value = Now
.NumberFormat = "mm/dd/yyyy"
End With
Exit Sub
End If
End Sub

This is what I tried. I thought this way I could easily add additional
ranges as ElseIf statements. I get an error, "Argument is not optional."
Private Sub Worksheet_Change(ByVal Target As Range)
Dim WS As Worksheet
Set WS = Worksheets("Sheet1")
If Not (Application.Intersect(Target, Range("Skill1")) Is Nothing) Then
GoTo Revise_Date
ElseIf Not (Application.Intersect(Target, Range("Skill2")) Is Nothing) Then
GoTo Revise_Date
Else: Exit Sub
End If

Revise_Date:
With WS.Cells(Target.Row, Target.Range.Offset(0,1))
.Value = Now
.NumberFormat = "mm/dd/yyyy"
End With
Exit Sub
End Sub

Gary''s Student
refer to cell relative to range
 
just create the combined range with Union()

set united=Union(Range("Skill1"),Range("Skill2"))
If Not (Application.Intersect(Target, united) Is Nothing) Then
--
Gary''s Student - gsnu200751


"Horatio J. Bilge, Jr." wrote:

I have a worksheet shere I periodically update the data in a range. The
column to the right of the range displays the date when I last updated that
row of data (see code below). I would like to change the code so it will do
the same thing with whatever named range I include.

This is what I have:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim WS As Worksheet
Set WS = Worksheets("Sheet1")
If Not (Application.Intersect(Target, Range("Skill1")) Is Nothing) Then
With WS.Cells(Target.Row, 13)
.Value = Now
.NumberFormat = "mm/dd/yyyy"
End With
Exit Sub
End If
End Sub

This is what I tried. I thought this way I could easily add additional
ranges as ElseIf statements. I get an error, "Argument is not optional."
Private Sub Worksheet_Change(ByVal Target As Range)
Dim WS As Worksheet
Set WS = Worksheets("Sheet1")
If Not (Application.Intersect(Target, Range("Skill1")) Is Nothing) Then
GoTo Revise_Date
ElseIf Not (Application.Intersect(Target, Range("Skill2")) Is Nothing) Then
GoTo Revise_Date
Else: Exit Sub
End If

Revise_Date:
With WS.Cells(Target.Row, Target.Range.Offset(0,1))
.Value = Now
.NumberFormat = "mm/dd/yyyy"
End With
Exit Sub
End Sub

Gord Dibben
refer to cell relative to range
 
Private Sub Worksheet_Change(ByVal Target As Range)
Dim WS As Worksheet
Set WS = Worksheets("Sheet1")
If Not (Application.Intersect(Target, _
Range("skill1,skill2,skill3")) Is Nothing) Then
On Error GoTo endit
Application.EnableEvents = False
With Target.Offset(0, 1)
.Value = Now
.NumberFormat = "mm/dd/yyyy"
End With
End If
endit:
Application.EnableEvents = True
End Sub

The first two lines are not needed IMO

Dim WS As Worksheet
Set WS = Worksheets("Sheet1")


Gord Dibben MS Excel MVP


On Thu, 25 Oct 2007 12:38:02 -0700, Horatio J. Bilge, Jr.
wrote:

I have a worksheet shere I periodically update the data in a range. The
column to the right of the range displays the date when I last updated that
row of data (see code below). I would like to change the code so it will do
the same thing with whatever named range I include.

This is what I have:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim WS As Worksheet
Set WS = Worksheets("Sheet1")
If Not (Application.Intersect(Target, Range("Skill1")) Is Nothing) Then
With WS.Cells(Target.Row, 13)
.Value = Now
.NumberFormat = "mm/dd/yyyy"
End With
Exit Sub
End If
End Sub

This is what I tried. I thought this way I could easily add additional
ranges as ElseIf statements. I get an error, "Argument is not optional."
Private Sub Worksheet_Change(ByVal Target As Range)
Dim WS As Worksheet
Set WS = Worksheets("Sheet1")
If Not (Application.Intersect(Target, Range("Skill1")) Is Nothing) Then
GoTo Revise_Date
ElseIf Not (Application.Intersect(Target, Range("Skill2")) Is Nothing) Then
GoTo Revise_Date
Else: Exit Sub
End If

Revise_Date:
With WS.Cells(Target.Row, Target.Range.Offset(0,1))
.Value = Now
.NumberFormat = "mm/dd/yyyy"
End With
Exit Sub
End Sub

Horatio J. Bilge, Jr.
refer to cell relative to range
 
This places the date one cell to the right of the cell I edit. I want the
date to go one cell to the right of the range. For example, for
range("Skill1")="A1:E20" if I edit A2 (or B2, C2, etc.), the date should go
in F2. And for "Skill2"="G1:Y20" if I edit G2, the date should go in Z2.

"Gord Dibben" wrote:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim WS As Worksheet
Set WS = Worksheets("Sheet1")
If Not (Application.Intersect(Target, _
Range("skill1,skill2,skill3")) Is Nothing) Then
On Error GoTo endit
Application.EnableEvents = False
With Target.Offset(0, 1)
.Value = Now
.NumberFormat = "mm/dd/yyyy"
End With
End If
endit:
Application.EnableEvents = True
End Sub

The first two lines are not needed IMO

Dim WS As Worksheet
Set WS = Worksheets("Sheet1")


Gord Dibben MS Excel MVP


On Thu, 25 Oct 2007 12:38:02 -0700, Horatio J. Bilge, Jr.
wrote:

I have a worksheet shere I periodically update the data in a range. The
column to the right of the range displays the date when I last updated that
row of data (see code below). I would like to change the code so it will do
the same thing with whatever named range I include.

This is what I have:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim WS As Worksheet
Set WS = Worksheets("Sheet1")
If Not (Application.Intersect(Target, Range("Skill1")) Is Nothing) Then
With WS.Cells(Target.Row, 13)
.Value = Now
.NumberFormat = "mm/dd/yyyy"
End With
Exit Sub
End If
End Sub

This is what I tried. I thought this way I could easily add additional
ranges as ElseIf statements. I get an error, "Argument is not optional."
Private Sub Worksheet_Change(ByVal Target As Range)
Dim WS As Worksheet
Set WS = Worksheets("Sheet1")
If Not (Application.Intersect(Target, Range("Skill1")) Is Nothing) Then
GoTo Revise_Date
ElseIf Not (Application.Intersect(Target, Range("Skill2")) Is Nothing) Then
GoTo Revise_Date
Else: Exit Sub
End If

Revise_Date:
With WS.Cells(Target.Row, Target.Range.Offset(0,1))
.Value = Now
.NumberFormat = "mm/dd/yyyy"
End With
Exit Sub
End Sub

Gord Dibben
refer to cell relative to range
 
Getting a bit beyond my skills.

I'll work on it but probably do what you would do and that is go through VB
help.

Hopefully someone can jump in before we're forced to do that<g


Gord

On Thu, 25 Oct 2007 13:41:01 -0700, Horatio J. Bilge, Jr.
wrote:

This places the date one cell to the right of the cell I edit. I want the
date to go one cell to the right of the range. For example, for
range("Skill1")="A1:E20" if I edit A2 (or B2, C2, etc.), the date should go
in F2. And for "Skill2"="G1:Y20" if I edit G2, the date should go in Z2.

"Gord Dibben" wrote:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim WS As Worksheet
Set WS = Worksheets("Sheet1")
If Not (Application.Intersect(Target, _
Range("skill1,skill2,skill3")) Is Nothing) Then
On Error GoTo endit
Application.EnableEvents = False
With Target.Offset(0, 1)
.Value = Now
.NumberFormat = "mm/dd/yyyy"
End With
End If
endit:
Application.EnableEvents = True
End Sub

The first two lines are not needed IMO

Dim WS As Worksheet
Set WS = Worksheets("Sheet1")


Gord Dibben MS Excel MVP


On Thu, 25 Oct 2007 12:38:02 -0700, Horatio J. Bilge, Jr.
wrote:

I have a worksheet shere I periodically update the data in a range. The
column to the right of the range displays the date when I last updated that
row of data (see code below). I would like to change the code so it will do
the same thing with whatever named range I include.

This is what I have:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim WS As Worksheet
Set WS = Worksheets("Sheet1")
If Not (Application.Intersect(Target, Range("Skill1")) Is Nothing) Then
With WS.Cells(Target.Row, 13)
.Value = Now
.NumberFormat = "mm/dd/yyyy"
End With
Exit Sub
End If
End Sub

This is what I tried. I thought this way I could easily add additional
ranges as ElseIf statements. I get an error, "Argument is not optional."
Private Sub Worksheet_Change(ByVal Target As Range)
Dim WS As Worksheet
Set WS = Worksheets("Sheet1")
If Not (Application.Intersect(Target, Range("Skill1")) Is Nothing) Then
GoTo Revise_Date
ElseIf Not (Application.Intersect(Target, Range("Skill2")) Is Nothing) Then
GoTo Revise_Date
Else: Exit Sub
End If

Revise_Date:
With WS.Cells(Target.Row, Target.Range.Offset(0,1))
.Value = Now
.NumberFormat = "mm/dd/yyyy"
End With
Exit Sub
End Sub

Dave Peterson
refer to cell relative to range
 
Maybe...

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCol As Long

'only one cell at a time
If Target.Cells.Count 1 Then Exit Sub

If Not (Application.Intersect(Target, Me.Range("skill1")) Is Nothing) Then
'in Skill1
With Me.Range("skill1")
myCol = .Columns(.Columns.Count).Column + 1
End With
ElseIf Not (Application.Intersect(Target, _
Me.Range("skill2")) Is Nothing) Then
'in Skill2
With Me.Range("skill2")
myCol = .Columns(.Columns.Count).Column + 1
End With
ElseIf Not (Application.Intersect(Target, _
Me.Range("skill3")) Is Nothing) Then
'in Skill3
With Me.Range("skill3")
myCol = .Columns(.Columns.Count).Column + 1
End With
Else
myCol = 0
End If

If myCol = 0 Then Exit Sub

On Error GoTo endit
Application.EnableEvents = False
With Me.Cells(Target.Row, myCol)
.Value = Now
.NumberFormat = "mm/dd/yyyy"
End With

endit:
Application.EnableEvents = True
End Sub


Horatio J. Bilge, Jr. wrote:

This places the date one cell to the right of the cell I edit. I want the
date to go one cell to the right of the range. For example, for
range("Skill1")="A1:E20" if I edit A2 (or B2, C2, etc.), the date should go
in F2. And for "Skill2"="G1:Y20" if I edit G2, the date should go in Z2.

"Gord Dibben" wrote:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim WS As Worksheet
Set WS = Worksheets("Sheet1")
If Not (Application.Intersect(Target, _
Range("skill1,skill2,skill3")) Is Nothing) Then
On Error GoTo endit
Application.EnableEvents = False
With Target.Offset(0, 1)
.Value = Now
.NumberFormat = "mm/dd/yyyy"
End With
End If
endit:
Application.EnableEvents = True
End Sub

The first two lines are not needed IMO

Dim WS As Worksheet
Set WS = Worksheets("Sheet1")


Gord Dibben MS Excel MVP


On Thu, 25 Oct 2007 12:38:02 -0700, Horatio J. Bilge, Jr.
wrote:

I have a worksheet shere I periodically update the data in a range. The
column to the right of the range displays the date when I last updated that
row of data (see code below). I would like to change the code so it will do
the same thing with whatever named range I include.

This is what I have:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim WS As Worksheet
Set WS = Worksheets("Sheet1")
If Not (Application.Intersect(Target, Range("Skill1")) Is Nothing) Then
With WS.Cells(Target.Row, 13)
.Value = Now
.NumberFormat = "mm/dd/yyyy"
End With
Exit Sub
End If
End Sub

This is what I tried. I thought this way I could easily add additional
ranges as ElseIf statements. I get an error, "Argument is not optional."
Private Sub Worksheet_Change(ByVal Target As Range)
Dim WS As Worksheet
Set WS = Worksheets("Sheet1")
If Not (Application.Intersect(Target, Range("Skill1")) Is Nothing) Then
GoTo Revise_Date
ElseIf Not (Application.Intersect(Target, Range("Skill2")) Is Nothing) Then
GoTo Revise_Date
Else: Exit Sub
End If

Revise_Date:
With WS.Cells(Target.Row, Target.Range.Offset(0,1))
.Value = Now
.NumberFormat = "mm/dd/yyyy"
End With
Exit Sub
End Sub



--

Dave Peterson

Gord Dibben
refer to cell relative to range
 
Whew!

Thanks Dave.

Gord

On Thu, 25 Oct 2007 17:16:04 -0500, Dave Peterson
wrote:

Maybe...

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCol As Long

'only one cell at a time
If Target.Cells.Count 1 Then Exit Sub

If Not (Application.Intersect(Target, Me.Range("skill1")) Is Nothing) Then
'in Skill1
With Me.Range("skill1")
myCol = .Columns(.Columns.Count).Column + 1
End With
ElseIf Not (Application.Intersect(Target, _
Me.Range("skill2")) Is Nothing) Then
'in Skill2
With Me.Range("skill2")
myCol = .Columns(.Columns.Count).Column + 1
End With
ElseIf Not (Application.Intersect(Target, _
Me.Range("skill3")) Is Nothing) Then
'in Skill3
With Me.Range("skill3")
myCol = .Columns(.Columns.Count).Column + 1
End With
Else
myCol = 0
End If

If myCol = 0 Then Exit Sub

On Error GoTo endit
Application.EnableEvents = False
With Me.Cells(Target.Row, myCol)
.Value = Now
.NumberFormat = "mm/dd/yyyy"
End With

endit:
Application.EnableEvents = True
End Sub


Horatio J. Bilge, Jr. wrote:

This places the date one cell to the right of the cell I edit. I want the
date to go one cell to the right of the range. For example, for
range("Skill1")="A1:E20" if I edit A2 (or B2, C2, etc.), the date should go
in F2. And for "Skill2"="G1:Y20" if I edit G2, the date should go in Z2.

"Gord Dibben" wrote:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim WS As Worksheet
Set WS = Worksheets("Sheet1")
If Not (Application.Intersect(Target, _
Range("skill1,skill2,skill3")) Is Nothing) Then
On Error GoTo endit
Application.EnableEvents = False
With Target.Offset(0, 1)
.Value = Now
.NumberFormat = "mm/dd/yyyy"
End With
End If
endit:
Application.EnableEvents = True
End Sub

The first two lines are not needed IMO

Dim WS As Worksheet
Set WS = Worksheets("Sheet1")


Gord Dibben MS Excel MVP


On Thu, 25 Oct 2007 12:38:02 -0700, Horatio J. Bilge, Jr.
wrote:

I have a worksheet shere I periodically update the data in a range. The
column to the right of the range displays the date when I last updated that
row of data (see code below). I would like to change the code so it will do
the same thing with whatever named range I include.

This is what I have:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim WS As Worksheet
Set WS = Worksheets("Sheet1")
If Not (Application.Intersect(Target, Range("Skill1")) Is Nothing) Then
With WS.Cells(Target.Row, 13)
.Value = Now
.NumberFormat = "mm/dd/yyyy"
End With
Exit Sub
End If
End Sub

This is what I tried. I thought this way I could easily add additional
ranges as ElseIf statements. I get an error, "Argument is not optional."
Private Sub Worksheet_Change(ByVal Target As Range)
Dim WS As Worksheet
Set WS = Worksheets("Sheet1")
If Not (Application.Intersect(Target, Range("Skill1")) Is Nothing) Then
GoTo Revise_Date
ElseIf Not (Application.Intersect(Target, Range("Skill2")) Is Nothing) Then
GoTo Revise_Date
Else: Exit Sub
End If

Revise_Date:
With WS.Cells(Target.Row, Target.Range.Offset(0,1))
.Value = Now
.NumberFormat = "mm/dd/yyyy"
End With
Exit Sub
End Sub

Dave Peterson
refer to cell relative to range
 
Hope it worked for the OP, though <bg.

Gord Dibben wrote:

Whew!

Thanks Dave.

Gord

On Thu, 25 Oct 2007 17:16:04 -0500, Dave Peterson
wrote:

Maybe...

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCol As Long

'only one cell at a time
If Target.Cells.Count 1 Then Exit Sub

If Not (Application.Intersect(Target, Me.Range("skill1")) Is Nothing) Then
'in Skill1
With Me.Range("skill1")
myCol = .Columns(.Columns.Count).Column + 1
End With
ElseIf Not (Application.Intersect(Target, _
Me.Range("skill2")) Is Nothing) Then
'in Skill2
With Me.Range("skill2")
myCol = .Columns(.Columns.Count).Column + 1
End With
ElseIf Not (Application.Intersect(Target, _
Me.Range("skill3")) Is Nothing) Then
'in Skill3
With Me.Range("skill3")
myCol = .Columns(.Columns.Count).Column + 1
End With
Else
myCol = 0
End If

If myCol = 0 Then Exit Sub

On Error GoTo endit
Application.EnableEvents = False
With Me.Cells(Target.Row, myCol)
.Value = Now
.NumberFormat = "mm/dd/yyyy"
End With

endit:
Application.EnableEvents = True
End Sub


Horatio J. Bilge, Jr. wrote:

This places the date one cell to the right of the cell I edit. I want the
date to go one cell to the right of the range. For example, for
range("Skill1")="A1:E20" if I edit A2 (or B2, C2, etc.), the date should go
in F2. And for "Skill2"="G1:Y20" if I edit G2, the date should go in Z2.

"Gord Dibben" wrote:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim WS As Worksheet
Set WS = Worksheets("Sheet1")
If Not (Application.Intersect(Target, _
Range("skill1,skill2,skill3")) Is Nothing) Then
On Error GoTo endit
Application.EnableEvents = False
With Target.Offset(0, 1)
.Value = Now
.NumberFormat = "mm/dd/yyyy"
End With
End If
endit:
Application.EnableEvents = True
End Sub

The first two lines are not needed IMO

Dim WS As Worksheet
Set WS = Worksheets("Sheet1")


Gord Dibben MS Excel MVP


On Thu, 25 Oct 2007 12:38:02 -0700, Horatio J. Bilge, Jr.
wrote:

I have a worksheet shere I periodically update the data in a range. The
column to the right of the range displays the date when I last updated that
row of data (see code below). I would like to change the code so it will do
the same thing with whatever named range I include.

This is what I have:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim WS As Worksheet
Set WS = Worksheets("Sheet1")
If Not (Application.Intersect(Target, Range("Skill1")) Is Nothing) Then
With WS.Cells(Target.Row, 13)
.Value = Now
.NumberFormat = "mm/dd/yyyy"
End With
Exit Sub
End If
End Sub

This is what I tried. I thought this way I could easily add additional
ranges as ElseIf statements. I get an error, "Argument is not optional."
Private Sub Worksheet_Change(ByVal Target As Range)
Dim WS As Worksheet
Set WS = Worksheets("Sheet1")
If Not (Application.Intersect(Target, Range("Skill1")) Is Nothing) Then
GoTo Revise_Date
ElseIf Not (Application.Intersect(Target, Range("Skill2")) Is Nothing) Then
GoTo Revise_Date
Else: Exit Sub
End If

Revise_Date:
With WS.Cells(Target.Row, Target.Range.Offset(0,1))
.Value = Now
.NumberFormat = "mm/dd/yyyy"
End With
Exit Sub
End Sub



--

Dave Peterson

Horatio J. Bilge, Jr.
refer to cell relative to range
 
Just tried it, and it works great.
Thanks!

"Dave Peterson" wrote:

Hope it worked for the OP, though <bg.

Gord Dibben wrote:

Whew!

Thanks Dave.

Gord

On Thu, 25 Oct 2007 17:16:04 -0500, Dave Peterson
wrote:

Maybe...

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCol As Long

'only one cell at a time
If Target.Cells.Count 1 Then Exit Sub

If Not (Application.Intersect(Target, Me.Range("skill1")) Is Nothing) Then
'in Skill1
With Me.Range("skill1")
myCol = .Columns(.Columns.Count).Column + 1
End With
ElseIf Not (Application.Intersect(Target, _
Me.Range("skill2")) Is Nothing) Then
'in Skill2
With Me.Range("skill2")
myCol = .Columns(.Columns.Count).Column + 1
End With
ElseIf Not (Application.Intersect(Target, _
Me.Range("skill3")) Is Nothing) Then
'in Skill3
With Me.Range("skill3")
myCol = .Columns(.Columns.Count).Column + 1
End With
Else
myCol = 0
End If

If myCol = 0 Then Exit Sub

On Error GoTo endit
Application.EnableEvents = False
With Me.Cells(Target.Row, myCol)
.Value = Now
.NumberFormat = "mm/dd/yyyy"
End With

endit:
Application.EnableEvents = True
End Sub


Horatio J. Bilge, Jr. wrote:

This places the date one cell to the right of the cell I edit. I want the
date to go one cell to the right of the range. For example, for
range("Skill1")="A1:E20" if I edit A2 (or B2, C2, etc.), the date should go
in F2. And for "Skill2"="G1:Y20" if I edit G2, the date should go in Z2.

"Gord Dibben" wrote:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim WS As Worksheet
Set WS = Worksheets("Sheet1")
If Not (Application.Intersect(Target, _
Range("skill1,skill2,skill3")) Is Nothing) Then
On Error GoTo endit
Application.EnableEvents = False
With Target.Offset(0, 1)
.Value = Now
.NumberFormat = "mm/dd/yyyy"
End With
End If
endit:
Application.EnableEvents = True
End Sub

The first two lines are not needed IMO

Dim WS As Worksheet
Set WS = Worksheets("Sheet1")


Gord Dibben MS Excel MVP


On Thu, 25 Oct 2007 12:38:02 -0700, Horatio J. Bilge, Jr.
wrote:

I have a worksheet shere I periodically update the data in a range. The
column to the right of the range displays the date when I last updated that
row of data (see code below). I would like to change the code so it will do
the same thing with whatever named range I include.

This is what I have:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim WS As Worksheet
Set WS = Worksheets("Sheet1")
If Not (Application.Intersect(Target, Range("Skill1")) Is Nothing) Then
With WS.Cells(Target.Row, 13)
.Value = Now
.NumberFormat = "mm/dd/yyyy"
End With
Exit Sub
End If
End Sub

This is what I tried. I thought this way I could easily add additional
ranges as ElseIf statements. I get an error, "Argument is not optional."
Private Sub Worksheet_Change(ByVal Target As Range)
Dim WS As Worksheet
Set WS = Worksheets("Sheet1")
If Not (Application.Intersect(Target, Range("Skill1")) Is Nothing) Then
GoTo Revise_Date
ElseIf Not (Application.Intersect(Target, Range("Skill2")) Is Nothing) Then
GoTo Revise_Date
Else: Exit Sub
End If

Revise_Date:
With WS.Cells(Target.Row, Target.Range.Offset(0,1))
.Value = Now
.NumberFormat = "mm/dd/yyyy"
End With
Exit Sub
End Sub



--

Dave Peterson

Horatio J. Bilge, Jr.
refer to cell relative to range
 
This might be asking too much, but...
Is there a way that I can programmatically add a new ElseIf statement
automatically whenever I define a new range?
~ Horatio


"Dave Peterson" wrote:

Hope it worked for the OP, though <bg.

Gord Dibben wrote:

Whew!

Thanks Dave.

Gord

On Thu, 25 Oct 2007 17:16:04 -0500, Dave Peterson
wrote:

Maybe...

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCol As Long

'only one cell at a time
If Target.Cells.Count 1 Then Exit Sub

If Not (Application.Intersect(Target, Me.Range("skill1")) Is Nothing) Then
'in Skill1
With Me.Range("skill1")
myCol = .Columns(.Columns.Count).Column + 1
End With
ElseIf Not (Application.Intersect(Target, _
Me.Range("skill2")) Is Nothing) Then
'in Skill2
With Me.Range("skill2")
myCol = .Columns(.Columns.Count).Column + 1
End With
ElseIf Not (Application.Intersect(Target, _
Me.Range("skill3")) Is Nothing) Then
'in Skill3
With Me.Range("skill3")
myCol = .Columns(.Columns.Count).Column + 1
End With
Else
myCol = 0
End If

If myCol = 0 Then Exit Sub

On Error GoTo endit
Application.EnableEvents = False
With Me.Cells(Target.Row, myCol)
.Value = Now
.NumberFormat = "mm/dd/yyyy"
End With

endit:
Application.EnableEvents = True
End Sub


Horatio J. Bilge, Jr. wrote:

This places the date one cell to the right of the cell I edit. I want the
date to go one cell to the right of the range. For example, for
range("Skill1")="A1:E20" if I edit A2 (or B2, C2, etc.), the date should go
in F2. And for "Skill2"="G1:Y20" if I edit G2, the date should go in Z2.

"Gord Dibben" wrote:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim WS As Worksheet
Set WS = Worksheets("Sheet1")
If Not (Application.Intersect(Target, _
Range("skill1,skill2,skill3")) Is Nothing) Then
On Error GoTo endit
Application.EnableEvents = False
With Target.Offset(0, 1)
.Value = Now
.NumberFormat = "mm/dd/yyyy"
End With
End If
endit:
Application.EnableEvents = True
End Sub

The first two lines are not needed IMO

Dim WS As Worksheet
Set WS = Worksheets("Sheet1")


Gord Dibben MS Excel MVP


On Thu, 25 Oct 2007 12:38:02 -0700, Horatio J. Bilge, Jr.
wrote:

I have a worksheet shere I periodically update the data in a range. The
column to the right of the range displays the date when I last updated that
row of data (see code below). I would like to change the code so it will do
the same thing with whatever named range I include.

This is what I have:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim WS As Worksheet
Set WS = Worksheets("Sheet1")
If Not (Application.Intersect(Target, Range("Skill1")) Is Nothing) Then
With WS.Cells(Target.Row, 13)
.Value = Now
.NumberFormat = "mm/dd/yyyy"
End With
Exit Sub
End If
End Sub

This is what I tried. I thought this way I could easily add additional
ranges as ElseIf statements. I get an error, "Argument is not optional."
Private Sub Worksheet_Change(ByVal Target As Range)
Dim WS As Worksheet
Set WS = Worksheets("Sheet1")
If Not (Application.Intersect(Target, Range("Skill1")) Is Nothing) Then
GoTo Revise_Date
ElseIf Not (Application.Intersect(Target, Range("Skill2")) Is Nothing) Then
GoTo Revise_Date
Else: Exit Sub
End If

Revise_Date:
With WS.Cells(Target.Row, Target.Range.Offset(0,1))
.Value = Now
.NumberFormat = "mm/dd/yyyy"
End With
Exit Sub
End Sub



--

Dave Peterson


All times are GMT +1. The time now is 09:50 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com