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\$