Macro/Formula help

F

fgwiii

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
 
L

Luke M

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

Don Guillett

Or, if you don't want a list in the sheet.
formula="=ISNUMBER(MATCH(D2,{27109,27206,etc}0))"


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Luke M said:
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 said:
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
Click to expand...
Click to expand...
 
Y

ytayta555

I am trying to run the following code as part of a macro and for some reason
Click to expand...

Hi

If you work in Excel 2003 ,maybe the reason is you
have to many arguments in your formula , and your
formula can become :

ActiveCell.FormulaR1C1 =
"=IF(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,TRUE,
IF(OR(RC[-1]=27712,RC[-1]=27716,RC[-1]=27­775,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,
TRUE,"NOMATCH"))"

{ 1) not shure in your local VBA sintax if : TRUE,"NOMATCH"))"
or TRUE,""NOMATCH""))" ; 2)if your formula is not on
an only line in VBA editor page , don't forget to be at end of line
in your
formula : _ ; such as :
ActiveCell.FormulaR1C1 = _
"=IF(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, _
.............................................etc................................... ,
_
TRUE,"NOMATCH"))" }
 
F

fgwiii

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 said:
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 said:
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
Click to expand...
Click to expand...
 
L

Luke M

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 said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

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

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

Ask a Question

Similar Threads

Macro 3
Creating Pivot Table with Macro using Multiple Sheets 0
Macro auto fill formula 4
Macro from cell specific to whole column 4
Macro Flexability 7
Link Cells & Paste Special Macro 7
macro to copy rows that contain a cell with a certain number 3
smart tags 1

Top