|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
June 19th, 2009, 01:46 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro/Formula help
Hello,
I am trying to run the following code as part of a macro and for some reason after I added additional entries to the ActiveCell.FormulaR1C1 = "=OR command and then ran the macro, the macro halts and references the ActiveCell.FormulaR1C1 = "=OR line of code. Please see below: Range("E1").Select ActiveWindow.SmallScroll ToRight:=0 Columns("E:E").Select Selection.Insert Shift:=xlToRight Range("E2").Select ActiveCell.FormulaR1C1 = "=OR(RC[-1]=27109,RC[-1]=27206,RC[-1]=27210,RC[-1]=27220,RC[-1]=27238,RC[-1]=27239,RC[-1]=27381,RC[-1]=27452,RC[-1]=27459,RC[-1]=27474,RC[-1]=27478,RC[-1]=27490,RC[-1]=27491,RC[-1]=27497,RC[-1]=27500,RC[-1]=27502,RC[-1]=27562,RC[-1]=27651,RC[-1]=27673,RC[-1]=27676,RC[-1]=27712,RC[-1]=27716,RC[-1]=27775,RC[-1]=27843,RC[-1]=27854,RC[-1]=27884,RC[-1]=28062,RC[-1]=28247,RC[-1]=28301,RC[-1]=28408,RC[-1]=28438,RC[-1]=28481,RC[-1]=28500,RC[-1]=28502,RC[-1]=28562,RC[-1]=28568,RC[-1]=28580,RC[-1]=28618,RC[-1]=28691)" Range("E2").Select Selection.AutoFill Destination:=Range("E2:E3501"), Type:=xlFillDefault Range("E2:E3501").Select Columns("E:E").Select Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone Selection.AutoFilter Field:=5, Criteria1:="TRUE" Is there something I am missing here? Thanks 
|
|
#2
June 19th, 2009, 02:16 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Macro/Formula help
The easiest way to debug the entry of formula using VBA is to:
1. enter the formula as a string (using an apostrophe) 2. exit the macro 3. in the speardsheet, try to remove the apostrophe manually and find the error ActiveCell.FormulaR1C1 = "'=OR(RC[-1]=27109,RC[-1]=27206,RC[-1]=27210,RC[-1]=27220,RC[-1]=27238,RC[-1]=27239,RC[-1]=27381,RC[-1]=27452,RC[-1]=27459,RC[-1]=27474,RC[-1]=27478,RC[-1]=27490,RC[-1]=27491,RC[-1]=27497,RC[-1]=27500,RC[-1]=27502,RC[-1]=27562,RC[-1]=27651,RC[-1]=27673,RC[-1]=27676,RC[-1]=27712,RC[-1]=27716,RC[-1]=27775,RC[-1]=27843,RC[-1]=27854,RC[-1]=27884,RC[-1]=28062,RC[-1]=28247,RC[-1]=28301,RC[-1]=28408,RC[-1]=28438,RC[-1]=28481,RC[-1]=28500,RC[-1]=28502,RC[-1]=28562,RC[-1]=28568,RC[-1]=28580,RC[-1]=28618,RC[-1]=28691)" exit sub -- Gary''s Student - gsnu200857 "fgwiii" wrote: Hello, I am trying to run the following code as part of a macro and for some reason after I added additional entries to the ActiveCell.FormulaR1C1 = "=OR command and then ran the macro, the macro halts and references the ActiveCell.FormulaR1C1 = "=OR line of code. Please see below: Range("E1").Select ActiveWindow.SmallScroll ToRight:=0 Columns("E:E").Select Selection.Insert Shift:=xlToRight Range("E2").Select ActiveCell.FormulaR1C1 = "=OR(RC[-1]=27109,RC[-1]=27206,RC[-1]=27210,RC[-1]=27220,RC[-1]=27238,RC[-1]=27239,RC[-1]=27381,RC[-1]=27452,RC[-1]=27459,RC[-1]=27474,RC[-1]=27478,RC[-1]=27490,RC[-1]=27491,RC[-1]=27497,RC[-1]=27500,RC[-1]=27502,RC[-1]=27562,RC[-1]=27651,RC[-1]=27673,RC[-1]=27676,RC[-1]=27712,RC[-1]=27716,RC[-1]=27775,RC[-1]=27843,RC[-1]=27854,RC[-1]=27884,RC[-1]=28062,RC[-1]=28247,RC[-1]=28301,RC[-1]=28408,RC[-1]=28438,RC[-1]=28481,RC[-1]=28500,RC[-1]=28502,RC[-1]=28562,RC[-1]=28568,RC[-1]=28580,RC[-1]=28618,RC[-1]=28691)" Range("E2").Select Selection.AutoFill Destination:=Range("E2:E3501"), Type:=xlFillDefault Range("E2:E3501").Select Columns("E:E").Select Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone Selection.AutoFilter Field:=5, Criteria1:="TRUE" Is there something I am missing here? Thanks
|
|
#3
June 19th, 2009, 02:19 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Macro/Formula help
the OR function can only have 30 arguements. Your's has 34. A better way to
do this would be to list all your criteria somewhere, say column Z, in the workbook (makes it easier to change if needed) and have the formula do a MATCH check like ActiveCell.Formula = _ "=ISNUMBER(MATCH(D2,Z$1:Z$40,0))" -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "fgwiii" wrote: Hello, I am trying to run the following code as part of a macro and for some reason after I added additional entries to the ActiveCell.FormulaR1C1 = "=OR command and then ran the macro, the macro halts and references the ActiveCell.FormulaR1C1 = "=OR line of code. Please see below: Range("E1").Select ActiveWindow.SmallScroll ToRight:=0 Columns("E:E").Select Selection.Insert Shift:=xlToRight Range("E2").Select ActiveCell.FormulaR1C1 = "=OR(RC[-1]=27109,RC[-1]=27206,RC[-1]=27210,RC[-1]=27220,RC[-1]=27238,RC[-1]=27239,RC[-1]=27381,RC[-1]=27452,RC[-1]=27459,RC[-1]=27474,RC[-1]=27478,RC[-1]=27490,RC[-1]=27491,RC[-1]=27497,RC[-1]=27500,RC[-1]=27502,RC[-1]=27562,RC[-1]=27651,RC[-1]=27673,RC[-1]=27676,RC[-1]=27712,RC[-1]=27716,RC[-1]=27775,RC[-1]=27843,RC[-1]=27854,RC[-1]=27884,RC[-1]=28062,RC[-1]=28247,RC[-1]=28301,RC[-1]=28408,RC[-1]=28438,RC[-1]=28481,RC[-1]=28500,RC[-1]=28502,RC[-1]=28562,RC[-1]=28568,RC[-1]=28580,RC[-1]=28618,RC[-1]=28691)" Range("E2").Select Selection.AutoFill Destination:=Range("E2:E3501"), Type:=xlFillDefault Range("E2:E3501").Select Columns("E:E").Select Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone Selection.AutoFilter Field:=5, Criteria1:="TRUE" Is there something I am missing here? Thanks
|
|
#4
June 19th, 2009, 02:46 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Macro/Formula help
|
|
#5
June 19th, 2009, 03:31 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Macro/Formula help
I went with your suggestion, however a fair number that are marked "True" are
not on the list. Here is the code: Range("E1").Select ActiveWindow.SmallScroll ToRight:=0 Columns("E:E").Select Selection.Insert Shift:=xlToRight Range("E2").Select ActiveCell.Formula = "=ISNUMBER(MATCH(D2,[Cindy_PERSONAL.XLS]Sheet1!$F$2:$F$40))" Range("E2").Select Selection.AutoFill Destination:=Range("E2:E3501"), Type:=xlFillDefault Range("E2:E3501").Select Columns("E:E").Select Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone Selection.AutoFilter Field:=5, Criteria1:="TRUE" Any suggestions? Thanks Fred "Luke M" wrote: the OR function can only have 30 arguements. Your's has 34. A better way to do this would be to list all your criteria somewhere, say column Z, in the workbook (makes it easier to change if needed) and have the formula do a MATCH check like ActiveCell.Formula = _ "=ISNUMBER(MATCH(D2,Z$1:Z$40,0))" -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "fgwiii" wrote: Hello, I am trying to run the following code as part of a macro and for some reason after I added additional entries to the ActiveCell.FormulaR1C1 = "=OR command and then ran the macro, the macro halts and references the ActiveCell.FormulaR1C1 = "=OR line of code. Please see below: Range("E1").Select ActiveWindow.SmallScroll ToRight:=0 Columns("E:E").Select Selection.Insert Shift:=xlToRight Range("E2").Select ActiveCell.FormulaR1C1 = "=OR(RC[-1]=27109,RC[-1]=27206,RC[-1]=27210,RC[-1]=27220,RC[-1]=27238,RC[-1]=27239,RC[-1]=27381,RC[-1]=27452,RC[-1]=27459,RC[-1]=27474,RC[-1]=27478,RC[-1]=27490,RC[-1]=27491,RC[-1]=27497,RC[-1]=27500,RC[-1]=27502,RC[-1]=27562,RC[-1]=27651,RC[-1]=27673,RC[-1]=27676,RC[-1]=27712,RC[-1]=27716,RC[-1]=27775,RC[-1]=27843,RC[-1]=27854,RC[-1]=27884,RC[-1]=28062,RC[-1]=28247,RC[-1]=28301,RC[-1]=28408,RC[-1]=28438,RC[-1]=28481,RC[-1]=28500,RC[-1]=28502,RC[-1]=28562,RC[-1]=28568,RC[-1]=28580,RC[-1]=28618,RC[-1]=28691)" Range("E2").Select Selection.AutoFill Destination:=Range("E2:E3501"), Type:=xlFillDefault Range("E2:E3501").Select Columns("E:E").Select Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone Selection.AutoFilter Field:=5, Criteria1:="TRUE" Is there something I am missing here? Thanks
|
|
#6
June 19th, 2009, 05:54 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Macro/Formula help
You're missing an arguement from your MATCH function to declare 'exact' match
only. Should be: "=ISNUMBER(MATCH(D2,[Cindy_PERSONAL.XLS]Sheet1!$F$2:$F$40,0))" note the added ',0' -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "fgwiii" wrote: I went with your suggestion, however a fair number that are marked "True" are not on the list. Here is the code: Range("E1").Select ActiveWindow.SmallScroll ToRight:=0 Columns("E:E").Select Selection.Insert Shift:=xlToRight Range("E2").Select ActiveCell.Formula = "=ISNUMBER(MATCH(D2,[Cindy_PERSONAL.XLS]Sheet1!$F$2:$F$40))" Range("E2").Select Selection.AutoFill Destination:=Range("E2:E3501"), Type:=xlFillDefault Range("E2:E3501").Select Columns("E:E").Select Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone Selection.AutoFilter Field:=5, Criteria1:="TRUE" Any suggestions? Thanks Fred "Luke M" wrote: the OR function can only have 30 arguements. Your's has 34. A better way to do this would be to list all your criteria somewhere, say column Z, in the workbook (makes it easier to change if needed) and have the formula do a MATCH check like ActiveCell.Formula = _ "=ISNUMBER(MATCH(D2,Z$1:Z$40,0))" -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "fgwiii" wrote: Hello, I am trying to run the following code as part of a macro and for some reason after I added additional entries to the ActiveCell.FormulaR1C1 = "=OR command and then ran the macro, the macro halts and references the ActiveCell.FormulaR1C1 = "=OR line of code. Please see below: Range("E1").Select ActiveWindow.SmallScroll ToRight:=0 Columns("E:E").Select Selection.Insert Shift:=xlToRight Range("E2").Select ActiveCell.FormulaR1C1 = "=OR(RC[-1]=27109,RC[-1]=27206,RC[-1]=27210,RC[-1]=27220,RC[-1]=27238,RC[-1]=27239,RC[-1]=27381,RC[-1]=27452,RC[-1]=27459,RC[-1]=27474,RC[-1]=27478,RC[-1]=27490,RC[-1]=27491,RC[-1]=27497,RC[-1]=27500,RC[-1]=27502,RC[-1]=27562,RC[-1]=27651,RC[-1]=27673,RC[-1]=27676,RC[-1]=27712,RC[-1]=27716,RC[-1]=27775,RC[-1]=27843,RC[-1]=27854,RC[-1]=27884,RC[-1]=28062,RC[-1]=28247,RC[-1]=28301,RC[-1]=28408,RC[-1]=28438,RC[-1]=28481,RC[-1]=28500,RC[-1]=28502,RC[-1]=28562,RC[-1]=28568,RC[-1]=28580,RC[-1]=28618,RC[-1]=28691)" Range("E2").Select Selection.AutoFill Destination:=Range("E2:E3501"), Type:=xlFillDefault Range("E2:E3501").Select Columns("E:E").Select Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone Selection.AutoFilter Field:=5, Criteria1:="TRUE" Is there something I am missing here? Thanks
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
