Recording a macro with 12 or more nesting formulas. Excel 2007

**arjun_mail** · 08-29-2010, 08:37 PM

Hi there,
I am currently trying to record a macro that will help me bucketize marketing data that I work on. Even though most of the other nesting issues work correctly. I have trouble with one.
as the macro record the nesting function in more than 1 line, I am assuming that the code is broken, hence there is a syntax error.
Attached is the code.

Application.CutCopyMode = False
Columns("F:F").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("F2").Select
ActiveCell.FormulaR1C1 = _
"=IF(OR(RC5=""Less than 1 Million"",RC5=""1-3 Million"",RC5=""3-5 Million"",RC5=""5-10 Million""),""Under 10 Million"",IF(RC5=""10-25 Million"",""10-25 Million"",IF(RC5=""25-50 Million"",""25-50 Million"",IF(RC5=""50-100 Million"",""50-100 Million"",IF(RC5=""100-250 Million"",""100-250 Million"",IF(RC5=""250-500 Million"",""250-500 Million"",IF(RC5=""500 Million-1 Billion"",""500 Million-1 Billion"",IF(OR(RC5=""Over 25 Billion"",RC5=""1-10 Billion"",RC5=""11-25 Billion""),""Over 1 Billion"",IF(OR(RC5=""Confidential"",RC5="" ""),""Confidential"",""NA"")))))))))"
Columns("J:J").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("I2").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.NumberFormat = "0"
Range("J2").Select
ActiveCell.FormulaR1C1 = _
"=IF(AND(RC9>=1,RC9<=25),""Fewer than 25"",IF(AND(RC9>=26,RC9<=50),""26-50"",IF(AND(RC9>=51,RC9<=100),""51-100"",IF(AND(RC9>=101,RC9<=500),""101-500"",IF(AND(RC9>=501,RC9<=1000),""501-1000"",IF(RC9>=1000,""Over 1,000"",IF(RC9<1,""Confidential"",""NA"")))))))"
Range("J2").Select
Selection.AutoFill Destination:=Range("J2:J152"), Type:=xlFillDefault
Range("J2:J152").Select
Columns("U:U").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("U2").Select
ActiveCell.FormulaR1C1 = _
"=IF(OR(RC20=""Business Services"",RC20=""Financial Services"",RC20=""Insurance"",RC20=""Banking"",RC20=""Media"",RC20=""Language Services"",RC20=""Consumer Services"",RC20=""Retail "",RC20=""Real Estate"",RC20=""Telecommunications Services""),""Business Services"",IF(OR(RC20=""Industrial Manufacturing"",RC20=""Chemicals"",RC20=""Metals & Mining"",RC20=""Industrial Manufacturing"",RC20=""Optics & Photonics"",RC20=""Security Products & Services""),""Industrial Manufacturing"",IF(RC20=""Aerospace & Defense"",""Aerospace & Defense"",IF(OR(RC20=""Food and Drink"",RC20=""Agriculture""),""Food and Drink"",IF(OR(RC20=""Automotive & Transport"",RC20=""Transportation Services""),""Automotive & Transport"",IF(OR(RC20=""Biotech & Pharma"",RC20=""Health Care""),""Biotechnology"",IF(OR(RC20=""Energy & Utilities"",RC20=""Waste Management Services""),""Alternative energy"",IF(OR(RC20=""Computer Software"",RC20=""Computer Hardware"",RC20=""Computer Services"",RC20=""Electronics""),""Information Technology"",IF(OR(RC
20=""Consumer Product Manufacturing"",RC20=""Security Products & Services"",RC20=""Telecommunications Equipment""),""Consumer Product Manufacturing"",IF(OR(RC20=""Associations and Government"",RC20=""Membership Organizations"",RC20=""Foundations"",RC20=""Government""),""Associations and Government"",IF(RC20=""Construction"",""Construction"",IF(RC20="""",""Empty Field"",""NA""))))))))))))"
Range("U1").Select
ActiveCell.FormulaR1C1 = "Industry class"
With ActiveCell.Characters(Start:=1, Length:=14).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.Color = -16777216
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
Range("U2").Select
Selection.AutoFill Destination:=Range("U2:U152"), Type:=xlFillDefault
Range("U2:U152").Select
End Sub

Now I tried line break with " _" and then proceeded the next line with " &" but I still have challenges. Any help would be appreciated.

Thanks

**Norie** · 08-29-2010, 09:18 PM

Why not 'fix' the formula?

Almost any formula with that many nested IFs can be restructured to be at least a bit shorter and perhaps even more efficient.

As for the macro recorder, it's a great tool but you shouldn't rely on it coming up with the exact code you need.

Use it to get the syntax you need and change it for your ultimate enquiries.

I'm no formula guru but taking a quick look at them I can see that the 2nd one at least can be shortened, probably using some sort of lookup.

Here's what I came up with for the formula in J2.

=IF(I2<1, "Confidential", LOOKUP(I2, {0,26,51,101,501,1001},{"Fewer than 25","26-50","51-100","101-500","501-1000","Over 1,000"}))

I know it's missing a bit but you should get the idea, for that formula at least.

**arjun_mail** · 08-29-2010, 09:25 PM

thanks for the help, I guess your right about making it shorter with the first 2 functions as this will not make the excel file 2 heavy. However with the last part, I am trying to funnel all the options into 10-15 to make it look more proportionate on the graph.

**teylyn** · 08-29-2010, 09:30 PM

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

**shg** · 08-30-2010, 12:02 AM

Please change your QUOTE tags to CODE tags.

**MarvinP** · 08-30-2010, 12:38 AM

arjun -
You need to click on the Green Edit button below your message, go to Advanced and highlight/sekect your code and click on the # icon above the text. This will put Code tags around the selected text. Then save the changes.