Eastwick
11-28-2019, 06:53 AM
Hello All, I haven't posted for a long while so hope I am doing it correctly; I have a folder containing workbooks, the first sheet in each workbook provides a summary of (new) valuations across up to 28 classifications of land in each municipality of which there are 128 in total. Not all classifications are present in each municipality. I also have another worksheet which details all the previous valuations for each classification in each municipality; NOT ALL current valuation classifications may have been present in the "previous valuations" worksheet. I have cobbled together the following code which is pointed at the directory where the workbooks reside and it opens all the workbooks and copies the two data ranges into two arrays and populates them into a column for each municipality so I can quickly look across the States (in the resultant matrix) and see comparisons per classification and detect "hotpots", being higher or lower values; works as intended/required.
Sub myMatrix()
Dim FolderPath As String, Filepath As String, Filename As String
Dim arFinance As Variant
Dim arGeneral As Variant
Dim GEOlocation As String
FolderPath = "G:\repos\"
Filepath = FolderPath & "*.xls*"
Filename = Dir(Filepath)
Dim lastcolumn As Long
Do While Filename <> ""
Workbooks.Open (FolderPath & Filename)
GEOlocation = Range("B2")
arFinance = ActiveSheet.Range("F2:F9").Value
arGeneral = ActiveSheet.Range("H10:H29").Value
Application.DisplayAlerts = False
ActiveWorkbook.Close
lastcolumn = Cells(1, Columns.Count).End(xlToLeft).Column
newColumn = lastcolumn + 1
ActiveSheet.Cells(1, newColumn) = GEOlocation
ActiveSheet.Cells(31, newColumn) = TTL_Value
ActiveWorkbook.Worksheets("Sheet1").Range(Cells(2, newColumn), Cells(9, newColumn)) = arFinance
ActiveWorkbook.Worksheets("Sheet1").Range(Cells(10, newColumn), Cells(29, newColumn)) = arGeneral
Filename = Dir
Loop
End Sub
Summary worksheet;
25498
Resultant Matrix (from Code);
25500
The other "previous values" spreadsheet in row format lists only the values that were present previously (see below) listing all previous values for the classifications in each municipality; There may not have been a "previous" value for each classification - so no need to see that data of course but if the classification is present in the new data it needs to be "flagged" as new.
25499
I really need to have a procedure that matches the municipality name across the columns in my matrix and the looks in the previous values worksheet matching the municipality (rows) and then inserting a column before each municipality column in the matrix worksheet, matching and inserting each previous value for each classification and inserting the text; "no previous" for values that are "new" or thereby not present in the list of previous values worksheet.
Any and all guidance would be greatly appreciated. ;-)
Sub myMatrix()
Dim FolderPath As String, Filepath As String, Filename As String
Dim arFinance As Variant
Dim arGeneral As Variant
Dim GEOlocation As String
FolderPath = "G:\repos\"
Filepath = FolderPath & "*.xls*"
Filename = Dir(Filepath)
Dim lastcolumn As Long
Do While Filename <> ""
Workbooks.Open (FolderPath & Filename)
GEOlocation = Range("B2")
arFinance = ActiveSheet.Range("F2:F9").Value
arGeneral = ActiveSheet.Range("H10:H29").Value
Application.DisplayAlerts = False
ActiveWorkbook.Close
lastcolumn = Cells(1, Columns.Count).End(xlToLeft).Column
newColumn = lastcolumn + 1
ActiveSheet.Cells(1, newColumn) = GEOlocation
ActiveSheet.Cells(31, newColumn) = TTL_Value
ActiveWorkbook.Worksheets("Sheet1").Range(Cells(2, newColumn), Cells(9, newColumn)) = arFinance
ActiveWorkbook.Worksheets("Sheet1").Range(Cells(10, newColumn), Cells(29, newColumn)) = arGeneral
Filename = Dir
Loop
End Sub
Summary worksheet;
25498
Resultant Matrix (from Code);
25500
The other "previous values" spreadsheet in row format lists only the values that were present previously (see below) listing all previous values for the classifications in each municipality; There may not have been a "previous" value for each classification - so no need to see that data of course but if the classification is present in the new data it needs to be "flagged" as new.
25499
I really need to have a procedure that matches the municipality name across the columns in my matrix and the looks in the previous values worksheet matching the municipality (rows) and then inserting a column before each municipality column in the matrix worksheet, matching and inserting each previous value for each classification and inserting the text; "no previous" for values that are "new" or thereby not present in the list of previous values worksheet.
Any and all guidance would be greatly appreciated. ;-)