Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Discussion (Misc queries) > refer to cell relative to range
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 135
Default 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

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 11,058
Default 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

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 22,906
Default 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

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 135
Default 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


Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 22,906
Default 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



Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 35,218
Default 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
Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 22,906
Default 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



Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 35,218
Default 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
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Refer to a Single cell in a name range Jitendra Kumar Excel Worksheet Functions 1 October 18th 06 02:23 PM
Can a cell refer to a Range of cells? Courreges Excel Discussion (Misc queries) 3 June 13th 06 03:25 PM
Can a cell refer to range name/array of data previously set? Courreges Excel Discussion (Misc queries) 1 June 12th 06 02:45 PM
???Refer to a specifc cell in a named range Jaylin Excel Worksheet Functions 1 February 10th 06 11:47 AM
How do I refer a Range to a Cell Mal Excel Worksheet Functions 6 June 7th 05 08:47 AM


All times are GMT +1. The time now is 02:52 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement