How to run the macro for specific times? how to short the code for text, formula and border?

D

drhgsangani

New Member
Joined
Aug 14, 2013
Messages
12
<!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} </style> <![endif]--><!--[if gte mso 9]><xml> <o:shapedefaults v:ext="edit" spidmax="1026"/> </xml><![endif]--><!--[if gte mso 9]><xml> <o:shapelayout v:ext="edit"> <o:idmap v:ext="edit" data="1"/> </o:shapelayout></xml><![endif]-->


Dear all,
i have prepared code by following excel steps.
it is too lengthy (it must require shortning), i don't know how to prepare code for border.
in the code, one part is text & formulas and other part is to bordering it.
Problem with the code are;

1) the code is starting from Range("A1").
i want it should run it on any cell of the sheet.

2) the value is also set for sr. no 1 to 20.
i want it should ask me like "how many items in table?"

3) the same code runs only one time in sheet.
i want it should runs for n times, means it should ask me like "how many tables?"

thanks in advance.

following is the code:

Sub Evaluation()
'
Columns("A:A").ColumnWidth = 3.86
Columns("B:B").ColumnWidth = 23.14
Columns("C:C").ColumnWidth = 9.29
Columns("D:D").ColumnWidth = 7.71
Columns("E:E").ColumnWidth = 6.29
Columns("F:F").ColumnWidth = 7
Columns("G:G").ColumnWidth = 9.14
Columns("H:H").ColumnWidth = 7.43
Columns("I:I").ColumnWidth = 13.43
Columns("J:J").ColumnWidth = 7
Columns("K:K").ColumnWidth = 11.86
Columns("L:L").ColumnWidth = 5.86
Columns("M:M").ColumnWidth = 9.43
Columns("N:N").ColumnWidth = 8.57
Range("A3").FormulaR1C1 = "Sr"
Range("A4").FormulaR1C1 = "No"
Range("A5").FormulaR1C1 = "1"
Range("A6").FormulaR1C1 = "2"
Range("A7").FormulaR1C1 = "3"
Range("A8").FormulaR1C1 = "4"
Range("A9").FormulaR1C1 = "5"
Range("A10").FormulaR1C1 = "6"
Range("A11").FormulaR1C1 = "7"
Range("A12").FormulaR1C1 = "8"
Range("A13").FormulaR1C1 = "9"
Range("A14").FormulaR1C1 = "10"
Range("A15").FormulaR1C1 = "11"
Range("A16").FormulaR1C1 = "12"
Range("A17").FormulaR1C1 = "13"
Range("A18").FormulaR1C1 = "14"
Range("A19").FormulaR1C1 = "15"
Range("A20").FormulaR1C1 = "16"
Range("A21").FormulaR1C1 = "17"
Range("A22").FormulaR1C1 = "18"
Range("A23").FormulaR1C1 = "19"
Range("A24").FormulaR1C1 = "20"
Range("A27").FormulaR1C1 = "Calculations :"
Range("B1").FormulaR1C1 = "Value1 "
Range("B3").FormulaR1C1 = "Name"
Range("B25").FormulaR1C1 = "Value2 : "
Range("B28").FormulaR1C1 = "Value3"
Range("C3").FormulaR1C1 = "Value4"
Range("C4").FormulaR1C1 = "Value5"
Range("C28").FormulaR1C1 = "=IF(ISERROR(R[-3]C/RC[8]*100),"""",R[-3]C/RC[8]*100)"
Range("D3").FormulaR1C1 = "Value6 "
Range("D4").FormulaR1C1 = "Value7"
Range("D5").FormulaR1C1 = "=IF(ISERROR(RC[-1]/RC[1]),"""",RC[-1]/RC[1])"
Range("D6").FormulaR1C1 = "=IF(ISERROR(RC[-1]/RC[1]),"""",RC[-1]/RC[1])"
Range("D7").FormulaR1C1 = "=IF(ISERROR(RC[-1]/RC[1]),"""",RC[-1]/RC[1])"
Range("D8").FormulaR1C1 = "=IF(ISERROR(RC[-1]/RC[1]),"""",RC[-1]/RC[1])"
Range("D9").FormulaR1C1 = "=IF(ISERROR(RC[-1]/RC[1]),"""",RC[-1]/RC[1])"
Range("D10").FormulaR1C1 = "=IF(ISERROR(RC[-1]/RC[1]),"""",RC[-1]/RC[1])"
Range("D11").FormulaR1C1 = "=IF(ISERROR(RC[-1]/RC[1]),"""",RC[-1]/RC[1])"
Range("D12").FormulaR1C1 = "=IF(ISERROR(RC[-1]/RC[1]),"""",RC[-1]/RC[1])"
Range("D13").FormulaR1C1 = "=IF(ISERROR(RC[-1]/RC[1]),"""",RC[-1]/RC[1])"
Range("D14").FormulaR1C1 = "=IF(ISERROR(RC[-1]/RC[1]),"""",RC[-1]/RC[1])"
Range("D15").FormulaR1C1 = "=IF(ISERROR(RC[-1]/RC[1]),"""",RC[-1]/RC[1])"
Range("D16").FormulaR1C1 = "=IF(ISERROR(RC[-1]/RC[1]),"""",RC[-1]/RC[1])"
Range("D17").FormulaR1C1 = "=IF(ISERROR(RC[-1]/RC[1]),"""",RC[-1]/RC[1])"
Range("D18").FormulaR1C1 = "=IF(ISERROR(RC[-1]/RC[1]),"""",RC[-1]/RC[1])"
Range("D19").FormulaR1C1 = "=IF(ISERROR(RC[-1]/RC[1]),"""",RC[-1]/RC[1])"
Range("D20").FormulaR1C1 = "=IF(ISERROR(RC[-1]/RC[1]),"""",RC[-1]/RC[1])"
Range("D21").FormulaR1C1 = "=IF(ISERROR(RC[-1]/RC[1]),"""",RC[-1]/RC[1])"
Range("D22").FormulaR1C1 = "=IF(ISERROR(RC[-1]/RC[1]),"""",RC[-1]/RC[1])"
Range("D23").FormulaR1C1 = "=IF(ISERROR(RC[-1]/RC[1]),"""",RC[-1]/RC[1])"
Range("D24").FormulaR1C1 = "=IF(ISERROR(RC[-1]/RC[1]),"""",RC[-1]/RC[1])"
Range("D25").FormulaR1C1 = _
"For Print, hide red text columns (D,E, F, H, J & L) and empty rows"
Range("D28").FormulaR1C1 = "Value8"
Range("E4").FormulaR1C1 = "Value9"
Range("F4").FormulaR1C1 = "Value10"
Range("F28").FormulaR1C1 = "Value11"
Range("G3").FormulaR1C1 = "Value12"
Range("G4").FormulaR1C1 = "Value13"
Range("G5").FormulaR1C1 = "=IF(ISERROR(RC[-2]*RC[1]),"""",RC[-2]*RC[1])"
Range("G6").FormulaR1C1 = "=IF(ISERROR(RC[-2]*RC[1]),"""",RC[-2]*RC[1])"
Range("G7").FormulaR1C1 = "=IF(ISERROR(RC[-2]*RC[1]),"""",RC[-2]*RC[1])"
Range("G8").FormulaR1C1 = "=IF(ISERROR(RC[-2]*RC[1]),"""",RC[-2]*RC[1])"
Range("G9").FormulaR1C1 = "=IF(ISERROR(RC[-2]*RC[1]),"""",RC[-2]*RC[1])"
Range("G10").FormulaR1C1 = "=IF(ISERROR(RC[-2]*RC[1]),"""",RC[-2]*RC[1])"
Range("G11").FormulaR1C1 = "=IF(ISERROR(RC[-2]*RC[1]),"""",RC[-2]*RC[1])"
Range("G12").FormulaR1C1 = "=IF(ISERROR(RC[-2]*RC[1]),"""",RC[-2]*RC[1])"
Range("G13").FormulaR1C1 = "=IF(ISERROR(RC[-2]*RC[1]),"""",RC[-2]*RC[1])"
Range("G14").FormulaR1C1 = "=IF(ISERROR(RC[-2]*RC[1]),"""",RC[-2]*RC[1])"
Range("G15").FormulaR1C1 = "=IF(ISERROR(RC[-2]*RC[1]),"""",RC[-2]*RC[1])"
Range("G16").FormulaR1C1 = "=IF(ISERROR(RC[-2]*RC[1]),"""",RC[-2]*RC[1])"
Range("G17").FormulaR1C1 = "=IF(ISERROR(RC[-2]*RC[1]),"""",RC[-2]*RC[1])"
Range("G18").FormulaR1C1 = "=IF(ISERROR(RC[-2]*RC[1]),"""",RC[-2]*RC[1])"
Range("G19").FormulaR1C1 = "=IF(ISERROR(RC[-2]*RC[1]),"""",RC[-2]*RC[1])"
Range("G20").FormulaR1C1 = "=IF(ISERROR(RC[-2]*RC[1]),"""",RC[-2]*RC[1])"
Range("G21").FormulaR1C1 = "=IF(ISERROR(RC[-2]*RC[1]),"""",RC[-2]*RC[1])"
Range("G22").FormulaR1C1 = "=IF(ISERROR(RC[-2]*RC[1]),"""",RC[-2]*RC[1])"
Range("G23").FormulaR1C1 = "=IF(ISERROR(RC[-2]*RC[1]),"""",RC[-2]*RC[1])"
Range("G24").FormulaR1C1 = "=IF(ISERROR(RC[-2]*RC[1]),"""",RC[-2]*RC[1])"
Range("H4").FormulaR1C1 = "Value14"
Range("H5").FormulaR1C1 = _
"=IF(ISERROR(RC[-2]*RC[-4]/100),"""",RC[-2]*RC[-4]/100)"
Range("H6").FormulaR1C1 = _
"=IF(ISERROR(RC[-2]*RC[-4]/100),"""",RC[-2]*RC[-4]/100)"
Range("H7").FormulaR1C1 = _
"=IF(ISERROR(RC[-2]*RC[-4]/100),"""",RC[-2]*RC[-4]/100)"
Range("H8").FormulaR1C1 = _
"=IF(ISERROR(RC[-2]*RC[-4]/100),"""",RC[-2]*RC[-4]/100)"
Range("H9").FormulaR1C1 = _
"=IF(ISERROR(RC[-2]*RC[-4]/100),"""",RC[-2]*RC[-4]/100)"
Range("H10").FormulaR1C1 = _
"=IF(ISERROR(RC[-2]*RC[-4]/100),"""",RC[-2]*RC[-4]/100)"
Range("H11").FormulaR1C1 = _
"=IF(ISERROR(RC[-2]*RC[-4]/100),"""",RC[-2]*RC[-4]/100)"
Range("H12").FormulaR1C1 = _
"=IF(ISERROR(RC[-2]*RC[-4]/100),"""",RC[-2]*RC[-4]/100)"
Range("H13").FormulaR1C1 = _
"=IF(ISERROR(RC[-2]*RC[-4]/100),"""",RC[-2]*RC[-4]/100)"
Range("H14").FormulaR1C1 = _
"=IF(ISERROR(RC[-2]*RC[-4]/100),"""",RC[-2]*RC[-4]/100)"
Range("H15").FormulaR1C1 = _
"=IF(ISERROR(RC[-2]*RC[-4]/100),"""",RC[-2]*RC[-4]/100)"
Range("H16").FormulaR1C1 = _
"=IF(ISERROR(RC[-2]*RC[-4]/100),"""",RC[-2]*RC[-4]/100)"
Range("H17").FormulaR1C1 = _
"=IF(ISERROR(RC[-2]*RC[-4]/100),"""",RC[-2]*RC[-4]/100)"
Range("H18").FormulaR1C1 = _
"=IF(ISERROR(RC[-2]*RC[-4]/100),"""",RC[-2]*RC[-4]/100)"
Range("H19").FormulaR1C1 = _
"=IF(ISERROR(RC[-2]*RC[-4]/100),"""",RC[-2]*RC[-4]/100)"
Range("H20").FormulaR1C1 = _
"=IF(ISERROR(RC[-2]*RC[-4]/100),"""",RC[-2]*RC[-4]/100)"
Range("H21").FormulaR1C1 = _
"=IF(ISERROR(RC[-2]*RC[-4]/100),"""",RC[-2]*RC[-4]/100)"
Range("H22").FormulaR1C1 = _
"=IF(ISERROR(RC[-2]*RC[-4]/100),"""",RC[-2]*RC[-4]/100)"
Range("H23").FormulaR1C1 = _
"=IF(ISERROR(RC[-2]*RC[-4]/100),"""",RC[-2]*RC[-4]/100)"
Range("H24").FormulaR1C1 = _
"=IF(ISERROR(RC[-2]*RC[-4]/100),"""",RC[-2]*RC[-4]/100)"
Range("I3").FormulaR1C1 = "Value15"
Range("I4").FormulaR1C1 = "Value16"
Range("I5").FormulaR1C1 = "=IF(ISERROR(RC[-6]-RC[-2]),"""",RC[-6]-RC[-2])"
Range("I6").FormulaR1C1 = "=IF(ISERROR(RC[-6]-RC[-2]),"""",RC[-6]-RC[-2])"
Range("I7").FormulaR1C1 = "=IF(ISERROR(RC[-6]-RC[-2]),"""",RC[-6]-RC[-2])"
Range("I8").FormulaR1C1 = "=IF(ISERROR(RC[-6]-RC[-2]),"""",RC[-6]-RC[-2])"
Range("I9").FormulaR1C1 = "=IF(ISERROR(RC[-6]-RC[-2]),"""",RC[-6]-RC[-2])"
Range("I10").FormulaR1C1 = "=IF(ISERROR(RC[-6]-RC[-2]),"""",RC[-6]-RC[-2])"
Range("I11").FormulaR1C1 = "=IF(ISERROR(RC[-6]-RC[-2]),"""",RC[-6]-RC[-2])"
Range("I12").FormulaR1C1 = "=IF(ISERROR(RC[-6]-RC[-2]),"""",RC[-6]-RC[-2])"
Range("I13").FormulaR1C1 = "=IF(ISERROR(RC[-6]-RC[-2]),"""",RC[-6]-RC[-2])"
Range("I14").FormulaR1C1 = "=IF(ISERROR(RC[-6]-RC[-2]),"""",RC[-6]-RC[-2])"
Range("I15").FormulaR1C1 = "=IF(ISERROR(RC[-6]-RC[-2]),"""",RC[-6]-RC[-2])"
Range("I16").FormulaR1C1 = "=IF(ISERROR(RC[-6]-RC[-2]),"""",RC[-6]-RC[-2])"
Range("I17").FormulaR1C1 = "=IF(ISERROR(RC[-6]-RC[-2]),"""",RC[-6]-RC[-2])"
Range("I18").FormulaR1C1 = "=IF(ISERROR(RC[-6]-RC[-2]),"""",RC[-6]-RC[-2])"
Range("I19").FormulaR1C1 = "=IF(ISERROR(RC[-6]-RC[-2]),"""",RC[-6]-RC[-2])"
Range("I20").FormulaR1C1 = "=IF(ISERROR(RC[-6]-RC[-2]),"""",RC[-6]-RC[-2])"
Range("I21").FormulaR1C1 = "=IF(ISERROR(RC[-6]-RC[-2]),"""",RC[-6]-RC[-2])"
Range("I22").FormulaR1C1 = "=IF(ISERROR(RC[-6]-RC[-2]),"""",RC[-6]-RC[-2])"
Range("I23").FormulaR1C1 = "=IF(ISERROR(RC[-6]-RC[-2]),"""",RC[-6]-RC[-2])"
Range("I24").FormulaR1C1 = "=IF(ISERROR(RC[-6]-RC[-2]),"""",RC[-6]-RC[-2])"
Range("I28").FormulaR1C1 = "Value17"
Range("J4").FormulaR1C1 = "Value18"
Range("J5").FormulaR1C1 = "=IF(ISERROR(RC[-6]-RC[-2]),"""",RC[-6]-RC[-2])"
Range("J6").FormulaR1C1 = "=IF(ISERROR(RC[-6]-RC[-2]),"""",RC[-6]-RC[-2])"
Range("J7").FormulaR1C1 = "=IF(ISERROR(RC[-6]-RC[-2]),"""",RC[-6]-RC[-2])"
Range("J8").FormulaR1C1 = "=IF(ISERROR(RC[-6]-RC[-2]),"""",RC[-6]-RC[-2])"
Range("J9").FormulaR1C1 = "=IF(ISERROR(RC[-6]-RC[-2]),"""",RC[-6]-RC[-2])"
Range("J10").FormulaR1C1 = "=IF(ISERROR(RC[-6]-RC[-2]),"""",RC[-6]-RC[-2])"
Range("J11").FormulaR1C1 = "=IF(ISERROR(RC[-6]-RC[-2]),"""",RC[-6]-RC[-2])"
Range("J12").FormulaR1C1 = "=IF(ISERROR(RC[-6]-RC[-2]),"""",RC[-6]-RC[-2])"
Range("J13").FormulaR1C1 = "=IF(ISERROR(RC[-6]-RC[-2]),"""",RC[-6]-RC[-2])"
Range("J14").FormulaR1C1 = "=IF(ISERROR(RC[-6]-RC[-2]),"""",RC[-6]-RC[-2])"
Range("J15").FormulaR1C1 = "=IF(ISERROR(RC[-6]-RC[-2]),"""",RC[-6]-RC[-2])"
Range("J16").FormulaR1C1 = "=IF(ISERROR(RC[-6]-RC[-2]),"""",RC[-6]-RC[-2])"
Range("J17").FormulaR1C1 = "=IF(ISERROR(RC[-6]-RC[-2]),"""",RC[-6]-RC[-2])"
Range("J18").FormulaR1C1 = "=IF(ISERROR(RC[-6]-RC[-2]),"""",RC[-6]-RC[-2])"
Range("J19").FormulaR1C1 = "=IF(ISERROR(RC[-6]-RC[-2]),"""",RC[-6]-RC[-2])"
Range("J20").FormulaR1C1 = "=IF(ISERROR(RC[-6]-RC[-2]),"""",RC[-6]-RC[-2])"
Range("J21").FormulaR1C1 = "=IF(ISERROR(RC[-6]-RC[-2]),"""",RC[-6]-RC[-2])"
Range("J22").FormulaR1C1 = "=IF(ISERROR(RC[-6]-RC[-2]),"""",RC[-6]-RC[-2])"
Range("J23").FormulaR1C1 = "=IF(ISERROR(RC[-6]-RC[-2]),"""",RC[-6]-RC[-2])"
Range("J24").FormulaR1C1 = "=IF(ISERROR(RC[-6]-RC[-2]),"""",RC[-6]-RC[-2])"
Range("K3").FormulaR1C1 = "Value19"
Range("K4").FormulaR1C1 = "Value20"
Range("K5").FormulaR1C1 = _
"=IF(ISERROR(RC[-8]/R[20]C[-8]),"""",RC[-8]/R[20]C[-8])"
Range("K6").FormulaR1C1 = _
"=IF(ISERROR(R[-1]C*RC[-2]/R[-1]C[-8]),"""",R[-1]C*RC[-2]/R[-1]C[-8])"
Range("K7").FormulaR1C1 = _
"=IF(ISERROR(R[-2]C*RC[-2]/R[-2]C[-8]),"""",R[-2]C*RC[-2]/R[-2]C[-8])"
Range("K8").FormulaR1C1 = _
"=IF(ISERROR(R[-3]C*RC[-2]/R[-3]C[-8]),"""",R[-3]C*RC[-2]/R[-3]C[-8])"
Range("K9").FormulaR1C1 = _
"=IF(ISERROR(R[-4]C*RC[-2]/R[-4]C[-8]),"""",R[-4]C*RC[-2]/R[-4]C[-8])"
Range("K10").FormulaR1C1 = _
"=IF(ISERROR(R[-5]C*RC[-2]/R[-5]C[-8]),"""",R[-5]C*RC[-2]/R[-5]C[-8])"
Range("K11").FormulaR1C1 = _
"=IF(ISERROR(R[-6]C*RC[-2]/R[-6]C[-8]),"""",R[-6]C*RC[-2]/R[-6]C[-8])"
Range("K12").FormulaR1C1 = _
"=IF(ISERROR(R[-7]C*RC[-2]/R[-7]C[-8]),"""",R[-7]C*RC[-2]/R[-7]C[-8])"
Range("K13").FormulaR1C1 = _
"=IF(ISERROR(R[-8]C*RC[-2]/R[-8]C[-8]),"""",R[-8]C*RC[-2]/R[-8]C[-8])"
Range("K14").FormulaR1C1 = _
"=IF(ISERROR(R[-9]C*RC[-2]/R[-9]C[-8]),"""",R[-9]C*RC[-2]/R[-9]C[-8])"
Range("K15").FormulaR1C1 = _
"=IF(ISERROR(R[-10]C*RC[-2]/R[-10]C[-8]),"""",R[-10]C*RC[-2]/R[-10]C[-8])"
Range("K16").FormulaR1C1 = _
"=IF(ISERROR(R[-11]C*RC[-2]/R[-11]C[-8]),"""",R[-11]C*RC[-2]/R[-11]C[-8])"
Range("K17").FormulaR1C1 = _
"=IF(ISERROR(R[-12]C*RC[-2]/R[-12]C[-8]),"""",R[-12]C*RC[-2]/R[-12]C[-8])"
Range("K18").FormulaR1C1 = _
"=IF(ISERROR(R[-13]C*RC[-2]/R[-13]C[-8]),"""",R[-13]C*RC[-2]/R[-13]C[-8])"
Range("K19").FormulaR1C1 = _
"=IF(ISERROR(R[-14]C*RC[-2]/R[-14]C[-8]),"""",R[-14]C*RC[-2]/R[-14]C[-8])"
Range("K20").FormulaR1C1 = _
"=IF(ISERROR(R[-15]C*RC[-2]/R[-15]C[-8]),"""",R[-15]C*RC[-2]/R[-15]C[-8])"
Range("K21").FormulaR1C1 = _
"=IF(ISERROR(R[-16]C*RC[-2]/R[-16]C[-8]),"""",R[-16]C*RC[-2]/R[-16]C[-8])"
Range("K22").FormulaR1C1 = _
"=IF(ISERROR(R[-17]C*RC[-2]/R[-17]C[-8]),"""",R[-17]C*RC[-2]/R[-17]C[-8])"
Range("K23").FormulaR1C1 = _
"=IF(ISERROR(R[-18]C*RC[-2]/R[-18]C[-8]),"""",R[-18]C*RC[-2]/R[-18]C[-8])"
Range("K24").FormulaR1C1 = _
"=IF(ISERROR(R[-19]C*RC[-2]/R[-19]C[-8]),"""",R[-19]C*RC[-2]/R[-19]C[-8])"
Range("K25").FormulaR1C1 = _
"=IF(ISERROR(RC[-8]*R[-20]C/R[-20]C[-8]),"""",RC[-8]*R[-20]C/R[-20]C[-8])"
Range("K28").FormulaR1C1 = _
"=IF(ISERROR(RC[-3]*R[-23]C[-8]/RC[-6]),"""",RC[-3]*R[-23]C[-8]/RC[-6])"
Range("L3").FormulaR1C1 = "Value21"
Range("L4").FormulaR1C1 = "Value28"
Range("L5").FormulaR1C1 = "=IF(ISERROR(RC[-1]/RC[-1]),"""",RC[-1]/RC[-1])"
Range("L6").FormulaR1C1 = _
"=IF(ISERROR(RC[-1]/R[-1]C[-1]),"""",RC[-1]/R[-1]C[-1])"
Range("L7").FormulaR1C1 = _
"=IF(ISERROR(RC[-1]/R[-2]C[-1]),"""",RC[-1]/R[-2]C[-1])"
Range("L8").FormulaR1C1 = _
"=IF(ISERROR(RC[-1]/R[-3]C[-1]),"""",RC[-1]/R[-3]C[-1])"
Range("L9").FormulaR1C1 = _
"=IF(ISERROR(RC[-1]/R[-4]C[-1]),"""",RC[-1]/R[-4]C[-1])"
Range("L10").FormulaR1C1 = _
"=IF(ISERROR(RC[-1]/R[-5]C[-1]),"""",RC[-1]/R[-5]C[-1])"
Range("L11").FormulaR1C1 = _
"=IF(ISERROR(RC[-1]/R[-6]C[-1]),"""",RC[-1]/R[-6]C[-1])"
Range("L12").FormulaR1C1 = _
"=IF(ISERROR(RC[-1]/R[-7]C[-1]),"""",RC[-1]/R[-7]C[-1])"
Range("L13").FormulaR1C1 = _
"=IF(ISERROR(RC[-1]/R[-8]C[-1]),"""",RC[-1]/R[-8]C[-1])"
Range("L14").FormulaR1C1 = _
"=IF(ISERROR(RC[-1]/R[-9]C[-1]),"""",RC[-1]/R[-9]C[-1])"
Range("L15").FormulaR1C1 = _
"=IF(ISERROR(RC[-1]/R[-10]C[-1]),"""",RC[-1]/R[-10]C[-1])"
Range("L16").FormulaR1C1 = _
"=IF(ISERROR(RC[-1]/R[-11]C[-1]),"""",RC[-1]/R[-11]C[-1])"
Range("L17").FormulaR1C1 = _
"=IF(ISERROR(RC[-1]/R[-12]C[-1]),"""",RC[-1]/R[-12]C[-1])"
Range("L18").FormulaR1C1 = _
"=IF(ISERROR(RC[-1]/R[-13]C[-1]),"""",RC[-1]/R[-13]C[-1])"
Range("L19").FormulaR1C1 = _
"=IF(ISERROR(RC[-1]/R[-14]C[-1]),"""",RC[-1]/R[-14]C[-1])"
Range("L20").FormulaR1C1 = _
"=IF(ISERROR(RC[-1]/R[-15]C[-1]),"""",RC[-1]/R[-15]C[-1])"
Range("L21").FormulaR1C1 = _
"=IF(ISERROR(RC[-1]/R[-16]C[-1]),"""",RC[-1]/R[-16]C[-1])"
Range("L22").FormulaR1C1 = _
"=IF(ISERROR(RC[-1]/R[-17]C[-1]),"""",RC[-1]/R[-17]C[-1])"
Range("L23").FormulaR1C1 = _
"=IF(ISERROR(RC[-1]/R[-18]C[-1]),"""",RC[-1]/R[-18]C[-1])"
Range("L24").FormulaR1C1 = _
"=IF(ISERROR(RC[-1]/R[-19]C[-1]),"""",RC[-1]/R[-19]C[-1])"
Range("M3").FormulaR1C1 = "Value22"
Range("M4").FormulaR1C1 = "Value23"
Range("M25").FormulaR1C1 = "Value24"
Range("M28").FormulaR1C1 = "Value25"
Range("N3").FormulaR1C1 = "Value26"
Range("N4").FormulaR1C1 = "Value27"
Range("N5").FormulaR1C1 = "=IF(ISERROR(RC[-1]*RC[-3]),"""",RC[-1]*RC[-3])"
Range("N6").FormulaR1C1 = "=IF(ISERROR(RC[-1]*RC[-3]),"""",RC[-1]*RC[-3])"
Range("N7").FormulaR1C1 = "=IF(ISERROR(RC[-1]*RC[-3]),"""",RC[-1]*RC[-3])"
Range("N8").FormulaR1C1 = "=IF(ISERROR(RC[-1]*RC[-3]),"""",RC[-1]*RC[-3])"
Range("N9").FormulaR1C1 = "=IF(ISERROR(RC[-1]*RC[-3]),"""",RC[-1]*RC[-3])"
Range("N10").FormulaR1C1 = "=IF(ISERROR(RC[-1]*RC[-3]),"""",RC[-1]*RC[-3])"
Range("N11").FormulaR1C1 = "=IF(ISERROR(RC[-1]*RC[-3]),"""",RC[-1]*RC[-3])"
Range("N12").FormulaR1C1 = "=IF(ISERROR(RC[-1]*RC[-3]),"""",RC[-1]*RC[-3])"
Range("N13").FormulaR1C1 = "=IF(ISERROR(RC[-1]*RC[-3]),"""",RC[-1]*RC[-3])"
Range("N14").FormulaR1C1 = "=IF(ISERROR(RC[-1]*RC[-3]),"""",RC[-1]*RC[-3])"
Range("N15").FormulaR1C1 = "=IF(ISERROR(RC[-1]*RC[-3]),"""",RC[-1]*RC[-3])"
Range("N16").FormulaR1C1 = "=IF(ISERROR(RC[-1]*RC[-3]),"""",RC[-1]*RC[-3])"
Range("N17").FormulaR1C1 = "=IF(ISERROR(RC[-1]*RC[-3]),"""",RC[-1]*RC[-3])"
Range("N18").FormulaR1C1 = "=IF(ISERROR(RC[-1]*RC[-3]),"""",RC[-1]*RC[-3])"
Range("N19").FormulaR1C1 = "=IF(ISERROR(RC[-1]*RC[-3]),"""",RC[-1]*RC[-3])"
Range("N20").FormulaR1C1 = "=IF(ISERROR(RC[-1]*RC[-3]),"""",RC[-1]*RC[-3])"
Range("N21").FormulaR1C1 = "=IF(ISERROR(RC[-1]*RC[-3]),"""",RC[-1]*RC[-3])"
Range("N22").FormulaR1C1 = "=IF(ISERROR(RC[-1]*RC[-3]),"""",RC[-1]*RC[-3])"
Range("N23").FormulaR1C1 = "=IF(ISERROR(RC[-1]*RC[-3]),"""",RC[-1]*RC[-3])"
Range("N24").FormulaR1C1 = "=IF(ISERROR(RC[-1]*RC[-3]),"""",RC[-1]*RC[-3])"
Range("N25").FormulaR1C1 = _
"=IF(ISERROR(SUM(R[-20]C:R[-1]C)),"""",SUM(R[-20]C:R[-1]C))"
Range("N28").FormulaR1C1 = _
"=IF(ISERROR(R[-3]C[-11]/R[-23]C[-11]),"""",R[-3]C[-11]/R[-23]C[-11])"
Range("A3,A4,A27,B1,B3,B25,B28,C1,C3,C4,C25,C28,D3,D4,D25,D28,E4,F4,F28,G3,G4,H4,I3,I4,I28,J4,K3,K4,L3,L4,M3,M4,N3,N4,N25,M28,M25,N28,K25,K28").Select
Selection.Font.Bold = False
Selection.Font.Bold = True
Range("D3:D24,E4:E24,F4:F24,H4:H24,J4:J24,L3:L24,D28,E28,F28,H28").Select
Selection.Font.ColorIndex = 3
Range("D25").Select
Selection.Font.ColorIndex = 5
Range("A1:B1").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Range("C1:N1").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Range("A3:A4").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("B3:B4").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("D3:D4").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("D3:F3").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Range("G3:H3").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Range("I3:J3").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Range("K3:K4").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("L3:L4").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("M3:M4").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("N3:N4").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("C3:C4").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("A3:N4").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Range("A5:A24").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("C5:C24").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("E5:E24").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("G5:G24").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("I5:I24").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("K5:K24").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("M5:M24").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("N5:N24").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("A25:N28").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("A28:B28").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Range("D28:E28").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Range("F28:H28").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Range("I28:K28").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Range("M28:N28").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Range("A25:B25").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Range("C25").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Range("D25:L25").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Range("M25:N25").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Range("C28").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Range("L28").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Range("A1:N28").Select
With Selection.Interior
.ColorIndex = 2
.Pattern = xlSolid
End With
Range("F4").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Range("G4").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Range("I4").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Range("A1:B1").Select
Range("B1").Activate
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Range("A1").Select
End Sub
 
Sort by date Sort by votes
Are you taking the mickey, or what?

How did you put this code together? By recording it? Why then do you format ten twenty bits of all the ranges, and at the end format the whole table in one? All this border stuff apart from the very last section (A1:N28) is superfluous, because it overlaps or is all ranges that could be put together, but then at the end you have it for for A1:N28 anyway...

Programming is about logic, about order. if you want to record something, think about what you want to record before you switch on the recorder.

Then when you fill a contiguous range with the same formula, you can do that in one line, you don't need to enter it in every cell. So your gazillion line code above becomes this lot simpler code:

Code: 
Option Explicit

Sub Evaluation()
'
    Dim lC As Long
    
    Columns("A:A").ColumnWidth = 3.86
    Columns("B:B").ColumnWidth = 23.14
    Columns("C:C").ColumnWidth = 9.29
    Columns("D:D").ColumnWidth = 7.71
    Columns("E:E").ColumnWidth = 6.29
    Columns("F:F").ColumnWidth = 7
    Columns("G:G").ColumnWidth = 9.14
    Columns("H:H").ColumnWidth = 7.43
    Columns("I:I").ColumnWidth = 13.43
    Columns("J:J").ColumnWidth = 7
    Columns("K:K").ColumnWidth = 11.86
    Columns("L:L").ColumnWidth = 5.86
    Columns("M:M").ColumnWidth = 9.43
    Columns("N:N").ColumnWidth = 8.57
    Range("A3").FormulaR1C1 = "Sr"
    Range("A4").FormulaR1C1 = "No"
    Range("A5").FormulaR1C1 = "1"
    Range("A5:A24").AutoFill Range("A5:A24"), xlFillValues
    Range("A27").FormulaR1C1 = "Calculations :"
    Range("B1").FormulaR1C1 = "Value1 "
    Range("B3").FormulaR1C1 = "Name"
    Range("B25").FormulaR1C1 = "Value2 : "
    Range("B28").FormulaR1C1 = "Value3"
    Range("C3").FormulaR1C1 = "Value4"
    Range("C4").FormulaR1C1 = "Value5"
    Range("C28").FormulaR1C1 = "=IF(ISERROR(R[-3]C/RC[8]*100),"""",R[-3]C/RC[8]*100)"
    Range("D3").FormulaR1C1 = "Value6 "
    Range("D4").FormulaR1C1 = "Value7"
    Range("D5:D24").FormulaR1C1 = "=IF(ISERROR(RC[-1]/RC[1]),"""",RC[-1]/RC[1])"
    Range("D25").FormulaR1C1 = _
            "For Print, hide red text columns (D,E, F, H, J & L) and empty rows"
    Range("D28").FormulaR1C1 = "Value8"
    Range("E4").FormulaR1C1 = "Value9"
    Range("F4").FormulaR1C1 = "Value10"
    Range("F28").FormulaR1C1 = "Value11"
    Range("G3").FormulaR1C1 = "Value12"
    Range("G4").FormulaR1C1 = "Value13"
    Range("G5:G24").FormulaR1C1 = "=IF(ISERROR(RC[-2]*RC[1]),"""",RC[-2]*RC[1])"
    Range("H4").FormulaR1C1 = "Value14"
    Range("H5:H24").FormulaR1C1 = _
            "=IF(ISERROR(RC[-2]*RC[-4]/100),"""",RC[-2]*RC[-4]/100)"
    Range("I3").FormulaR1C1 = "Value15"
    Range("I4").FormulaR1C1 = "Value16"
    Range("I5:I24").FormulaR1C1 = "=IF(ISERROR(RC[-6]-RC[-2]),"""",RC[-6]-RC[-2])"
    Range("I28").FormulaR1C1 = "Value17"
    Range("J4").FormulaR1C1 = "Value18"
    Range("J5:J24").FormulaR1C1 = "=IF(ISERROR(RC[-6]-RC[-2]),"""",RC[-6]-RC[-2])"
    Range("K3").FormulaR1C1 = "Value19"
    Range("K4").FormulaR1C1 = "Value20"
    Range("K5:K24").FormulaR1C1 = _
            "=IF(ISERROR(RC[-8]/R[20]C[-8]),"""",RC[-8]/R[20]C[-8])"
    Range("K25").FormulaR1C1 = _
            "=IF(ISERROR(RC[-8]*R[-20]C/R[-20]C[-8]),"""",RC[-8]*R[-20]C/R[-20]C[-8])"
    Range("K28").FormulaR1C1 = _
            "=IF(ISERROR(RC[-3]*R[-23]C[-8]/RC[-6]),"""",RC[-3]*R[-23]C[-8]/RC[-6])"
    Range("L3").FormulaR1C1 = "Value21"
    Range("L4").FormulaR1C1 = "Value28"
    Range("L5:L24").FormulaR1C1 = "=IF(ISERROR(RC[-1]/RC[-1]),"""",RC[-1]/RC[-1])"
    Range("M3").FormulaR1C1 = "Value22"
    Range("M4").FormulaR1C1 = "Value23"
    Range("M25").FormulaR1C1 = "Value24"
    Range("M28").FormulaR1C1 = "Value25"
    Range("N3").FormulaR1C1 = "Value26"
    Range("N4").FormulaR1C1 = "Value27"
    Range("N5:N24").FormulaR1C1 = "=IF(ISERROR(RC[-1]*RC[-3]),"""",RC[-1]*RC[-3])"
    Range("N25").FormulaR1C1 = _
            "=IF(ISERROR(SUM(R[-20]C:R[-1]C)),"""",SUM(R[-20]C:R[-1]C))"
    Range("N28").FormulaR1C1 = _
            "=IF(ISERROR(R[-3]C[-11]/R[-23]C[-11]),"""",R[-3]C[-11]/R[-23]C[-11])"
    With Range("A3,A4,A27,B1,B3,B25,B28,C1,C3,C4,C25,C28,D3,D4,D25,D28,E4,F4,F28,G3," & _
            "G4,H4,I3,I4,I28,J4,K3,K4,L3,L4,M3,M4,N3,N4,N25,M28,M 25,N28,K25,K28")
        .Font.Bold = True
    End With
    With Range("D3:D24,E4:E24,F4:F24,H4:H24,J4:J24,L3:L24,D28,E28,F28,H28")
        .Font.ColorIndex = 3
    End With
    Range("D25").Font.ColorIndex = 5
    With Range("A1:N28")
        With .Interior
        .ColorIndex = 2
        .Pattern = xlSolid
        End With
        .Borders(xlDiagonalDown).LineStyle = xlNone
        .Borders(xlDiagonalUp).LineStyle = xlNone
        With .Borders(xlEdgeLeft)
            .LineStyle = xlContinuous
            .Weight = xlMedium
            .ColorIndex = xlAutomatic
        End With
        With .Borders(xlEdgeTop)
            .LineStyle = xlContinuous
            .Weight = xlMedium
            .ColorIndex = xlAutomatic
        End With
        With .Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .Weight = xlMedium
            .ColorIndex = xlAutomatic
        End With
        With .Borders(xlEdgeRight)
            .LineStyle = xlContinuous
            .Weight = xlMedium
            .ColorIndex = xlAutomatic
        End With
        .Borders(xlInsideVertical).LineStyle = xlNone
    End With


End Sub

Also, please, put your code in code tags see below.

OK, now you will have to specify again what it is you want to do with making this code more flexible. Please explain logically, like this is for the first table but I want to do the same for tables with x,y,z etc. on different sheets, same sheets.
 
Upvote 0
sijpie said:
Are you taking the mickey, or what?

How did you put this code together? By recording it? Why then do you format ten twenty bits of all the ranges, and at the end format the whole table in one? All this border stuff apart from the very last section (A1:N28) is superfluous, because it overlaps or is all ranges that could be put together, but then at the end you have it for for A1:N28 anyway...

Programming is about logic, about order. if you want to record something, think about what you want to record before you switch on the recorder.

Then when you fill a contiguous range with the same formula, you can do that in one line, you don't need to enter it in every cell. So your gazillion line code above becomes this lot simpler code:

Code: 
Option Explicit

Sub Evaluation()
'
    Dim lC As Long
    
    Columns("A:A").ColumnWidth = 3.86
    Columns("B:B").ColumnWidth = 23.14
    Columns("C:C").ColumnWidth = 9.29
    Columns("D:D").ColumnWidth = 7.71
    Columns("E:E").ColumnWidth = 6.29
    Columns("F:F").ColumnWidth = 7
    Columns("G:G").ColumnWidth = 9.14
    Columns("H:H").ColumnWidth = 7.43
    Columns("I:I").ColumnWidth = 13.43
    Columns("J:J").ColumnWidth = 7
    Columns("K:K").ColumnWidth = 11.86
    Columns("L:L").ColumnWidth = 5.86
    Columns("M:M").ColumnWidth = 9.43
    Columns("N:N").ColumnWidth = 8.57
    Range("A3").FormulaR1C1 = "Sr"
    Range("A4").FormulaR1C1 = "No"
    Range("A5").FormulaR1C1 = "1"
    Range("A5:A24").AutoFill Range("A5:A24"), xlFillValues
    Range("A27").FormulaR1C1 = "Calculations :"
    Range("B1").FormulaR1C1 = "Value1 "
    Range("B3").FormulaR1C1 = "Name"
    Range("B25").FormulaR1C1 = "Value2 : "
    Range("B28").FormulaR1C1 = "Value3"
    Range("C3").FormulaR1C1 = "Value4"
    Range("C4").FormulaR1C1 = "Value5"
    Range("C28").FormulaR1C1 = "=IF(ISERROR(R[-3]C/RC[8]*100),"""",R[-3]C/RC[8]*100)"
    Range("D3").FormulaR1C1 = "Value6 "
    Range("D4").FormulaR1C1 = "Value7"
    Range("D5:D24").FormulaR1C1 = "=IF(ISERROR(RC[-1]/RC[1]),"""",RC[-1]/RC[1])"
    Range("D25").FormulaR1C1 = _
            "For Print, hide red text columns (D,E, F, H, J & L) and empty rows"
    Range("D28").FormulaR1C1 = "Value8"
    Range("E4").FormulaR1C1 = "Value9"
    Range("F4").FormulaR1C1 = "Value10"
    Range("F28").FormulaR1C1 = "Value11"
    Range("G3").FormulaR1C1 = "Value12"
    Range("G4").FormulaR1C1 = "Value13"
    Range("G5:G24").FormulaR1C1 = "=IF(ISERROR(RC[-2]*RC[1]),"""",RC[-2]*RC[1])"
    Range("H4").FormulaR1C1 = "Value14"
    Range("H5:H24").FormulaR1C1 = _
            "=IF(ISERROR(RC[-2]*RC[-4]/100),"""",RC[-2]*RC[-4]/100)"
    Range("I3").FormulaR1C1 = "Value15"
    Range("I4").FormulaR1C1 = "Value16"
    Range("I5:I24").FormulaR1C1 = "=IF(ISERROR(RC[-6]-RC[-2]),"""",RC[-6]-RC[-2])"
    Range("I28").FormulaR1C1 = "Value17"
    Range("J4").FormulaR1C1 = "Value18"
    Range("J5:J24").FormulaR1C1 = "=IF(ISERROR(RC[-6]-RC[-2]),"""",RC[-6]-RC[-2])"
    Range("K3").FormulaR1C1 = "Value19"
    Range("K4").FormulaR1C1 = "Value20"
    Range("K5:K24").FormulaR1C1 = _
            "=IF(ISERROR(RC[-8]/R[20]C[-8]),"""",RC[-8]/R[20]C[-8])"
    Range("K25").FormulaR1C1 = _
            "=IF(ISERROR(RC[-8]*R[-20]C/R[-20]C[-8]),"""",RC[-8]*R[-20]C/R[-20]C[-8])"
    Range("K28").FormulaR1C1 = _
            "=IF(ISERROR(RC[-3]*R[-23]C[-8]/RC[-6]),"""",RC[-3]*R[-23]C[-8]/RC[-6])"
    Range("L3").FormulaR1C1 = "Value21"
    Range("L4").FormulaR1C1 = "Value28"
    Range("L5:L24").FormulaR1C1 = "=IF(ISERROR(RC[-1]/RC[-1]),"""",RC[-1]/RC[-1])"
    Range("M3").FormulaR1C1 = "Value22"
    Range("M4").FormulaR1C1 = "Value23"
    Range("M25").FormulaR1C1 = "Value24"
    Range("M28").FormulaR1C1 = "Value25"
    Range("N3").FormulaR1C1 = "Value26"
    Range("N4").FormulaR1C1 = "Value27"
    Range("N5:N24").FormulaR1C1 = "=IF(ISERROR(RC[-1]*RC[-3]),"""",RC[-1]*RC[-3])"
    Range("N25").FormulaR1C1 = _
            "=IF(ISERROR(SUM(R[-20]C:R[-1]C)),"""",SUM(R[-20]C:R[-1]C))"
    Range("N28").FormulaR1C1 = _
            "=IF(ISERROR(R[-3]C[-11]/R[-23]C[-11]),"""",R[-3]C[-11]/R[-23]C[-11])"
    With Range("A3,A4,A27,B1,B3,B25,B28,C1,C3,C4,C25,C28,D3,D4,D25,D28,E4,F4,F28,G3," & _
            "G4,H4,I3,I4,I28,J4,K3,K4,L3,L4,M3,M4,N3,N4,N25,M28,M 25,N28,K25,K28")
        .Font.Bold = True
    End With
    With Range("D3:D24,E4:E24,F4:F24,H4:H24,J4:J24,L3:L24,D28,E28,F28,H28")
        .Font.ColorIndex = 3
    End With
    Range("D25").Font.ColorIndex = 5
    With Range("A1:N28")
        With .Interior
        .ColorIndex = 2
        .Pattern = xlSolid
        End With
        .Borders(xlDiagonalDown).LineStyle = xlNone
        .Borders(xlDiagonalUp).LineStyle = xlNone
        With .Borders(xlEdgeLeft)
            .LineStyle = xlContinuous
            .Weight = xlMedium
            .ColorIndex = xlAutomatic
        End With
        With .Borders(xlEdgeTop)
            .LineStyle = xlContinuous
            .Weight = xlMedium
            .ColorIndex = xlAutomatic
        End With
        With .Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .Weight = xlMedium
            .ColorIndex = xlAutomatic
        End With
        With .Borders(xlEdgeRight)
            .LineStyle = xlContinuous
            .Weight = xlMedium
            .ColorIndex = xlAutomatic
        End With
        .Borders(xlInsideVertical).LineStyle = xlNone
    End With


End Sub

Also, please, put your code in code tags see below.

OK, now you will have to specify again what it is you want to do with making this code more flexible. Please explain logically, like this is for the first table but I want to do the same for tables with x,y,z etc. on different sheets, same sheets.
Click to expand...

<!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><!--[if !mso]>******** classid="clsid:38481807-CA0E-42D2-BF39-B33AF135CC4D" id=ieooui></object> <style> st1\:*{behavior:url(#ieooui) } </style> <![endif]--><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} </style> <![endif]--> Thank you for going through the whole recorded code.
I don’t know much about code.
Now the problems associated with the code you have given are:


It gives run time error ‘1004’:

Autofill method of range class failed.

“Range("A5:A24").AutoFill Range("A5:A24"), xlFillValues” this is problematic line

Then I modified as;

Range("A5").FormulaR1C1 = "1"
Range("A6").FormulaR1C1 = "2"
Range("A5:A6").Select
Selection.AutoFill Destination:=Range("A5:A24"), Type:=xlFillDefault

The modification worked for me.

Then again problem occurred.

Mesaage is
“ Run-time error ‘1004’: Method ‘Range’ of object ‘_Global’ failed “

Then I have removed the part (see below) which is giving error.

With Range("A3,A4,A27,B1,B3,B25,B28,C1,C3,C4,C25,C28,D3,D4,D25,D28,E4,F4,F28,G3," & _
"G4,H4,I3,I4,I28,J4,K3,K4,L3,L4,M3,M4,N3,N4,N25,M28,M 25,N28,K25,K28")
.Font.Bold = True

Now it is working, but formula in K & L columns are different one that I have in my original recording.

Thanks.
 
Upvote 0
OK. Yes you solved the autofill correctly

The range error (to turn off bold) is probably because there is a space between M 25 in the ranges given, see if removing that solves it
Rich (BB code): 
With Range("A3,A4,A27,B1,B3,B25,B28,C1,C3,C4,C25,C28,D3,D4,D25,D28,E4,F4,F28,G3," & _
"G4,H4,I3,I4,I28,J4,K3,K4,L3,L4,M3,M4,N3,N4,N25,M28,M 25,N28,K25,K28")
.Font.Bold = True
The formula errors for K & L is my mistake. This sections should be:

Rich (BB code): 
Range("K5").FormulaR1C1 = _
            "=IF(ISERROR(RC[-8]/R[20]C[-8]),"""",RC[-8]/R[20]C[-8])"
Range("K6:K24").FormulaR1C1 = _
"=IF(ISERROR(R[-1]C*RC[-2]/R5C[-8]),"""",R5C*RC[-2]/R5C[-8])"
for K
and
Rich (BB code): 
Range("L5").FormulaR1C1 = "=IF(ISERROR(RC[-1]/RC[-1]),"""",RC[-1]/RC[-1])"
Range("L6:L24").FormulaR1C1 = _
"=IF(ISERROR(RC[-1]/R5C[-1]),"""",RC[-1]/R5C[-1])"
for L
 
Upvote 0
sijpie said:
OK. Yes you solved the autofill correctly

The range error (to turn off bold) is probably because there is a space between M 25 in the ranges given, see if removing that solves it
Rich (BB code): 
With Range("A3,A4,A27,B1,B3,B25,B28,C1,C3,C4,C25,C28,D3,D4,D25,D28,E4,F4,F28,G3," & _
"G4,H4,I3,I4,I28,J4,K3,K4,L3,L4,M3,M4,N3,N4,N25,M28,M 25,N28,K25,K28")
.Font.Bold = True
The formula errors for K & L is my mistake. This sections should be:

Rich (BB code): 
Range("K5").FormulaR1C1 = _
            "=IF(ISERROR(RC[-8]/R[20]C[-8]),"""",RC[-8]/R[20]C[-8])"
Range("K6:K24").FormulaR1C1 = _
"=IF(ISERROR(R[-1]C*RC[-2]/R5C[-8]),"""",R5C*RC[-2]/R5C[-8])"
for K
and
Rich (BB code): 
Range("L5").FormulaR1C1 = "=IF(ISERROR(RC[-1]/RC[-1]),"""",RC[-1]/RC[-1])"
Range("L6:L24").FormulaR1C1 = _
"=IF(ISERROR(RC[-1]/R5C[-1]),"""",RC[-1]/R5C[-1])"
for L
Click to expand...

Thank you very much, all correction worked.
great!!!!!!!!!

thanks again.

Still the inside borders are remaining.
can it be solved?
 
Upvote 0
I need to look at it, but this week no access to PC so it ll have to wait
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Solved
formating question
Replies
3
Views
356
Micron
M
L
  • Locked
  • Question Question
Help Cleaning Up Code
Replies
2
Views
682
RoryA
RoryA
P
  • Question Question
Issue with button not running in spread sheet only in test mode
Replies
1
Views
397
6StringJazzer
6StringJazzer
K
  • Solved
create a button that do actions
2 3
Replies
22
Views
1K
kerm007
K
A
  • Solved
How use RC formulas when fill three columns
2
Replies
18
Views
444
MARK858
MARK858

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top