I have no idea how M4 and column D are related. Or how column CW and K3 are.



Dan wrote:

m4, or D col is a Date formula that calcs another Date entered col, if entry
has "NOT" been made on col CW/k3 (that row) since a certain date,

then jump to required col so can:
then update that col with new/ correct info, else jump to a different col.

"Dave Peterson" wrote:

Range(target.address)
isn't needed.
Target
is sufficient.

Me refers to the thing that owns the code--in this case, it's the worksheet.

Dim M4 as range

set M4 = me.range("M4")

set isect = application.intersect(target, m4)

if isect is nothing then
msgbox "not in " & m4.address(0,0)
end if

=========
Since you're using With target, I can use something like this to determine if
the change is in M4:

dim m4 as range
set m4 = me.range("M4")

with target
if intersect(.cells, m4) is nothing then
'not in M4
else
'in M4
end if
end with

Target.Cells are the cells that comprise the changed cells.

============
I don't think you gave enough info to what you really want to do. Maybe this
shell of code will get you closer:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Dim J6 As Range
Dim M4 As Range
Dim K3 As Range

Set J6 = Me.Range("J6")
Set M4 = Me.Range("m4")
Set K3 = Me.Range("k3")

With Target
If .Count 1 Then
Exit Sub
End If

If Not (Intersect(.Cells, J6) Is Nothing) Then
'in J6
'do what you want
ElseIf Not (Intersect(.Cells, M4) Is Nothing) Then
'in m4
'do what you want
ElseIf Not (Intersect(.Cells, K3) Is Nothing) Then
'in k3
'do what you want
End If
End With
End Sub



Dan wrote:

hi,
I tried a few variations but couple of errors keep popping up.
maybe if you can paste full version of what you mean, appreciated.. thanks

combinations between: as range, set, me..., might be too many, maybe I am
missing 1 more thing?
Error:
iSect = Application.Intersect(Range(Target.Address), M4)
'performs task but Error: object variable or with block variable not set

note: A1:B1 is scratch.. does not apply, am using automated / dynamic cell
representation with cell: M6 formula that will update position, as posted,
thanks

using common cell location name for easy move of cells when modifying.
cannot name separately.
ie: using ranges for other tasks, under same sub:
heading under same: Private Sub Worksheet_Change(ByVal Target As Excel.Range)

' I tried both variations below, neither working without error

'Dim J6 As Range
'Set J6 = Me.Range("J6")
'Dim K3 As Range
'Set K3 = Me.Range("K3")
'Dim M4 As Range
'Set M4 = Me.Range("M4")

Dim J6 As Range
Set J6 = Range("J6")
Dim K3 As Range
Set K3 = Range("K3")
Dim M4 As Range
Set M4 = Range("M4")

Dim iSect As Range
Set iSect = Intersect(Target, J6)
'Set iSect = Intersect(Target, Range(J6))

iSect = Application.Intersect(Range(Target.Address), M4)
'without Set M4: performs task but Error: object variable or with block
variable not set
If iSect Is Nothing Then 'with Set M4 & M4 Range: performs task
but Error: object variable or with block variable not set
Exit Sub
End If
If Target.Value = "2" Then

If Not Intersect(Me.Range(J6), .Cells) Is Nothing Then 'task
With Me.Cells(.Row, K3).Select
End With
End If
End If

XXXXXXXXXXXXXXXXXXXXXXXXXXXX

iSect = Application.Intersect(Range(Target.Address), Range(M4))
'without Set M4: performs task but Error: object variable or with block
variable not set
iSect = Application.Intersect(Range(Target.Address), M4)
'without 'Range'
If iSect Is Nothing Then
Exit Sub
End If
If Target.Value = "2" Then

If Not Intersect(Me.Range(J6), .Cells) Is Nothing Then 'work
done is jump to dif cell depend on status of value/ 'Else' jump dif column
not listed here, i can do on last step
With Me.Cells(.Row, K3).Select
End With
End If
End If

xxxxxxxxxxxxxxxxxx area 2.. attempt 6 8...

Dim J6 As Range
Set J6 = Me.Range("J6")
Dim K3 As Range
Set K3 = Me.Range("K3")
Dim M4 As Range
Set M4 = Me.Range("M4")

Dim iSect As Range
Set iSect = Intersect(Target, J6)
'Set iSect = Intersect(Target, Range(J6))

iSect = Application.Intersect(Range(Target.Address), Range(M4))
'Error: object variable or with block variable not set
iSect = Application.Intersect(Range(Target.Address), M4) 'Error:
object variable or with block variable not set
If iSect Is Nothing Then
Exit Sub
End If
If Target.Value = "2" Then

If Not Intersect(Me.Range(J6), .Cells) Is Nothing Then
With Me.Cells(.Row, K3).Select
End With
End If
End If

"Dave Peterson" wrote:

When you're working with objects (including ranges), you need to use the Set
keyword.

set isect = ...

You could also use the same thing with the J6, K3, M4 variables.

Dim J6 as Range
set j6 = me.range("J6")
set isect = intersect(target, j6)

Personally, I wouldn't use J6 as a variable name--it doesn't provide enough info
when I'm writing the code.

If J6 was the price of apples:

dim PriceOfApples as range
set priceofapples = me.range("J6")

In fact, you may want to name the cells that you're using (Insert|Name inside
excel) and then if you insert/delete rows in your worksheet, the named cell will
(should be) ok and the code won't need to change.

dim PriceOfApples as range
set priceofapples = me.range("PriceOfApples")

(I like to use the same variable name as the name used in excel.)


Dan wrote:

hi, I am trying to make a macro perform a function if a cell in the same line
meets a desired value. not sure if on the right track, the following is an
attempt. thanks.

Dim J6 As String
Dim K3 As String
Dim M4 As String
J6 = Range("J6")
K3 = Range("K3")
M4 = Range("M4")
Dim iSect As Range

iSect = Application.Intersect(Range(Target.Address), M4) 'not
working: type mismatch?
'iSect = Application.Intersect(Range(Target.Address), "A1:B1")
'orig example
If iSect Is Nothing Then
Exit Sub
End If
If Target.Value = "2" Then

If Not Intersect(Me.Range(J6), .Cells) Is Nothing Then
With Me.Cells(.Row, K3).Select
End With
End If
End If
End If

'J6 has CN:CN, or:
=SUBSTITUTE(SUBSTITUTE(CELL("address",$CN6),"$","" ),ROW(),"")&":"&SUBSTITUTE(SUBSTITUTE(CELL("addres s",$CN6),"$",""),ROW(),"")

'K3 has CW:CW, or:
=SUBSTITUTE(SUBSTITUTE(CELL("address",$CW3),"$","" ),ROW(),"")&":"&SUBSTITUTE(SUBSTITUTE(CELL("addres s",$CW3),"$",""),ROW(),"")

'M4 has D:D, or:
=SUBSTITUTE(SUBSTITUTE(CELL("address",$D4),"$","") ,ROW(),"")&":"&SUBSTITUTE(SUBSTITUTE(CELL("address ",$D4),"$",""),ROW(),"")

--

Dave Peterson
.


--

Dave Peterson
.


--

Dave Peterson