HOW TO DO AN IF, IN A MACRO

D

Dan

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("address",$CN6),"$",""),ROW(),"")

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

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

joel

I'm not sue why the original would work

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")


the expression "A1:B1" is not a range it is a string. the Rang
equivalent in VBA is : range("A1:B1").

You have two ways of fixing the code

from
M4 = Range("M4")

to
Set M4 = Range("M4")


or

from
'iSect = Application.Intersect(Range(Target.Address), "A1:B1")

to
'iSect = Application.Intersect(Range(Target.Address), Range("A1:B1")
 
D

Dave Peterson

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.)
 
D

Dan

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


joel said:
I'm not sue why the original would work

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")


the expression "A1:B1" is not a range it is a string. the Range
equivalent in VBA is : range("A1:B1").

You have two ways of fixing the code

from
M4 = Range("M4")

to
Set M4 = Range("M4")


or

from
'iSect = Application.Intersect(Range(Target.Address), "A1:B1")

to
'iSect = Application.Intersect(Range(Target.Address), Range("A1:B1"))


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=177115

Microsoft Office Help

.
Click to expand...
 
D

Dan

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
 
D

Dan

if this helps, the rest of the item for sub area, is:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target 'end at bottom
If .Count > 1 Then Exit Sub


End If
End With 'With in header
End Sub
 
D

Dave Peterson

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
 
D

Dan

