I am using a Worksheet_event according to instructions from another thread to
place a formula next to a user changed cell.

If Not Intersect(Target, Me.Range("B3:B65536")) Is Nothing Then ...

I wanted to change the above formula to use the current last row instead of
65536. Although the statement below is correct in calculating the last row,
using End(xlUp) obviously messes things up and "Target" changes, resulting in
erroneous results.

If Not Intersect(Target, Me.Range("B3:B" & (Cells(Rows.Count,
"B").End(xlUp).Row))) Is Nothing Then ...

Is it possible to fix this situation? My best solution would be not to
process up to the last worksheet row but only column "B" of range "MyRange".
Any cells outside "MyRange" should be processed by other code, if at all.

It is unclear how that code messes up Target. It should have no effect on
Target.

If not intersect(Target,Range("Myrange")) is nothing then


End if

--
Regards,
Tom Ogilvy


"DoctorG" wrote:

I am using a Worksheet_event according to instructions from another thread to
place a formula next to a user changed cell.

If Not Intersect(Target, Me.Range("B3:B65536")) Is Nothing Then ...

I wanted to change the above formula to use the current last row instead of
65536. Although the statement below is correct in calculating the last row,
using End(xlUp) obviously messes things up and "Target" changes, resulting in
erroneous results.

If Not Intersect(Target, Me.Range("B3:B" & (Cells(Rows.Count,
"B").End(xlUp).Row))) Is Nothing Then ...

Is it possible to fix this situation? My best solution would be not to
process up to the last worksheet row but only column "B" of range "MyRange".
Any cells outside "MyRange" should be processed by other code, if at all.

It is kind of hard to get a decent response if you ask the equivalent of
this segment of code of doesn't do what I want it to do and not really be
specific about what you want you enter and where and what you want to
happen.

A common request if for the date to be placed into column B, when new content
is created in column A.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column < 1 Then Exit Sub
If IsEmpty(Target(1)) Then Exit Sub '-- also rejects if entire row cleared
If Target.row = 1 Then Exit Sub
If IsEmpty(Target.Offset(0, 1)) Then
Target.Offset(0, 1) = Date
Target.Offset(0, 1).NumberFormat = "yyyy-mm-dd"
End If
End Sub
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"DoctorG" wrote in message ...
I am using a Worksheet_event according to instructions from another thread to
place a formula next to a user changed cell.

If Not Intersect(Target, Me.Range("B3:B65536")) Is Nothing Then ...

I wanted to change the above formula to use the current last row instead of
65536. Although the statement below is correct in calculating the last row,
using End(xlUp) obviously messes things up and "Target" changes, resulting in
erroneous results.

If Not Intersect(Target, Me.Range("B3:B" & (Cells(Rows.Count,
"B").End(xlUp).Row))) Is Nothing Then ...

Is it possible to fix this situation? My best solution would be not to
process up to the last worksheet row but only column "B" of range "MyRange".
Any cells outside "MyRange" should be processed by other code, if at all.