Attribute VB_Name = "Raw_Complier" Sub Charleston_Attic_Compiler() Attribute Charleston_Attic_Compiler.VB_ProcData.VB_Invoke_Func = "m\n14" Application.ScreenUpdating = False '*************************************************************************************************** '****This Macro opens The neccessary ATTIC data files and compiles them into weekly spreadsheets**** '*************************************************************************************************** 'Macro Activated by User Input: Ctrl+M 'Macro recorded 8/23/2010 by Stan Atherton 'Updated 8/24 Dim dir As String, savdir As String Dim Drive As String Dim TS(1 To 3) As String, Sheetname(1 To 7) As String TS(1) = "C6:E185" 'TIMESTAMP Dim template(1) As String Dim date_start As String, date_end As String Dim subfolder(1 To 7) As String, File(1 To 7) As String, MicroLogger(1 To 7) As String Dim Weatherlogger(1 To 6) As String, wsubfolder(1 To 6) As String Dim nCol(1 To 7) As String, nACol(1 To 111) As String, wsheetname(1 To 3) As String Dim nDAS(5) Windows("CHARL_RAW_CompilerNEW.xlsm").Activate Drive = Range("C21").Value date_start = Range("C15").Value date_end = Range("C18").Value ' MicroLogger(1) = "1_105" MicroLogger(2) = "2_200" MicroLogger(3) = "2_202" MicroLogger(4) = "3_301" MicroLogger(5) = "4_401" MicroLogger(6) = "4_402" MicroLogger(7) = "5_501" ' Weatherlogger(1) = "1_105" Weatherlogger(2) = "2_200" Weatherlogger(3) = "2_202" Weatherlogger(4) = "3_300" Weatherlogger(5) = "4_401" Weatherlogger(6) = "5_501" ' wsubfolder(1) = "CHARL1\" wsubfolder(2) = "CHARL2\" wsubfolder(3) = "CHARL2\" wsubfolder(4) = "CHARL3\" wsubfolder(5) = "CHARL4\" wsubfolder(6) = "CHARL5\" ' wsheetname(1) = "Weather" wsheetname(2) = "Temp_RH" wsheetname(3) = "RH8" ' subfolder(1) = "CHARL1\" subfolder(2) = "CHARL2\" subfolder(3) = "CHARL2\" subfolder(4) = "CHARL3\" subfolder(5) = "CHARL4\" subfolder(6) = "CHARL4\" subfolder(7) = "CHARL5\" nDAS(1) = 1 nDAS(2) = 2 nDAS(3) = 1 nDAS(4) = 2 nDAS(5) = 1 ' Sheetname(1) = "Attic1" Sheetname(2) = "Attic2" Sheetname(3) = "Attic3" Sheetname(4) = "Attic4" Sheetname(5) = "Attic5" Sheetname(6) = "Attic6" Sheetname(7) = "Attic7" ' nCol(1) = 28 nCol(2) = 28 nCol(3) = 28 nCol(4) = 28 nCol(5) = 31 nCol(6) = 28 nCol(7) = 28 ' nACol(1) = "E7" nACol(2) = "F7" nACol(3) = "G7" nACol(4) = "H7" nACol(5) = "I7" nACol(6) = "J7" nACol(7) = "K7" nACol(8) = "L7" nACol(9) = "M7" nACol(10) = "N7" nACol(11) = "O7" nACol(12) = "P7" nACol(13) = "Q7" nACol(14) = "R7" nACol(15) = "S7" nACol(16) = "T7" nACol(17) = "U7" nACol(18) = "V7" nACol(19) = "W7" nACol(20) = "X7" nACol(21) = "Y7" nACol(22) = "Z7" nACol(23) = "AA7" nACol(24) = "AB7" nACol(25) = "AC7" nACol(26) = "AD7" nACol(27) = "AE7" nACol(28) = "AF7" nACol(29) = "AG7" nACol(30) = "AH7" nACol(31) = "AI7" nACol(32) = "W7" nACol(33) = "X7" nACol(34) = "Y7" savdir = Drive & ":\Users\Owner\Documents\SAAtherton\Charleston_Field_Tests\Reports\" dir = Drive & ":\Users\Owner\Documents\SAAtherton\Charleston_Field_Tests\Raw_Data\" Call CHARL_Arrays For date_index = date_start To date_end 'Range input into cells C15 and C18 in spreadsheet Workbooks.Open Filename:=dir & "GAF_Attics_template.xls" Windows("GAF_Attics_template.xls").Activate Worksheets("Attic1").Activate Range("E7:AF750").ClearContents Worksheets("Attic2").Activate Range("E7:AF750").ClearContents Worksheets("Attic3").Activate Range("E7:AF750").ClearContents Worksheets("Attic4").Activate Range("E7:AF750").ClearContents Worksheets("Attic5").Activate Range("E7:AI750").ClearContents Worksheets("Attic6").Activate Range("E7:AF750").ClearContents Worksheets("Attic7").Activate Range("E7:AF750").ClearContents Worksheets("RH8").Activate Range("E7:AF750").ClearContents Worksheets("Weather").Activate Range("E7:AG750").ClearContents Worksheets("Temp_RH").Activate Range("E7:N750").ClearContents Range("T7:V750").ClearContents Worksheets("Attic1").Activate Range("A3") = Week(date_index) Range("E7").Select Worksheets("Attic2").Activate Range("A3") = Week(date_index) Range("E7").Select Worksheets("Attic3").Activate Range("A3") = Week(date_index) Range("E7").Select Worksheets("Attic4").Activate Range("A3") = Week(date_index) Range("E7").Select Worksheets("Attic5").Activate Range("A3") = Week(date_index) Range("E7").Select Worksheets("Attic6").Activate Range("A3") = Week(date_index) Range("E7").Select Worksheets("Attic7").Activate Range("A3") = Week(date_index) Range("E7").Select Worksheets("RH8").Activate Range("A3") = Week(date_index) Range("E7").Select Range("A3") = Week(date_index) Worksheets("Weather").Activate Range("A3") = Week(date_index) Range("E7").Select Worksheets("Temp_RH").Activate Range("E7").Select Range("A3") = Week(date_index) template(1) = dir & "GAF_Attics_template.xls" For i = 1 To 7 'This loop opens neccessary data files for the given date '''''file(i) = "CHARL" & MicroLogger(i) & "_" & Week(date_index) & ".RPT" File(i) = "CHARL" & MicroLogger(i) & "_" & Week(date_index) & ".xls" Workbooks.Open Filename:=dir & subfolder(i) & File(i) Next i For TStamp = 1 To 7 'This loop applies the timestamp to the output template Windows(File(TStamp)).Activate Range(TS(1)).Select Selection.Copy Windows("GAF_Attics_template.xls").Activate Worksheets(Sheetname(TStamp)).Activate Range("B7").Select ActiveSheet.Paste Application.CutCopyMode = False Next TStamp For k = 1 To 7 'This set of If/Then commands and internal loops selects/copies/pastes the specific columns from the 'Raw data to the output template using a GoTo structure. '1 =1_105 '2 =2_200 '3 =2_202 '4 =3_301 '5 =4_401 '6 =4_402 '7 =5_501 If k = 1 Then GoTo 10 If k = 2 Then GoTo 20 If k = 3 Then GoTo 30 If k = 4 Then GoTo 40 If k = 5 Then GoTo 50 If k = 6 Then GoTo 60 If k = 7 Then GoTo 70 ''''''''''' 10 'Pulls Data from DAS 1_105 For l = 17 To 18 Windows(File(k)).Activate 'Activates 1_105 Range(AtticCol(5, l)).Select Selection.Copy Windows("GAF_Attics_template.xls").Activate Worksheets(Sheetname(5)).Activate Range(nACol(l)).Select ActiveSheet.Paste Application.CutCopyMode = False Next l For m = 1 To 17 Windows(File(k)).Activate Range(AtticCol(3, m)).Select Selection.Copy Windows("GAF_Attics_template.xls").Activate Worksheets(Sheetname(3)).Activate Range(nACol(m)).Select ActiveSheet.Paste Application.CutCopyMode = False Next m Workbooks(File(k)).Close GoTo 80 ''''''''''' 20 'Pulls Data from DAS 2_200 For nn = 1 To 4 'Attics 1 to 4 For nnn = 26 To 28 Windows(File(k)).Activate Range(AtticCol(nn, nnn)).Select Selection.Copy Windows("GAF_Attics_template.xls").Activate Worksheets(Sheetname(nn)).Activate Range(nACol(nnn)).Select ActiveSheet.Paste Application.CutCopyMode = False Next nnn Next nn For nnnn = 29 To 31 'Attic 5 Windows(File(k)).Activate Range(AtticCol(5, nnnn)).Select Selection.Copy Windows("GAF_Attics_template.xls").Activate Worksheets(Sheetname(5)).Activate Range(nACol(nnnn)).Select ActiveSheet.Paste Application.CutCopyMode = False Next nnnn For oo = 6 To 7 For ooo = 26 To 28 'Attic 6 and 7 Windows(File(k)).Activate Range(AtticCol(oo, ooo)).Select Selection.Copy Windows("GAF_Attics_template.xls").Activate Worksheets(Sheetname(oo)).Activate Range(nACol(ooo)).Select ActiveSheet.Paste Application.CutCopyMode = False Next ooo Next oo Workbooks(File(k)).Close GoTo 80 ''''''''''' 30 'Pulls Data from DAS 2_202 For p = 3 To 4 For o = 18 To 23 Windows(File(k)).Activate Range(AtticCol(p, o)).Select Selection.Copy Windows("GAF_Attics_template.xls").Activate Worksheets(Sheetname(p)).Activate Range(nACol(o)).Select ActiveSheet.Paste Application.CutCopyMode = False Next o Next p For q = 19 To 26 Windows(File(k)).Activate Range(AtticCol(5, q)).Select Selection.Copy Windows("GAF_Attics_template.xls").Activate Worksheets(Sheetname(5)).Activate Range(nACol(q)).Select ActiveSheet.Paste Application.CutCopyMode = False Next q For R = 27 To 28 Windows(File(k)).Activate Range(AtticCol(5, R)).Select Selection.Copy Windows("GAF_Attics_template.xls").Activate Worksheets(Sheetname(5)).Activate Range(nACol(R)).Select ActiveSheet.Paste Application.CutCopyMode = False Next R Workbooks(File(k)).Close GoTo 80 ''''''''''' 40 'Pulls Data from DAS 3_301 For S = 1 To 16 Windows(File(k)).Activate Range(AtticCol(4, S)).Select Selection.Copy Windows("GAF_Attics_template.xls").Activate Worksheets(Sheetname(4)).Activate Range(nACol(S)).Select ActiveSheet.Paste Application.CutCopyMode = False Next S For u = 5 To 6 For t = 1 To 10 Windows(File(k)).Activate Range(AtticCol(u, t)).Select Selection.Copy Windows("GAF_Attics_template.xls").Activate Worksheets(Sheetname(u)).Activate Range(nACol(t)).Select ActiveSheet.Paste Application.CutCopyMode = False Next t Next u Workbooks(File(k)).Close GoTo 80 ''''''''''' 50 'Pulls Data from DAS 4_401 For v = 1 To 16 Windows(File(k)).Activate Range(AtticCol(2, v)).Select Selection.Copy Windows("GAF_Attics_template.xls").Activate Worksheets(Sheetname(2)).Activate Range(nACol(v)).Select ActiveSheet.Paste Application.CutCopyMode = False Next v Workbooks(File(k)).Close GoTo 80 ''''''''' 60 'Pulls Data from DAS 4_402 For W = 1 To 16 Windows(File(k)).Activate Range(AtticCol(1, W)).Select Selection.Copy Windows("GAF_Attics_template.xls").Activate Worksheets(Sheetname(1)).Activate Range(nACol(W)).Select ActiveSheet.Paste Application.CutCopyMode = False Next W For y = 5 To 6 For X = 11 To 16 Windows(File(k)).Activate Range(AtticCol(y, X)).Select Selection.Copy Windows("GAF_Attics_template.xls").Activate Worksheets(Sheetname(y)).Activate Range(nACol(X)).Select ActiveSheet.Paste Application.CutCopyMode = False Next X Next y For Z = 1 To 16 Windows(File(k)).Activate Range(AtticCol(7, Z)).Select Selection.Copy Windows("GAF_Attics_template.xls").Activate Worksheets(Sheetname(7)).Activate Range(nACol(Z)).Select ActiveSheet.Paste Application.CutCopyMode = False Next Z Workbooks(File(k)).Close GoTo 80 ''''''''''' 70 'Pulls Data from DAS 5_501 For a = 18 To 25 Windows(File(k)).Activate Range(AtticCol(1, a)).Select Selection.Copy Windows("GAF_Attics_template.xls").Activate Worksheets(Sheetname(1)).Activate Range(nACol(a)).Select ActiveSheet.Paste Application.CutCopyMode = False Next a For c = 2 To 4 For b = 24 To 25 Windows(File(k)).Activate Range(AtticCol(c, b)).Select Selection.Copy Windows("GAF_Attics_template.xls").Activate Worksheets(Sheetname(c)).Activate Range(nACol(b)).Select ActiveSheet.Paste Application.CutCopyMode = False Next b Next c For d = 18 To 23 Windows(File(k)).Activate Range(AtticCol(6, d)).Select Selection.Copy Windows("GAF_Attics_template.xls").Activate Worksheets(Sheetname(6)).Activate Range(nACol(d)).Select ActiveSheet.Paste Application.CutCopyMode = False Next d For e = 24 To 25 Windows(File(k)).Activate Range(AtticCol(6, e)).Select Selection.Copy Windows("GAF_Attics_template.xls").Activate Worksheets(Sheetname(6)).Activate Range(nACol(e)).Select ActiveSheet.Paste Application.CutCopyMode = False Next e For ee = 18 To 25 Windows(File(k)).Activate Range(AtticCol(7, ee)).Select Selection.Copy Windows("GAF_Attics_template.xls").Activate Worksheets(Sheetname(7)).Activate Range(nACol(ee)).Select ActiveSheet.Paste Application.CutCopyMode = False Next ee For ef = 18 To 23 Windows(File(k)).Activate Range(AtticCol(2, ef)).Select Selection.Copy Windows("GAF_Attics_template.xls").Activate Worksheets(Sheetname(2)).Activate Range(nACol(ef)).Select ActiveSheet.Paste Application.CutCopyMode = False Next ef Workbooks(File(k)).Close GoTo 80 ''''''' 80 Next k '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''This part calls in the WEATHER data'''''''''' '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' For i = 1 To 6 'opens the files 'file(i) = "CHARL" & Weatherlogger(i) & "_" & Week(date_index) & ".RPT" File(i) = "CHARL" & Weatherlogger(i) & "_" & Week(date_index) & ".xls" Workbooks.Open Filename:=dir & wsubfolder(i) & File(i) 'Workbooks.OpenText Filename:=dir & wsubfolder(i) & file((i)), Origin:= _ '437, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _ 'ConsecutiveDelimiter:=True, Tab:=False, Semicolon:=False, Comma:=False _ ', Space:=True, Other:=True, OtherChar:="-", FieldInfo:=Array(Array(1, 1) _ ', Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), _ 'Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15 _ ', 1), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array(20, 1), Array(21, 1), _ 'Array(22, 1), Array(23, 1), Array(24, 1), Array(25, 1), Array(26, 1), Array(27, 1), Array( _ '28, 1), Array(29, 1), Array(30, 1), Array(31, 1), Array(32, 1), Array(33, 1), Array(34, 1), _ 'Array(35, 1), Array(36, 1), Array(37, 1), Array(38, 1), Array(39, 1), Array(40, 1), Array( _ '41, 1), Array(42, 1), Array(43, 1), Array(44, 1), Array(45, 1), Array(46, 1), Array(47, 1), _ 'Array(48, 1), Array(49, 1), Array(50, 1), Array(51, 1), Array(52, 1), Array(53, 1), Array( _ '54, 1), Array(55, 1), Array(56, 1), Array(57, 1), Array(58, 1), Array(59, 1), Array(60, 1), _ 'Array(61, 1), Array(62, 1), Array(63, 1), Array(64, 1), Array(65, 1), Array(66, 1), Array( _ '67, 1), Array(68, 1), Array(69, 1)), TrailingMinusNumbers:=True Next i For TStamp = 1 To 3 'applies the timestamp to the template worsksheets Windows(File(TStamp)).Activate Range(TS(1)).Select Selection.Copy Windows("GAF_Attics_template.xls").Activate Worksheets(wsheetname(TStamp)).Activate Range("B7").Select ActiveSheet.Paste Application.CutCopyMode = False Next TStamp For k = 1 To 6 '1 =1_105 '2 =2_200 '3 =2_202 '4 =3_300 '5 =4_401 '6 =5_501 If k = 1 Then GoTo 110 If k = 2 Then GoTo 120 If k = 3 Then GoTo 130 If k = 4 Then GoTo 140 If k = 5 Then GoTo 150 If k = 6 Then GoTo 160 ''''''' 110 'Pulls Data from DAS 1_105 For l = 1 To 4 Windows(File(k)).Activate 'Activates 1_105 Range(WCol(2, l)).Select Selection.Copy Windows("GAF_Attics_template.xls").Activate Worksheets(wsheetname(2)).Activate Range(nACol(l)).Select ActiveSheet.Paste Application.CutCopyMode = False Next l '''''''''added july 2011 For op = 32 To 34 Windows(File(k)).Activate 'Activates 1_105 Range(WCol(2, op)).Select Selection.Copy Windows("GAF_Attics_template.xls").Activate Worksheets(wsheetname(2)).Activate Range(nACol(op)).Select ActiveSheet.Paste Application.CutCopyMode = False Next op Workbooks(File(k)).Close GoTo 180 ''''' 120 'Pulls Data from DAS 2_200 Windows(File(k)).Activate 'Activates 2_200 Range(WCol(1, 29)).Select Selection.Copy Windows("GAF_Attics_template.xls").Activate Worksheets(wsheetname(1)).Activate Range(nACol(29)).Select ActiveSheet.Paste Application.CutCopyMode = False Windows(File(k)).Activate 'Activates 2_200 Range(WCol(2, 18)).Select Selection.Copy Windows("GAF_Attics_template.xls").Activate Worksheets(wsheetname(2)).Activate Range(nACol(18)).Select ActiveSheet.Paste Application.CutCopyMode = False For b = 26 To 28 Windows(File(k)).Activate 'Activates 2_200 Range(WCol(3, b)).Select Selection.Copy Windows("GAF_Attics_template.xls").Activate Worksheets(wsheetname(3)).Activate Range(nACol(b)).Select ActiveSheet.Paste Application.CutCopyMode = False Next b Workbooks(File(k)).Close GoTo 180 '''''' 130 'Pulls Data from DAS 2_202 For a = 5 To 8 Windows(File(k)).Activate 'Activates 2_202 Range(WCol(2, a)).Select Selection.Copy Windows("GAF_Attics_template.xls").Activate Worksheets(wsheetname(2)).Activate Range(nACol(a)).Select ActiveSheet.Paste Application.CutCopyMode = False Next a Workbooks(File(k)).Close GoTo 180 ''''' 140 'Pulls Data from DAS 3_300 For c = 1 To 28 Windows(File(k)).Activate 'Activates 3_300 Range(WCol(1, c)).Select Selection.Copy Windows("GAF_Attics_template.xls").Activate Worksheets(wsheetname(1)).Activate Range(nACol(c)).Select ActiveSheet.Paste Application.CutCopyMode = False Next c For d = 9 To 10 Windows(File(k)).Activate 'Activates 3_300 Range(WCol(2, d)).Select Selection.Copy Windows("GAF_Attics_template.xls").Activate Worksheets(wsheetname(2)).Activate Range(nACol(d)).Select ActiveSheet.Paste Application.CutCopyMode = False Next d For e = 16 To 17 Windows(File(k)).Activate 'Activates 3_300 Range(WCol(2, e)).Select Selection.Copy Windows("GAF_Attics_template.xls").Activate Worksheets(wsheetname(2)).Activate Range(nACol(e)).Select ActiveSheet.Paste Application.CutCopyMode = False Next e Workbooks(File(k)).Close GoTo 180 ''''''' 150 'Pulls Data from DAS 4_401 For F = 1 To 16 Windows(File(k)).Activate 'Activates '4_401 Range(WCol(3, F)).Select Selection.Copy Windows("GAF_Attics_template.xls").Activate Worksheets(wsheetname(3)).Activate Range(nACol(F)).Select ActiveSheet.Paste Application.CutCopyMode = False Next F Workbooks(File(k)).Close GoTo 180 ''''' 160 'Pulls Data from DAS 5_501 For g = 18 To 23 Windows(File(k)).Activate 'Activates '5_501 Range(WCol(3, g)).Select Selection.Copy Windows("GAF_Attics_template.xls").Activate Worksheets(wsheetname(3)).Activate Range(nACol(g)).Select ActiveSheet.Paste Application.CutCopyMode = False Next g For h = 24 To 25 Windows(File(k)).Activate 'Activates '5_501 Range(WCol(3, h)).Select Selection.Copy Windows("GAF_Attics_template.xls").Activate Worksheets(wsheetname(3)).Activate Range(nACol(h)).Select ActiveSheet.Paste Application.CutCopyMode = False Next h Workbooks(File(k)).Close GoTo 180 ''''' 180 Next k Call AtticCalc Windows("GAF_Attics_template.xls").Activate Worksheets("Attic1").Activate Range("E7").Select Worksheets("Attic3").Activate Range("E7").Select Worksheets("Attic4").Activate Range("E7").Select Worksheets("Attic5").Activate Range("E7").Select Worksheets("Attic6").Activate Range("E7").Select Worksheets("Attic7").Activate Range("E7").Select Worksheets("RH8").Activate Range("E7").Select Worksheets("Weather").Activate Range("E7").Select Worksheets("Temp_RH").Activate Range("E7").Select Worksheets("Attic2").Activate Range("E7").Select Call SIMPLIFYS Call FindTheFaulty Call FixitForSudhir ActiveWorkbook.SaveAs Filename:=savdir & "GAF_ATTICS_" & Week(date_index) & ".xls" _ , FileFormat:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _ , CreateBackup:=False ActiveWindow.Close Next date_index Application.ScreenUpdating = True MsgBox "Your mother has finished making me a sandwich, if you catch my drift." End Sub Public AtticCol(1 To 7, 1 To 31) As String, WCol(1 To 3, 1 To 111) As String, Week(1 To 200) As String, PanelCol(1 To 21, 1 To 64) As String Sub CHARL_Arrays() 'Updated 8/24 ' ********************************************************************************************* ' ****This Macro COMPILES All Attic, Panel, and Weather Arrays Used to Read and Write DATA***** ' ********************************************************************************************* ' Macro recorded 8/23/2010 by Stan Atherton '' ATTIC 2 AtticCol(2, 1) = "F6:F172" 'T_4_1 to T_4_17 from 401 AtticCol(2, 2) = "G6:G172" AtticCol(2, 3) = "H6:H172" AtticCol(2, 4) = "I6:I172" AtticCol(2, 5) = "J6:J172" AtticCol(2, 6) = "K6:K172" AtticCol(2, 7) = "L6:L172" AtticCol(2, 8) = "M6:M172" AtticCol(2, 9) = "N6:N172" AtticCol(2, 10) = "O6:O172" AtticCol(2, 11) = "P6:P172" AtticCol(2, 12) = "Q6:Q172" AtticCol(2, 13) = "R6:R172" AtticCol(2, 14) = "S6:S172" AtticCol(2, 15) = "T6:T172" AtticCol(2, 16) = "U6:U172" AtticCol(2, 17) = "V6:V172" 'void TC ' AtticCol(2, 18) = "F6:F172" 'RH_5_1 to RH_5_6 from 501 AtticCol(2, 19) = "G6:G172" AtticCol(2, 20) = "H6:H172" AtticCol(2, 21) = "I6:I172" AtticCol(2, 22) = "J6:J172" AtticCol(2, 23) = "K6:K172" ' AtticCol(2, 24) = "AD6:AD172" 'RH_5_25 to RH_5_26 from 501 AtticCol(2, 25) = "AE6:AE172" ' AtticCol(2, 26) = "N6:N172" 'HFT_2_4 to HFT_2_6 from 200 AtticCol(2, 27) = "O6:O172" AtticCol(2, 28) = "P6:P172" ''ATTIC 3 AtticCol(3, 1) = "AE6:AE172" 'T_1_282 to T_1_298 from 105 AtticCol(3, 2) = "AF6:AF172" AtticCol(3, 3) = "AG6:AG172" AtticCol(3, 4) = "AH6:AH172" AtticCol(3, 5) = "AI6:AI172" AtticCol(3, 6) = "AJ6:AJ172" AtticCol(3, 7) = "AK6:AK172" AtticCol(3, 8) = "AL6:AL172" AtticCol(3, 9) = "AM6:AM172" AtticCol(3, 10) = "AN6:AN172" AtticCol(3, 11) = "AO6:AO172" AtticCol(3, 12) = "AP6:AP172" AtticCol(3, 13) = "AQ6:AQ172" AtticCol(3, 14) = "AR6:AR172" AtticCol(3, 15) = "AS6:AS172" AtticCol(3, 16) = "AT6:AT172" AtticCol(3, 17) = "AU6:AU172" ' AtticCol(3, 18) = "AR6:AR172" 'RH_2_103 to RH_2_108 from 202 AtticCol(3, 19) = "AS6:AS172" AtticCol(3, 20) = "AT6:AT172" AtticCol(3, 21) = "AU6:AU172" AtticCol(3, 22) = "AV6:AV172" AtticCol(3, 23) = "AW6:AW172" ' AtticCol(3, 24) = "AF6:AF172" 'RH_5_27 to RH_5_28 from 501 AtticCol(3, 25) = "AG6:AG172" AtticCol(3, 26) = "Q6:Q172" 'HFT_2_7 to HFT_2_9 from 200 AtticCol(3, 27) = "R6:R172" AtticCol(3, 28) = "S6:S172" ''ATTIC 4 AtticCol(4, 1) = "F6:F172" 'T_3_1 to T_3_16 from 301 AtticCol(4, 2) = "G6:G172" AtticCol(4, 3) = "H6:H172" AtticCol(4, 4) = "I6:I172" AtticCol(4, 5) = "J6:J172" AtticCol(4, 6) = "K6:K172" AtticCol(4, 7) = "L6:L172" AtticCol(4, 8) = "M6:M172" AtticCol(4, 9) = "N6:N172" AtticCol(4, 10) = "O6:O172" AtticCol(4, 11) = "P6:P172" AtticCol(4, 12) = "Q6:Q172" AtticCol(4, 13) = "R6:R172" AtticCol(4, 14) = "S6:S172" AtticCol(4, 15) = "T6:T172" AtticCol(4, 16) = "U6:U172" AtticCol(4, 17) = "V6:V172" 'VOID TC ' AtticCol(4, 18) = "BD6:BD172" 'RH_2_115 to RH_2_120 from 202 AtticCol(4, 19) = "BE6:BE172" AtticCol(4, 20) = "BF6:BF172" AtticCol(4, 21) = "BG6:BG172" AtticCol(4, 22) = "BH6:BH172" AtticCol(4, 23) = "BI6:BI172" ' AtticCol(4, 24) = "AH6:AH172" 'RH_5_29 to RH_5_30 from 501 AtticCol(4, 25) = "AI6:AI172" ' AtticCol(4, 26) = "T6:T172" 'HFT_2_10 to HFT_2_12 from 200 AtticCol(4, 27) = "U6:U172" AtticCol(4, 28) = "V6:V172" ''ATTIC 5 AtticCol(5, 1) = "W6:W172" 'T_3_18 to T_3_27 from 301 AtticCol(5, 2) = "X6:X172" AtticCol(5, 3) = "Y6:Y172" AtticCol(5, 4) = "Z6:Z172" AtticCol(5, 5) = "AA6:AA172" AtticCol(5, 6) = "AB6:AB172" AtticCol(5, 7) = "AC6:AC172" AtticCol(5, 8) = "AD6:AD172" AtticCol(5, 9) = "AE6:AE172" AtticCol(5, 10) = "AF6:AF172" ' AtticCol(5, 11) = "J6:J172" 'T_4_69 to T_4_74 from 402 AtticCol(5, 12) = "K6:K172" AtticCol(5, 13) = "L6:L172" AtticCol(5, 14) = "M6:M172" AtticCol(5, 15) = "N6:N172" AtticCol(5, 16) = "O6:O172" ' AtticCol(5, 17) = "H6:H172" 'T_1_259 to T_1_260 from 105 AtticCol(5, 18) = "I6:I172" ' AtticCol(5, 19) = "BJ6:BJ172" 'RH_2_121 to RH_2_128 from 202 AtticCol(5, 20) = "BK6:BK172" AtticCol(5, 21) = "BL6:BL172" AtticCol(5, 22) = "BM6:BM172" AtticCol(5, 23) = "BN6:BN172" AtticCol(5, 24) = "BO6:BO172" AtticCol(5, 25) = "BP6:BP172" AtticCol(5, 26) = "BQ6:BQ172" ' AtticCol(5, 27) = "AJ6:AJ172" 'RH_2_95 to RH_2_96 from 202 AtticCol(5, 28) = "AK6:AK172" ' AtticCol(5, 29) = "W6:W172" 'HFT_2_13 to HFT_2_15 from 200 AtticCol(5, 30) = "X6:X172" AtticCol(5, 31) = "Y6:Y172" ''ATTIC 6 AtticCol(6, 1) = "BH6:BH172" 'T_3_55 to T_3_64 from 301 AtticCol(6, 2) = "BI6:BI172" AtticCol(6, 3) = "BJ6:BJ172" AtticCol(6, 4) = "BK6:BK172" AtticCol(6, 5) = "BL6:BL172" AtticCol(6, 6) = "BM6:BM172" AtticCol(6, 7) = "BN6:BN172" AtticCol(6, 8) = "BO6:BO172" AtticCol(6, 9) = "BP6:BP172" AtticCol(6, 10) = "BQ6:BQ172" ' AtticCol(6, 11) = "X6:X172" 'T_4_83 to T_4_89 from 402 AtticCol(6, 12) = "Y6:Y172" AtticCol(6, 13) = "Z6:Z172" AtticCol(6, 14) = "AA6:AA172" AtticCol(6, 15) = "AB6:AB172" AtticCol(6, 16) = "AC6:AC172" AtticCol(6, 17) = "AD6:AD172" 'void tc ' AtticCol(6, 18) = "AP6:AP172" 'RH_5_37 to RH_5_42 from 501 AtticCol(6, 19) = "AQ6:AQ172" AtticCol(6, 20) = "AR6:AR172" AtticCol(6, 21) = "AS6:AS172" AtticCol(6, 22) = "AT6:AT172" AtticCol(6, 23) = "AU6:AU172" ' AtticCol(6, 24) = "BN6:BN172" 'RH_5_61 to RH_5_62 from 501 AtticCol(6, 25) = "BO6:BO172" ' AtticCol(6, 26) = "Z6:Z172" 'HFT_2_16 to HF_2_18 from 200 AtticCol(6, 27) = "AA6:AA172" AtticCol(6, 28) = "AB6:AB172" ' ''ATTIC1 AtticCol(1, 1) = "AE6:AE172" 'T_4_90 to T_4_106 from 402 AtticCol(1, 2) = "AF6:AF172" AtticCol(1, 3) = "AG6:AG172" AtticCol(1, 4) = "AH6:AH172" AtticCol(1, 5) = "AI6:AI172" AtticCol(1, 6) = "AJ6:AJ172" AtticCol(1, 7) = "AK6:AK172" AtticCol(1, 8) = "AL6:AL172" AtticCol(1, 9) = "AM6:AM172" AtticCol(1, 10) = "AN6:AN172" AtticCol(1, 11) = "AO6:AO172" AtticCol(1, 12) = "AP6:AP172" AtticCol(1, 13) = "AQ6:AQ172" AtticCol(1, 14) = "AR6:AR172" AtticCol(1, 15) = "AS6:AS172" AtticCol(1, 16) = "AT6:AT172" AtticCol(1, 17) = "AU6:AU172" 'void TC ' AtticCol(1, 18) = "AX6:AX172" 'RH_5_45 to RH_5_52 from 501 AtticCol(1, 19) = "AY6:AY172" AtticCol(1, 20) = "AZ6:AZ172" AtticCol(1, 21) = "BA6:BA172" AtticCol(1, 22) = "BB6:BB172" AtticCol(1, 23) = "BC6:BC172" AtticCol(1, 24) = "BD6:BD172" AtticCol(1, 25) = "BE6:BE172" ' AtticCol(1, 26) = "AC6:AC172" ''HFT_2_19 to HFT_2_20 from 200 AtticCol(1, 27) = "AD6:AD172" ' AtticCol(1, 28) = "AF6:AF172" 'HFT_2_22 from 200 ' ''ATTIC 7 AtticCol(7, 1) = "AV6:AV172" 'T_4_107 to T_4_122 from 402 AtticCol(7, 2) = "AW6:AW172" AtticCol(7, 3) = "AX6:AX172" AtticCol(7, 4) = "AY6:AY172" AtticCol(7, 5) = "AZ6:AZ172" AtticCol(7, 6) = "BA6:BA172" AtticCol(7, 7) = "BB6:BB172" AtticCol(7, 8) = "BC6:BC172" AtticCol(7, 9) = "BD6:BD172" AtticCol(7, 10) = "BE6:BE172" AtticCol(7, 11) = "BF6:BF172" AtticCol(7, 12) = "BG6:BG172" AtticCol(7, 13) = "BH6:BH172" AtticCol(7, 14) = "BI6:BI172" AtticCol(7, 15) = "BJ6:BJ172" AtticCol(7, 16) = "BK6:BK172" AtticCol(7, 17) = "BL6:BL172" 'void tc ' AtticCol(7, 18) = "BF6:BF172" 'RH_5_53 to RH_5_60 from 501 AtticCol(7, 19) = "BG6:BG172" AtticCol(7, 20) = "BH6:BH172" AtticCol(7, 21) = "BI6:BI172" AtticCol(7, 22) = "BJ6:BJ172" AtticCol(7, 23) = "BK6:BK172" AtticCol(7, 24) = "BL6:BL172" AtticCol(7, 25) = "BM6:BM172" ' AtticCol(7, 26) = "AH6:AH172" 'HFT_2_24 to HFT_2_26 from 200 AtticCol(7, 27) = "AI6:AI172" AtticCol(7, 28) = "AJ6:AJ172" ''''''''''''''''''''''''''''''''''''''' ''''''WEATHER'''''''''''''''''''''''''' ''''''''''''''''''''''''''''''''''''''' ''Weather Sheet WCol(1, 1) = "F6:F172" '3_300 IntTemp to Water_off WCol(1, 2) = "G6:G172" WCol(1, 3) = "H6:H172" WCol(1, 4) = "I6:I172" WCol(1, 5) = "J6:J172" WCol(1, 6) = "K6:K172" WCol(1, 7) = "L6:L172" WCol(1, 8) = "M6:M172" WCol(1, 9) = "N6:N172" WCol(1, 10) = "O6:O172" WCol(1, 11) = "P6:P172" WCol(1, 12) = "Q6:Q172" WCol(1, 13) = "R6:R172" WCol(1, 14) = "S6:S172" WCol(1, 15) = "T6:T172" WCol(1, 16) = "U6:U172" WCol(1, 17) = "V6:V172" WCol(1, 18) = "W6:W172" WCol(1, 19) = "X6:X172" WCol(1, 20) = "Y6:Y172" WCol(1, 21) = "Z6:Z172" WCol(1, 22) = "AA6:AA172" WCol(1, 23) = "AB6:AB172" WCol(1, 24) = "AC6:AC172" WCol(1, 25) = "AD6:AD172" WCol(1, 26) = "AE6:AE172" WCol(1, 27) = "AF6:AF172" WCol(1, 28) = "AG6:AG172" ' WCol(1, 29) = "H6:H172" '2_200 LI_CORR_N ' '''''TempRH Sheet WCol(2, 1) = "N6:N172" '1_105 WCol(2, 2) = "O6:O172" WCol(2, 3) = "P6:P172" WCol(2, 4) = "Q6:Q172" ' WCol(2, 5) = "AF6:AF172" '2_202 WCol(2, 6) = "AG6:AG172" WCol(2, 7) = "AH6:AH172" WCol(2, 8) = "AI6:AI172" ' WCol(2, 9) = "H6:H172" '3_300 WCol(2, 10) = "I6:I172" WCol(2, 16) = "K6:K172" '3_300 WCol(2, 17) = "L6:L172" ' WCol(2, 18) = "H6:H172" '2_200 WCol(2, 32) = "AB6:AB172" WCol(2, 33) = "AC6:AC172" WCol(2, 34) = "AD6:AD172" ' '''''RH8 Sheet WCol(3, 1) = "F6:F172" '4_401 WCol(3, 2) = "G6:G172" WCol(3, 3) = "H6:H172" WCol(3, 4) = "I6:I172" WCol(3, 5) = "J6:J172" WCol(3, 6) = "K6:K172" WCol(3, 7) = "L6:L172" WCol(3, 8) = "M6:M172" WCol(3, 9) = "N6:N172" WCol(3, 10) = "O6:O172" WCol(3, 11) = "P6:P172" WCol(3, 12) = "Q6:Q172" WCol(3, 13) = "R6:R172" WCol(3, 14) = "S6:S172" WCol(3, 15) = "T6:T172" WCol(3, 16) = "U6:U172" ' WCol(3, 18) = "F6:F172" '5_501 WCol(3, 19) = "G6:G172" WCol(3, 20) = "H6:H172" WCol(3, 21) = "I6:I172" WCol(3, 22) = "J6:J172" WCol(3, 23) = "K6:K172" ' WCol(3, 24) = "AD6:AD172" '5_501 WCol(3, 25) = "AE6:AE172" ' WCol(3, 26) = "N6:N172" '2_200 WCol(3, 27) = "O6:O172" WCol(3, 28) = "P6:P172" ''''''''''' 'This array is for the selected weeks (per friday) Week(1) = "060410" '2010 Week(2) = "061110" Week(3) = "061810" Week(4) = "062510" Week(5) = "070210" Week(6) = "070910" Week(7) = "071610" Week(8) = "072310" Week(9) = "073010" Week(10) = "080610" Week(11) = "081310" Week(12) = "082010" Week(13) = "082710" Week(14) = "090310" Week(15) = "091010" Week(16) = "091710" Week(17) = "092410" Week(18) = "100110" Week(19) = "100810" Week(20) = "101510" Week(21) = "102210" Week(22) = "102910" Week(23) = "110510" Week(24) = "111210" Week(25) = "111910" Week(26) = "112610" Week(27) = "120310" Week(28) = "121010" Week(29) = "121710" Week(30) = "122410" Week(31) = "123110" Week(32) = "010711" ' 2011 Week(33) = "011411" Week(34) = "012111" Week(35) = "012811" Week(36) = "020411" Week(37) = "021111" Week(38) = "021811" Week(39) = "022511" Week(40) = "030411" Week(41) = "031111" Week(42) = "031811" Week(43) = "032511" Week(44) = "040111" Week(45) = "040811" Week(46) = "041511" Week(47) = "042211" Week(48) = "042911" Week(49) = "050611" Week(50) = "051311" Week(51) = "052011" Week(52) = "052711" Week(53) = "060311" Week(54) = "061011" Week(55) = "061711" Week(56) = "062411" Week(57) = "070111" Week(58) = "070811" Week(59) = "071511" Week(60) = "072211" Week(61) = "072911" Week(62) = "080511" Week(63) = "081211" Week(64) = "081911" Week(65) = "082611" Week(66) = "090211" Week(67) = "090911" Week(68) = "091611" Week(69) = "092311" Week(70) = "093011" Week(71) = "100711" Week(72) = "101411" Week(73) = "102111" Week(74) = "102811" Week(75) = "110411" Week(76) = "111111" Week(77) = "111811" Week(78) = "112511" Week(79) = "120211" Week(80) = "120911" Week(81) = "121611" Week(82) = "122311" Week(83) = "123011" Week(84) = "010612" Week(85) = "011312" Week(86) = "012012" Week(87) = "012712" Week(88) = "020312" Week(89) = "021012" Week(90) = "021712" Week(91) = "022412" Week(92) = "030212" Week(93) = "030912" Week(94) = "031612" Week(95) = "032312" Week(96) = "033012" Week(97) = "040612" Week(98) = "041312" Week(99) = "042012" Week(100) = "042712" Week(101) = "050412" Week(102) = "051112" Week(103) = "051812" Week(104) = "052512" Week(105) = "060112" Week(106) = "060812" Week(107) = "061512" Week(108) = "062212" Week(109) = "062912" Week(110) = "070612" Week(111) = "071312" Week(112) = "072012" Week(113) = "072712" Week(114) = "080312" Week(115) = "081012" Week(116) = "081712" Week(117) = "082412" Week(118) = "083112" Week(119) = "090712" Week(120) = "091412" Week(121) = "092112" Week(122) = "092812" Week(123) = "100512" Week(124) = "101212" Week(125) = "101912" Week(126) = "102612" Week(127) = "110212" Week(128) = "110912" Week(129) = "111612" Week(130) = "112312" Week(131) = "113012" Week(132) = "120712" Week(133) = "121412" Week(134) = "122112" Week(135) = "122812" Week(136) = "010412" '2012 Week(137) = "010412" Week(138) = "010412" Week(139) = "010412" Week(140) = "010412" Week(141) = "060412" Week(142) = "060412" Week(143) = "060412" Week(144) = "060412" Week(145) = "060412" Week(146) = "060412" Week(147) = "060412" Week(148) = "060412" Week(149) = "060412" Week(150) = "060412" Week(151) = "060412" Week(152) = "060412" Week(153) = "060412" Week(154) = "060412" Week(155) = "060412" Week(156) = "060412" Week(157) = "060412" Week(158) = "060412" Week(159) = "060412" Week(160) = "060412" Week(161) = "060412" Week(162) = "060412" Week(163) = "060412" Week(164) = "060412" Week(165) = "060412" Week(166) = "060412" Week(167) = "060412" Week(168) = "060412" Week(169) = "060412" Week(170) = "060412" Week(171) = "060412" Week(172) = "060412" Week(173) = "060412" Week(174) = "060412" Week(175) = "060412" Week(176) = "060412" Week(177) = "060412" Week(178) = "060412" Week(179) = "110112" Week(180) = "110812" Week(181) = "111512" Week(182) = "122212" Week(183) = "112912" Week(184) = "120612" Week(185) = "121312" Week(186) = "122012" Week(187) = "122712" End Sub Sub AtticCalc() '***************************************************************************************** '**** This Macro Calculates the Real Values of %RH and Heat Flux for Charleston Attics**** '***************************************************************************************** ' Macro recorded 8/23/2010 by Stan Atherton ' X and Z values updated 1/25/2011 Dim R(1 To 8) As String Dim Rs(1 To 10) As String Dim SW(1 To 8) As String Dim SWs(1 To 8) As String Dim SB(1 To 8) As String Dim SBs(1 To 8) As String Dim dri As String Dim dire As String Dim X(1 To 7, 1 To 8) As String Dim Xs(5, 9 To 10) As String Dim Zs(5, 9 To 10) As String Dim Z(1 To 7, 1 To 8) As String Dim Sheetname(1 To 7) As String Dim HB(1 To 7, 1 To 3) As String Dim HW(1 To 7, 1 To 3) As String Sheetname(1) = "Attic1" Sheetname(2) = "Attic2" Sheetname(3) = "Attic3" Sheetname(4) = "Attic4" Sheetname(5) = "Attic5" Sheetname(6) = "Attic6" Sheetname(7) = "Attic7" R(1) = "V" R(2) = "W" R(3) = "X" R(4) = "Y" R(5) = "Z" R(6) = "AA" R(7) = "AB" R(8) = "AC" 'Note that subscript s is for special case of attic 5 Rs(1) = "W" Rs(2) = "X" Rs(3) = "Y" Rs(4) = "Z" Rs(5) = "AA" Rs(6) = "AB" Rs(7) = "AC" Rs(8) = "AD" Rs(9) = "AE" Rs(10) = "AF" SW(1) = "AD" SW(2) = "AE" SW(3) = "AF" 'Note that subscript s is for special case of attic 5 SWs(1) = "AG" SWs(2) = "AH" SWs(3) = "AI" SB(1) = "AG" SB(2) = "AH" SB(3) = "AI" 'Note that subscript s is for special case of attic 5 SBs(1) = "AJ" SBs(2) = "AK" SBs(3) = "AL" '''The values of Array X() are the constant slope values for each %RH sensor (mv/%RH) 'X(2, 1) = 0.0291 'X(2, 2) = 0.0295 'X(2, 3) = 0.0294 'X(2, 4) = 0.0329 'X(2, 5) = 0.0308 'X(2, 6) = 0.0297 'X(2, 7) = 0.0318 'X(2, 8) = 0.0316 'X(3, 1) = 0.0312 'X(3, 2) = 0.0293 'X(3, 3) = 0.0294 'X(3, 4) = 0.0316 'X(3, 5) = 0.0316 'X(3, 6) = 0.0316 'X(3, 7) = 0.0316 'X(3, 8) = 0.0315 'X(4, 1) = 0.0294 'X(4, 2) = 0.0298 'X(4, 3) = 0.0292 'X(4, 4) = 0.0314 'X(4, 5) = 0.0305 'X(4, 6) = 0.0306 'X(4, 7) = 0.0303 'X(4, 8) = 0.0305 'X(5, 1) = 0.0313 'X(5, 2) = 0.0295 'X(5, 3) = 0.0298 'X(5, 4) = 0.0317 'X(5, 5) = 0.0317 'X(5, 6) = 0.0317 'X(5, 7) = 0.0317 'X(5, 8) = 0.0315 'X(6, 1) = 0.0295 'X(6, 2) = 0.0299 'X(6, 3) = 0.0316 'X(6, 4) = 0.0293 'X(6, 5) = 0.0316 'X(6, 6) = 0.0315 'X(6, 7) = 0.0316 'X(6, 8) = 0.0317 'X(1, 1) = 0.0311 'X(1, 2) = 0.0312 'X(1, 3) = 0.0297 'X(1, 4) = 0.0316 'X(1, 5) = 0.0316 'X(1, 6) = 0.0316 'X(1, 7) = 0.0316 'X(1, 8) = 0.0316 'X(7, 1) = 0.03 'X(7, 2) = 0.0299 'X(7, 3) = 0.0293 'X(7, 4) = 0.0315 'X(7, 5) = 0.0317 'X(7, 6) = 0.0317 'X(7, 7) = 0.0317 'X(7, 8) = 0.0317 X(2, 1) = 0.0291 X(2, 2) = 0.0295 X(2, 3) = 0.0294 X(2, 4) = 0.0329 X(2, 5) = 0.0308 X(2, 6) = 0.0297 X(2, 7) = 0.0318 X(2, 8) = 0.0316 X(3, 1) = 0.0312 X(3, 2) = 0.0293 X(3, 3) = 0.0294 X(3, 4) = 0.0316 X(3, 5) = 0.0316 X(3, 6) = 0.0316 X(3, 7) = 0.0316 X(3, 8) = 0.0315 X(4, 1) = 0.0294 X(4, 2) = 0.0298 X(4, 3) = 0.0292 X(4, 4) = 0.0314 X(4, 5) = 0.0305 X(4, 6) = 0.0306 X(4, 7) = 0.0303 X(4, 8) = 0.0305 X(5, 1) = 0.0313 X(5, 2) = 0.0295 X(5, 3) = 0.0298 X(5, 4) = 0.0317 X(5, 5) = 0.0317 X(5, 6) = 0.0317 X(5, 7) = 0.0317 X(5, 8) = 0.0315 X(6, 1) = 0.0295 X(6, 2) = 0.0299 X(6, 3) = 0.0316 X(6, 4) = 0.0293 X(6, 5) = 0.0316 X(6, 6) = 0.0315 X(6, 7) = 0.0316 X(6, 8) = 0.0317 X(1, 1) = 0.0311 X(1, 2) = 0.0312 X(1, 3) = 0.0297 X(1, 4) = 0.0316 X(1, 5) = 0.0316 X(1, 6) = 0.0316 X(1, 7) = 0.0316 X(1, 8) = 0.0316 X(7, 1) = 0.03 X(7, 2) = 0.0299 X(7, 3) = 0.0293 X(7, 4) = 0.0315 X(7, 5) = 0.0317 X(7, 6) = 0.0317 X(7, 7) = 0.0317 X(7, 8) = 0.0317 'Note that subscript s is for special case of attic 5 Xs(5, 9) = 0.0316 Xs(5, 10) = 0.0309 Zs(5, 9) = 0.800812 Zs(5, 10) = 0.80038 '''THe values of array Z are the constant values for zero offset for each RH sensor (V) 'Z(2, 1) = 0.722 'Z(2, 2) = 0.711 'Z(2, 3) = 0.764 'Z(2, 4) = 0.817713 'Z(2, 5) = 0.80947 'Z(2, 6) = 0.708 'Z(2, 7) = 0.805613 'Z(2, 8) = 0.849384 'Z(3, 1) = 0.813116 'Z(3, 2) = 0.708 'Z(3, 3) = 0.749 'Z(3, 4) = 0.845302 'Z(3, 5) = 0.84328 'Z(3, 6) = 0.846602 'Z(3, 7) = 0.849368 'Z(3, 8) = 0.850434 'Z(4, 1) = 0.704 'Z(4, 2) = 0.705 'Z(4, 3) = 0.721 'Z(4, 4) = 0.861 'Z(4, 5) = 0.831 'Z(4, 6) = 0.843 'Z(4, 7) = 0.833 'Z(4, 8) = 0.848 'Z(5, 1) = 0.809502 'Z(5, 2) = 0.712 'Z(5, 3) = 0.714 'Z(5, 4) = 0.840674 'Z(5, 5) = 0.846949 'Z(5, 6) = 0.845004 'Z(5, 7) = 0.842745 'Z(5, 8) = 0.841437 'Z(6, 1) = 0.707 'Z(6, 2) = 0.683 'Z(6, 3) = 0.810639 'Z(6, 4) = 0.707 'Z(6, 5) = 0.849319 'Z(6, 6) = 0.852114 'Z(6, 7) = 0.838266 'Z(6, 8) = 0.84525 'Z(1, 1) = 0.834369 'Z(1, 2) = 0.81019 'Z(1, 3) = 0.708 'Z(1, 4) = 0.847082 'Z(1, 5) = 0.849215 'Z(1, 6) = 0.843537 'Z(1, 7) = 0.844636 'Z(1, 8) = 0.852234 'Z(7, 1) = 0.704 'Z(7, 2) = 0.696 'Z(7, 3) = 0.716 'Z(7, 4) = 0.850728 'Z(7, 5) = 0.844666 'Z(7, 6) = 0.843746 'Z(7, 7) = 0.858056 'Z(7, 8) = 0.846394 Z(2, 1) = 0.722 Z(2, 2) = 0.711 Z(2, 3) = 0.764 Z(2, 4) = 0.817713 Z(2, 5) = 0.80947 Z(2, 6) = 0.708 Z(2, 7) = 0.805613 Z(2, 8) = 0.849384 Z(3, 1) = 0.813116 Z(3, 2) = 0.708 Z(3, 3) = 0.749 Z(3, 4) = 0.845302 Z(3, 5) = 0.84328 Z(3, 6) = 0.846602 Z(3, 7) = 0.849368 Z(3, 8) = 0.850434 Z(4, 1) = 0.704 Z(4, 2) = 0.705 Z(4, 3) = 0.721 Z(4, 4) = 0.861 Z(4, 5) = 0.831 Z(4, 6) = 0.843 Z(4, 7) = 0.833 Z(4, 8) = 0.848 Z(5, 1) = 0.809502 Z(5, 2) = 0.712 Z(5, 3) = 0.714 Z(5, 4) = 0.840674 Z(5, 5) = 0.846949 Z(5, 6) = 0.845004 Z(5, 7) = 0.842745 Z(5, 8) = 0.841437 Z(6, 1) = 0.707 Z(6, 2) = 0.683 Z(6, 3) = 0.810639 Z(6, 4) = 0.707 Z(6, 5) = 0.849319 Z(6, 6) = 0.852114 Z(6, 7) = 0.838266 Z(6, 8) = 0.84525 Z(1, 1) = 0.834369 Z(1, 2) = 0.81019 Z(1, 3) = 0.708 Z(1, 4) = 0.847082 Z(1, 5) = 0.849215 Z(1, 6) = 0.843537 Z(1, 7) = 0.844636 Z(1, 8) = 0.852234 Z(7, 1) = 0.704 Z(7, 2) = 0.696 Z(7, 3) = 0.716 Z(7, 4) = 0.850728 Z(7, 5) = 0.844666 Z(7, 6) = 0.843746 Z(7, 7) = 0.858056 Z(7, 8) = 0.846394 '''''The values of Array HW() are the constant factors by which the heat flux is calculated ''''' per heat flux transducer (For the case of SI units) HW(1, 1) = 5.27339249935108 HW(1, 2) = 5.10510239870343 HW(1, 3) = 6.53879999539875 HW(2, 1) = 5.343522354 HW(2, 2) = 5.735892645 HW(2, 3) = 5.283785074 HW(3, 1) = 5.463861504 HW(3, 2) = 6.293269487 HW(3, 3) = 6.38402875 HW(4, 1) = 5.215168303 HW(4, 2) = 5.709305354 HW(4, 3) = 6.652679545 HW(5, 1) = 5.36275161 HW(5, 2) = 5.435700137 HW(5, 3) = 6.259306619 HW(6, 1) = 4.787317973 HW(6, 2) = 5.241818005 HW(6, 3) = 6.542613775 HW(7, 1) = 5.639985805 HW(7, 2) = 5.459938611 HW(7, 3) = 6.492753042 '''''The values of Array HW() are the constant factors by which the heat flux is calculated ''''' per heat flux transducer (For the case of US units) HB(1, 1) = 1.672192762 HB(1, 2) = 1.618827971 HB(1, 3) = 2.073453479 HB(2, 1) = 1.694430939 HB(2, 2) = 1.818851558 HB(2, 3) = 1.675488247 HB(3, 1) = 1.732590483 HB(3, 2) = 1.995595754 HB(3, 3) = 2.024375517 HB(4, 1) = 1.653729869 HB(4, 2) = 1.810420728 HB(4, 3) = 2.109564684 HB(5, 1) = 1.700528535 HB(5, 2) = 1.723660513 HB(5, 3) = 1.984826129 HB(6, 1) = 1.518058529 HB(6, 2) = 1.662180489 HB(6, 3) = 2.074662828 HB(7, 1) = 1.788439499 HB(7, 2) = 1.731346534 HB(7, 3) = 2.05885199 dri = "C" dire = dri & ":\Users\Owner\Documents\SAAtherton\Charleston_Field_Tests\Raw_Data\" '''''''''''''''Heat Flux Calculator (SI) Windows("GAF_Attics_template.xls").Activate For n = 1 To 4 'WORKSHEETS Worksheets(Sheetname(n)).Activate For j = 1 To 3 'COLUMNS For i = 7 To 174 'ROWS Range(SB(j) & i) = (Range(SW(j) & i)) * HB(n, j) Next i Next j Next n ' Worksheets(Sheetname(5)).Activate For j = 1 To 3 'COLUMNS For i = 7 To 174 'ROWS Range(SBs(j) & i) = (Range(SWs(j) & i)) * HB(5, j) Next i Next j ' For n = 6 To 7 'WORKSHEETS Worksheets(Sheetname(n)).Activate For j = 1 To 3 'COLUMNS For i = 7 To 174 'ROWS Range(SB(j) & i) = (Range(SW(j) & i)) * HB(n, j) Next i Next j Next n ''''''''''''''''Heat Flux Calculator (US) For n = 1 To 4 'WORKSHEETS Worksheets(Sheetname(n)).Activate For j = 1 To 3 'COLUMNS For i = 7 To 174 'ROWS Range(SW(j) & i) = (Range(SW(j) & i)) * HW(n, j) Next i Next j Next n ' Worksheets(Sheetname(5)).Activate For j = 1 To 3 'COLUMNS For i = 7 To 174 'ROWS Range(SWs(j) & i) = (Range(SWs(j) & i)) * HW(5, j) Next i Next j ' For n = 6 To 7 'WORKSHEETS Worksheets(Sheetname(n)).Activate For j = 1 To 3 'COLUMNS For i = 7 To 174 'ROWS Range(SW(j) & i) = (Range(SW(j) & i)) * HW(n, j) Next i Next j Next n '''''''''''''''''%RH Calculator For n = 1 To 4 'WORKSHEETS Worksheets(Sheetname(n)).Activate For j = 1 To 8 'COLUMNS For i = 7 To 174 'ROWS Range(R(j) & i) = (((((Range(R(j) & i) + 31.226) / 0.06519) * 2) / 1000) - Z(n, j)) / (X(n, j)) Next i Next j Next n ' Worksheets(Sheetname(5)).Activate For j = 1 To 8 'COLUMNS For i = 7 To 174 'ROWS Range(Rs(j) & i) = (((((Range(Rs(j) & i) + 31.226) / 0.06519) * 2) / 1000) - Z(5, j)) / (X(5, j)) Next i Next j For j = 9 To 10 For i = 7 To 174 Range(Rs(j) & i) = (((((Range(Rs(j) & i) + 31.226) / 0.06519) * 2) / 1000) - Zs(5, j)) / (Xs(5, j)) Next i Next j ' For n = 6 To 7 'WORKSHEETS Worksheets(Sheetname(n)).Activate For j = 1 To 8 'COLUMNS For i = 7 To 174 'ROWS Range(R(j) & i) = (((((Range(R(j) & i) + 31.226) / 0.06519) * 2) / 1000) - Z(n, j)) / (X(n, j)) Next i Next j Next n End Sub Sub SIMPLIFYS() '''''''''''''''''''''''''''''''''''''''''''''''' '''''THIS MACRO Autofits the Template'''''' '''''''''''''''''''''''''''''''''''''''''''''''' Dim AT(1 To 7) As String AT(1) = "Attic1" AT(2) = "Attic2" AT(3) = "Attic3" AT(4) = "Attic4" AT(5) = "Attic5" AT(6) = "Attic6" AT(7) = "Attic7" For i = 1 To 7 Windows("GAF_Attics_template.xls").Activate Worksheets(AT(i)).Activate Columns("A:BN").Select Columns("A:BN").EntireColumn.AutoFit Next i End Sub Sub FindTheFaulty() ' This Subroutine Determines if any thermocouples are reading erroneous temperatures. ' FindTheFault Macro ' Macro recorded 9/7/2010 by Jeff Atherton ' Dim TCol(1 To 20) As String Dim Sheet(1 To 7) As String Dim Col(1 To 16) As String Col(1) = "E" Col(2) = "F" Col(3) = "G" Col(4) = "H" Col(5) = "I" Col(6) = "J" Col(7) = "K" Col(8) = "L" Col(9) = "M" Col(10) = "N" Col(11) = "O" Col(12) = "P" Col(13) = "Q" Col(14) = "R" Col(15) = "S" Col(16) = "T" TCol(1) = "A" TCol(2) = "B" TCol(3) = "C" TCol(4) = "D" TCol(5) = "E" TCol(6) = "F" TCol(7) = "G" TCol(8) = "H" TCol(9) = "I" TCol(10) = "J" TCol(11) = "K" TCol(12) = "L" TCol(13) = "M" Sheet(1) = "Attic1" Sheet(2) = "Attic2" Sheet(3) = "Attic3" Sheet(4) = "Attic4" Sheet(5) = "Attic5" Sheet(6) = "Attic6" Sheet(7) = "Attic7" Worksheets("ThermoTest").Activate Range("A2", "G17").ClearContents For i = 1 To 7 'Attics For j = 1 To 16 'Columns For n = 7 To 174 'Rows Worksheets(Sheet(i)).Activate Range(Col(j) & n).Select If Range(Col(j) & (n)).Value > 150 Then 'CHANGE THIS NUMBER TO CHANGE THE TEMP LIMIT - 150 is a summer limit' Range(Col(j) & "6").Select Selection.Copy Worksheets("ThermoTest").Activate Range(TCol(i) & (j + 1)).Select ActiveSheet.Paste GoTo 30 Else: GoTo 20 End If 20 Next n 30 Next j Next i Worksheets("ThermoTest").Activate For m = 1 To 7 For d = 2 To 17 If Range(TCol(m) & d).Value = "" Then Range(TCol(m) & d).Value = "Pass" Range(TCol(m) & d).Select With Selection.Interior .ColorIndex = 16 .Pattern = xlSolid End With Else: Range(TCol(m) & d).Select With Selection.Interior .ColorIndex = 3 .Pattern = xlSolid End With End If Next d Next m Range("A2:G17").Select ActiveWindow.SmallScroll Down:=3 Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlInsideVertical) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlInsideHorizontal) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection .HorizontalAlignment = xlLeft .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Worksheets("ThermoTest").Activate Range("A1").Select End Sub 'This subroutine moves columns around for easier reading ' Stan Atherton Sub FixitForSudhir() Dim ws(1 To 7) As String Dim Co(1 To 7) As String ws(1) = "Attic1" ws(2) = "Attic2" ws(3) = "Attic3" ws(4) = "Attic4" ws(5) = "Attic5" ws(6) = "Attic6" ws(7) = "Attic7" Co(1) = "W" Co(2) = "AH" Co(3) = "AD" Windows("GAF_Attics_template.xls").Activate 'Windows("GAF_ATTICS_121010").Activate For S = 1 To 7 If S = 1 Then GoTo 1 If S = 2 Then GoTo 2 If S = 3 Then GoTo 3 If S = 4 Then GoTo 4 If S = 5 Then GoTo 5 If S = 6 Then GoTo 6 If S = 7 Then GoTo 7 1 Worksheets(ws(1)).Activate Columns(Co(3)).Select Selection.Insert Shift:=xlToRight GoTo 80 2 Worksheets(ws(2)).Activate Columns(Co(3)).Select Selection.Insert Shift:=xlToRight GoTo 80 3 Worksheets(ws(3)).Activate Columns(Co(3)).Select Selection.Insert Shift:=xlToRight GoTo 80 4 Worksheets(ws(4)).Activate Columns(Co(3)).Select Selection.Insert Shift:=xlToRight GoTo 80 5 Worksheets(ws(5)).Activate Columns(Co(1)).Select Selection.Insert Shift:=xlToRight Columns(Co(2)).Select Selection.Insert Shift:=xlToRight GoTo 80 6 Worksheets(ws(6)).Activate Columns(Co(3)).Select Selection.Insert Shift:=xlToRight GoTo 80 7 Worksheets(ws(7)).Activate Columns(Co(3)).Select Selection.Insert Shift:=xlToRight GoTo 80 80 Next S End Sub