thanks immensely for responding, will chekc that out, am slow but have been
typing on this all day.. reviewing what where.. might not have described
what doing well (but didn't know how to say yet) with couple mistakes may
have induced.

won't get back till later but if repond to my next post as correct answer,
will give 'ANSWERED' check so others can see where answer is at.
(following not with your info yet, but is what am doing)

'goal: i want to hit enter in col CN/J6, if review 'on', if col D/M4 =2,
jump cursor to col DC/K7, else col CW/K2.
'representation of columns are in cells: J6 M4 K7, as listed at bottom.


Dim iSect As Range
Set iSect = Intersect(Target, M4) 'may have had J6 here before


If Not Intersect(Me.Range(J6), .Cells) Is Nothing Then
If Range(M6).Value > "0" Then 'single cell REVIEW ON


'If Range(M4).Value = "2" Then 'orig attempt, column D/M4,
Problem: ck intersect/isect?, Error: type mismatch, trying to add: If date
col D9=2/3/4?

iSect = Application.Intersect(Range(Target.Address), M4)
'Error: Object or With block variable not set
If iSect Is Nothing Then
Exit Sub
End If
If Target.Value = "2" Then 'if value in col D/M4 is true

With Me.Cells(.Row, K3).Select 'then to this, if still
true-Error: type mismatch
End With
Else
With Me.Cells(.Row, K7).Select 'else this
End With
End If

Else
With Me.Cells(.Row, K3).Select 'Error: type mismatch
End With
End If
End If


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

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

''K7 has DC:DC, or:
=SUBSTITUTE(SUBSTITUTE(CELL("address",$DC7),"$",""),ROW(),"")&":"&SUBSTITUTE(SUBSTITUTE(CELL("address",$DC7),"$",""),ROW(),"")




============
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
 
D

Dave Peterson

If you can't get the other suggestion to help, you're going to have to clarify
this:

'goal: i want to hit enter in col CN/J6, if review 'on', if col D/M4 =2,
jump cursor to col DC/K7, else col CW/K2.

I don't understand it.
 
D

Dan

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.
 
D

Dan

hi, sorry small window- missed pasting items at bottom. have reviewed your
suggestions some as well as could see. even with my small corrections -
straightening things out (to & from), still getting 1 same error as before.
I am not sure what am doing wrong.



'goal: i want to hit enter in col CN/J6, if review 'on', if col D/M4 =2,
jump cursor to col DC/K7, else col CW/K2.
'col D/ designated by m4 is formula column calcs if different date col is >
specific date, e.g.: IF(CC9>$S$3,2,0)
'merely if formula col D = 2, then macro A, else macro B
'representation of columns are in cells: J6 M4 K7, as listed at bottom.

' Dim J6 As Range 'installed above
' Set J6 = Range("J6")
' Dim K7 As Range
' Set K7 = Range("K7")
' Dim M4 As Range
' Set M4 = Me.Range("M4")

Dim iSect As Range
' 'Set iSect = Intersect(Target, M4) 'may have had J6 here before
Set iSect = Application.Intersect(Target, M4)

If Not Intersect(Me.Range(J6), .Cells) Is Nothing Then
If Range(M6).Value > "0" Then 'single cell REVIEW ON

'If Range(M4).Value = "2" Then 'orig attempt, column D/M4,
Problem: ck intersect/isect?, old Error: type mismatch, trying to add: If
date col D9=2/3/4?

'Error: Object or With block variable not set
iSect = Application.Intersect(Target, M4)
'old: iSect = Application.Intersect(Range(Target.Address), M4)
'old Error: Object or With block variable not set
If iSect Is Nothing Then
Exit Sub
End If
If Target.Value = "2" Then 'if value in col D/M4 is true

With Me.Cells(.Row, K3).Select 'then to this, if still
true-Error: type mismatch
End With
Else
With Me.Cells(.Row, K7).Select 'else this
End With 'JUMP
End If

Else
With Me.Cells(.Row, K3).Select 'Error: type mismatch
End With
End If
End If


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

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

''K7 has DC:DC, or:
=SUBSTITUTE(SUBSTITUTE(CELL("address",$DC7),"$",""),ROW(),"")&":"&SUBSTITUTE(SUBSTITUTE(CELL("address",$DC7),"$",""),ROW(),"")
 
D

Dan

i guesse had that already posted, that item done.. if that is not the
problem,
then I need to test for M4 equals 2? are we using isect for what had been
defined?

then might be I need to use your example (copy below), but don't see it
address how to test M4 range if equals 2 ?
back to simple

If column D = 2, perform macro1, else perform macro2

With Target
If Intersect(.Cells, M4) Is Nothing Then
'not in M4
Else
'in M4
End If
End With
 
D

Dave Peterson

I don't understand.

Column D is a complete column...not a single cell with a single value.

And you haven't explained how M4 is related to column D.
 
D

Dan

hi, sorry for delay, had spent couple 8 hour days trying to do this item.
know it is relatively simple, just takes me much more time since don't know
macro's/ vba, may be why i don't always explain well. in trying to keep to
just basics (will give some info..)

have sample of date macro WITH ME certain col enter date in different col.
so other: col D has formula's, when I do data entry (WITH ME.. same?) on
eg col M, if col D equals a certain value, =2, then do MACRO 1, else do
MACRO 2

i've seen IF, ELSE, END IF. have been collecting 1 or 2 other examples but
haven't had time to excessive test simple task of IF in column D, to choose
next macro to occur.

my experience with macro's is minimal, sorry if some what i say misleads but
to do an eg: set isect.. with eclipse after will not help me since i do
not know what the "before the etcetera" was to start with.. maybe isect is a
"anything" name is a given nickname (but worse for me i do not know, if is
the case: how the 'intersect' is working)

same thing different way?: in any row: 500, if col/ cell D500 = 2, do x,
else y
(I can figure out the: else, x, y) thanks
 
D

Dave Peterson

So if you're making changes in column M or X (say) that make the formula in
column D recalculate, you could use something like:

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

Set myRngToInspect = Me.Range("M:M,X:X")

With Target
If .Cells.Count > 1 Then
Exit Sub 'single cell at a time
End If

If Intersect(.Cells, myRngToInspect) Is Nothing Then
Exit Sub 'not in M or X
End If

Select Case Target.EntireRow.Range("D1").Value
Case Is = 2: Call Macro2
Case Is = 3: Call Macro3
Case Else
'do nothing or call a different macro
End Select
End With
End Sub

This kind of thing really depends on how that formula in column D is written.
If it depends on other cells besides the ones in column M and X in the same row,
then worksheet_change may not be the best way to go.

But if you have that complex formula, then you may have to check all the cells
in the column D to know what to do????
 
D

Dan

hi, thanks for the reply, won't be able to get to for 2-7 days so thought
owe what got. thanks for the extra effort. just glancing at looks very well
encompassed.

was not aware of how col d receives could affect what doing, if so..
i use a-z as 'prefix' work columns to other cols (to reduce repetition).
B col main test:
=IF(OR(FF9={"",0,"n/a"}),0,IF(FF9>$X$2,9,IF(FF9>1,2,1)))

D col: (dates
=IF(OR(B637=0,COUNTIF(CO637:CS637,""),CC637<$S$2),0,IF(OR(CC637<$S$3,CD637<$S$4),1,2))

- if date < fixed cell, false, true)
(co:cs affects date in CC, 2 levels of cc calc'd, cascaded "if's" date
considers for 2 different date cols, 1 more pending this work).. probably:
..1,3),2)))
pending to test, thanks
 
D

Dan

should say, col M has no affect to D (once updated: semi-static, e.g. cols:
CO:CS base work data cols), if record up to/ since date then macro1?: jump
to next work, else macro2 jump to base work data input.
 
D

Dave Peterson

Change this line:
Set myRngToInspect = Me.Range("M:M,X:X")
to include just the columns that you want to monitor for changes.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

help with EXCEL SCRIPT 0
INDIRECT Question.. 0
help with: MACRO / SCRIPT for button 3
Help needed! Extracting Heading 3 and Contents from a Word Doc to Excel 0
Help with script: STARTING A NEW SUB.. (within sub?) 11
macros creating 1
Applying macro to mulitple worksheets? 1
Creating Pivot Table with Macro using Multiple Sheets 0

Top