Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Worksheet Functions > Macro/Formula help
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 36
Default 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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 11,058
Default 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
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 2,722
Default 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
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 10,124
Default Macro/Formula help
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

"Luke M" wrote in message
...
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

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 36
Default 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
Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 2,722
Default 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
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
MACRO or FORMULA CHENG28 Excel Worksheet Functions 3 November 19th 09 03:21 PM
Macro with formula orquidea Excel Worksheet Functions 3 December 5th 07 08:53 PM
help with the macro or with the formula Igneshwara reddy[_2_] Excel Worksheet Functions 6 March 22nd 07 09:16 PM
Macro or Formula? A.S. Excel Discussion (Misc queries) 1 September 20th 06 05:26 PM
Macro - formula for end of last row Gary Brown Excel Worksheet Functions 1 June 1st 05 12:02 AM


All times are GMT +1. The time now is 07:42 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement