Background: End users wanted to automate the process of taking information out of our CRM software (in the form of a csv), and then process it into something more usable for their purposes. Ended up not getting used, but I did manage to make it run/work on old data.
The following code expects an import of values to be placed in a sheet labeled "Import", each row of the import is associated with a specific location that has been inspected, but there are multiple rows per location (different comments, and information for the location).
The code is meant to take these multiple rows, and create an output with only one row per location, while concatenating the information that isn't duplicated in the import rows.
Wanted to see how many pitfalls I've fallen into, I've taken college coursework with programming, but no real guidance in industry (learned a great deal through trial/error when asked to make something at work).
Code does run, but I'm particularly interested in knowing whether my use of an array is really bad practice or not. I use a similar structure for a lot of the VBA code I make that needs to deal with import data (I don't know of a better way to manipulate the data, leaving it in the sheets ends up being excessively slow on larger sets of data).
Sub ProcessData() Dim Imp_Sheet As Worksheet Set Imp_Sheet = Sheets("Import") Dim Unique_Sheet As Worksheet Set Unique_Sheet = Sheets("UniqueVals") Dim Out_Sheet As Worksheet Set Out_Sheet = Sheets("Output") 'Clear unique value sheet Unique_Sheet.Cells.Clear 'Copy up to 3000 locations from the import sheet. Imp_Sheet.Range("C1:C3000").Copy Unique_Sheet.Paste (Unique_Sheet.Cells(1, 1)) 'Remove duplicate locations Unique_Sheet.Range("A1:A3000").RemoveDuplicates Columns:=(1), Header:=xlYes 'Find number of inspections(Could include duplicate inspections that I don't want) Dim Inspection_Total As Long: Inspection_Total = Find_End(Unique_Sheet.Name) For i = 2 To Inspection_Total 'Check if it's found the end of the unique value list If (Unique_Sheet.Cells(i, 1).Value = "") Then Exit For End If 'give each unique value a number for future reference Unique_Sheet.Cells(i, 2).Value = i - 1 Next i Unique_Sheet.Cells(1, 2).Value = i - 2 'Change inspection total to be the actual number of inspections Inspection_Total = i - 2 Dim Import_Total: Import_Total = Find_End(Imp_Sheet.Name) 'Prepare array to load import values into. Dim Imp_Arr() As Variant '0 = location, 1= city, 2= st, 3=date site set, 4=pre-ins date, 5= mod name, 6= mod title, 7 = summary ReDim Imp_Arr(Inspection_Total, 7) 'Walk through import sheet, for reach row, compare to the list of unique locations (to find where each row belongs) For i = 2 To Import_Total For x = 2 To Unique_Sheet.Cells(1, 2) + 1 If (Imp_Sheet.Cells(i, 3) = Unique_Sheet.Cells(x, 1)) Then Imp_Arr(Unique_Sheet.Cells(x, 2) - 1, 0) = Imp_Sheet.Cells(i, 3) Imp_Arr(Unique_Sheet.Cells(x, 2) - 1, 1) = Imp_Sheet.Cells(i, 7) Imp_Arr(Unique_Sheet.Cells(x, 2) - 1, 2) = Imp_Sheet.Cells(i, 8) Imp_Arr(Unique_Sheet.Cells(x, 2) - 1, 3) = "MANUAL INPUT REQUIRED" Imp_Arr(Unique_Sheet.Cells(x, 2) - 1, 4) = Imp_Sheet.Cells(i, 6) Imp_Arr(Unique_Sheet.Cells(x, 2) - 1, 5) = Imp_Sheet.Cells(i, 4) Imp_Arr(Unique_Sheet.Cells(x, 2) - 1, 6) = Imp_Sheet.Cells(i, 5) 'Comments on a specific location need to be on one row, this accomplishes that Imp_Arr(Unique_Sheet.Cells(x, 2) - 1, 7) = Imp_Arr(Unique_Sheet.Cells(x, 2) - 1, 7) & Imp_Sheet.Cells(i, 10) & " " & Imp_Sheet.Cells(i, 11) & " " & Imp_Sheet.Cells(i, 12) & ";" & Chr(10) End If Next x Next i 'Output one row for each location. For i = 0 To Inspection_Total For Z = 0 To 7 ' Out_Sheet.Cells(i + 2, Z + 1).Value = Imp_Arr(i, Z) Next Z Next i End Sub
Sheets("UniqueVals"). Dynamic Ranges are preferred over static ones (e.g. Range("A1", Range("A" & Rows.Count).End(xlUp)) is better then Range("A1:A3000")). \$\endgroup\$