Good evening, all,

I'm writing a worksheet_change macr that will set a value in column V of the
target row if a change is made in any of the columns B to U.
So, if I make a change in any cell from A15 to U15, I want to select/goto
V15, and so on.
I have stored column V's column number to a variable ("AmendDate"), so that
if I insert/delete any columns, the code will work out where column V has
moved to.
I wrote a piece of code many moons ago that went something like:
Range("R" & target.row & "C" & AmendDate).select
but it doesn't work quite as I want it to... well, it doesn't work at all!
Can anyone suggest a line of code that will work with either the letter
reference of the column (in this case "V"), or ideally, using the column
number variable stored in "AmendDate"?

Thanks in advance for your help. :-)

Pete

Columns B thru U:

Private Sub Worksheet_Change(ByVal Target As Range)
Set t = Target
Set r1 = Range("B:U")
If Intersect(t, r1) Is Nothing Then Exit Sub
Cells(t.Row, "V").Select
End Sub

Goes in the worksheet code area, not a standard module.
--
Gary''s Student - gsnu200774


"PeteRooney" wrote:

Good evening, all,

I'm writing a worksheet_change macr that will set a value in column V of the
target row if a change is made in any of the columns B to U.
So, if I make a change in any cell from A15 to U15, I want to select/goto
V15, and so on.
I have stored column V's column number to a variable ("AmendDate"), so that
if I insert/delete any columns, the code will work out where column V has
moved to.
I wrote a piece of code many moons ago that went something like:
Range("R" & target.row & "C" & AmendDate).select
but it doesn't work quite as I want it to... well, it doesn't work at all!
Can anyone suggest a line of code that will work with either the letter
reference of the column (in this case "V"), or ideally, using the column
number variable stored in "AmendDate"?

Thanks in advance for your help. :-)

Pete

Gary,

Thanks for this!

In the line:

Cells(t.Row, "V").Select

could I substitute the variable where I stored the column number of V? The
reason I ask it that if I insert/delete any columns before V, what is in V
won't be in V any more.

Thanks

Pete


"Gary''s Student" wrote:

Columns B thru U:

Private Sub Worksheet_Change(ByVal Target As Range)
Set t = Target
Set r1 = Range("B:U")
If Intersect(t, r1) Is Nothing Then Exit Sub


In the line:
End Sub

Goes in the worksheet code area, not a standard module.
--
Gary''s Student - gsnu200774


"PeteRooney" wrote:

Good evening, all,

I'm writing a worksheet_change macr that will set a value in column V of the
target row if a change is made in any of the columns B to U.
So, if I make a change in any cell from A15 to U15, I want to select/goto
V15, and so on.
I have stored column V's column number to a variable ("AmendDate"), so that
if I insert/delete any columns, the code will work out where column V has
moved to.
I wrote a piece of code many moons ago that went something like:
Range("R" & target.row & "C" & AmendDate).select
but it doesn't work quite as I want it to... well, it doesn't work at all!
Can anyone suggest a line of code that will work with either the letter
reference of the column (in this case "V"), or ideally, using the column
number variable stored in "AmendDate"?

Thanks in advance for your help. :-)

Pete

It doesn't matter, if T:U are deleted, V is still V, even though it was
previously X.


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"PeteRooney" wrote in message
...
Gary,

Thanks for this!

In the line:

Cells(t.Row, "V").Select

could I substitute the variable where I stored the column number of V? The
reason I ask it that if I insert/delete any columns before V, what is in V
won't be in V any more.

Thanks

Pete


"Gary''s Student" wrote:

Columns B thru U:

Private Sub Worksheet_Change(ByVal Target As Range)
Set t = Target
Set r1 = Range("B:U")
If Intersect(t, r1) Is Nothing Then Exit Sub


In the line:
End Sub

Goes in the worksheet code area, not a standard module.
--
Gary''s Student - gsnu200774


"PeteRooney" wrote:

Good evening, all,

I'm writing a worksheet_change macr that will set a value in column V
of the
target row if a change is made in any of the columns B to U.
So, if I make a change in any cell from A15 to U15, I want to
select/goto
V15, and so on.
I have stored column V's column number to a variable ("AmendDate"), so
that
if I insert/delete any columns, the code will work out where column V
has
moved to.
I wrote a piece of code many moons ago that went something like:
Range("R" & target.row & "C" & AmendDate).select
but it doesn't work quite as I want it to... well, it doesn't work at
all!
Can anyone suggest a line of code that will work with either the letter
reference of the column (in this case "V"), or ideally, using the
column
number variable stored in "AmendDate"?

Thanks in advance for your help. :-)

Pete

Hello, Bob,

I already tried subsituting the variable for the label and it works.

I trap the column number to the variable using the column header, which has
a range name. This way, no matter where the column moves to the left or
right, I can always find exactly where it's gone to.

Thanks for your help!

Pete

"Bob Phillips" wrote:

It doesn't matter, if T:U are deleted, V is still V, even though it was
previously X.


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"PeteRooney" wrote in message
...
Gary,

Thanks for this!

In the line:

Cells(t.Row, "V").Select

could I substitute the variable where I stored the column number of V? The
reason I ask it that if I insert/delete any columns before V, what is in V
won't be in V any more.

Thanks

Pete


"Gary''s Student" wrote:

Columns B thru U:

Private Sub Worksheet_Change(ByVal Target As Range)
Set t = Target
Set r1 = Range("B:U")
If Intersect(t, r1) Is Nothing Then Exit Sub


In the line:
End Sub

Goes in the worksheet code area, not a standard module.
--
Gary''s Student - gsnu200774


"PeteRooney" wrote:

Good evening, all,

I'm writing a worksheet_change macr that will set a value in column V
of the
target row if a change is made in any of the columns B to U.
So, if I make a change in any cell from A15 to U15, I want to
select/goto
V15, and so on.
I have stored column V's column number to a variable ("AmendDate"), so
that
if I insert/delete any columns, the code will work out where column V
has
moved to.
I wrote a piece of code many moons ago that went something like:
Range("R" & target.row & "C" & AmendDate).select
but it doesn't work quite as I want it to... well, it doesn't work at
all!
Can anyone suggest a line of code that will work with either the letter
reference of the column (in this case "V"), or ideally, using the
column
number variable stored in "AmendDate"?

Thanks in advance for your help. :-)

Pete