Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
HOW TO DO AN IF, IN A MACRO
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(),"") |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
HOW TO DO AN IF, IN A MACRO
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
HOW TO DO AN IF, IN A MACRO
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 . |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
HOW TO DO AN IF, IN A MACRO
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 "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(),"") |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
HOW TO DO AN IF, IN A MACRO
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
HOW TO DO AN IF, IN A MACRO
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("addres s",$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("addres s",$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 "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 . |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
HOW TO DO AN IF, IN A MACRO
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. Dan wrote: 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("addres s",$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("addres s",$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 "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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
HOW TO DO AN IF, IN A MACRO
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 . |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
HOW TO DO AN IF, IN A MACRO
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 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
HOW TO DO AN IF, IN A MACRO
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("addres s",$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("addres s",$DC7),"$",""),ROW(),"") "Dave Peterson" wrote: I have no idea how M4 and column D are related. Or how column CW and K3 are. |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
HOW TO DO AN IF, IN A MACRO
isect is an object. You need to use the Set statement.
Set isect = ... Dan wrote: 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("addres s",$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("addres s",$DC7),"$",""),ROW(),"") "Dave Peterson" wrote: I have no idea how M4 and column D are related. Or how column CW and K3 are. -- Dave Peterson |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
HOW TO DO AN IF, IN A MACRO
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 "Dave Peterson" wrote: isect is an object. You need to use the Set statement. Set isect = ... Dan wrote: 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("addres s",$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("addres s",$DC7),"$",""),ROW(),"") "Dave Peterson" wrote: I have no idea how M4 and column D are related. Or how column CW and K3 are. -- Dave Peterson . |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
HOW TO DO AN IF, IN A MACRO
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. Dan wrote: 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 "Dave Peterson" wrote: isect is an object. You need to use the Set statement. Set isect = ... Dan wrote: 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("addres s",$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("addres s",$DC7),"$",""),ROW(),"") "Dave Peterson" wrote: I have no idea how M4 and column D are related. Or how column CW and K3 are. -- Dave Peterson . -- Dave Peterson |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
HOW TO DO AN IF, IN A MACRO
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 "Dave Peterson" wrote: 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. Dan wrote: 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 "Dave Peterson" wrote: isect is an object. You need to use the Set statement. Set isect = ... Dan wrote: 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("addres s",$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("addres s",$DC7),"$",""),ROW(),"") "Dave Peterson" wrote: I have no idea how M4 and column D are related. Or how column CW and K3 are. -- Dave Peterson . -- Dave Peterson . |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
HOW TO DO AN IF, IN A MACRO
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???? Dan wrote: 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 "Dave Peterson" wrote: 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. Dan wrote: 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 "Dave Peterson" wrote: isect is an object. You need to use the Set statement. Set isect = ... Dan wrote: 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("addres s",$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("addres s",$DC7),"$",""),ROW(),"") "Dave Peterson" wrote: I have no idea how M4 and column D are related. Or how column CW and K3 are. -- Dave Peterson . -- Dave Peterson . -- Dave Peterson |
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
HOW TO DO AN IF, IN A MACRO
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(FF91,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 "Dave Peterson" wrote: 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???? Dan wrote: 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 "Dave Peterson" wrote: 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. Dan wrote: 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 "Dave Peterson" wrote: isect is an object. You need to use the Set statement. Set isect = ... Dan wrote: 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("addres s",$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("addres s",$DC7),"$",""),ROW(),"") "Dave Peterson" wrote: I have no idea how M4 and column D are related. Or how column CW and K3 are. -- Dave Peterson . -- Dave Peterson . -- Dave Peterson . |
#17
Posted to microsoft.public.excel.misc
|
|||
|
|||
HOW TO DO AN IF, IN A MACRO
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. "Dave Peterson" wrote: 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???? Dan wrote: 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 "Dave Peterson" wrote: 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. Dan wrote: 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 "Dave Peterson" wrote: isect is an object. You need to use the Set statement. Set isect = ... Dan wrote: 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("addres s",$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("addres s",$DC7),"$",""),ROW(),"") "Dave Peterson" wrote: I have no idea how M4 and column D are related. Or how column CW and K3 are. -- Dave Peterson . -- Dave Peterson . -- Dave Peterson . |
#18
Posted to microsoft.public.excel.misc
|
|||
|
|||
HOW TO DO AN IF, IN A MACRO
Change this line:
Set myRngToInspect = Me.Range("M:M,X:X") to include just the columns that you want to monitor for changes. Dan wrote: 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. "Dave Peterson" wrote: 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???? Dan wrote: 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 "Dave Peterson" wrote: 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. Dan wrote: 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 "Dave Peterson" wrote: isect is an object. You need to use the Set statement. Set isect = ... Dan wrote: 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("addres s",$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("addres s",$DC7),"$",""),ROW(),"") "Dave Peterson" wrote: I have no idea how M4 and column D are related. Or how column CW and K3 are. -- Dave Peterson . -- Dave Peterson . -- Dave Peterson . -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro recorded... tabs & file names changed, macro hangs | Excel Worksheet Functions | |||
need help to update macro to office 2007 macro enabled workbook | Excel Discussion (Misc queries) | |||
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
using a cell value to control a counter inside a macro and displaying macro value | Excel Worksheet Functions |