![]() 'get percentage bruised, change second argument of round from 2 to 3 if you want a decimal (i.e., 33.3 instead of 33) K2.Cells(m.Row, j).Value = k2.Cells(m.Row, j).Value + k1.Cells(i, j).Value 'output sum cell for current apple type = current cell value plus the value in the current input row 'if the cell in Row i, Column A = the current apple type equal to m Set AppleTypes = k2.Range(k2.Cells(2, 1), k2.Cells(OutputLRow, 1)) K2.Range(k2.Cells(2, 2), k2.Cells(OutputLRow, InputLCol)).ClearContents 'get rid of previous sums/percentages if existing OutputLRow = k2.Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row 'determine last row in output after having removed duplicates K2.Cells.RemoveDuplicates Columns:=Array(1) K1.Range(k1.Cells(InputFRow, ApplesCol), k1.Cells(InputLRow, ApplesCol)).Copy k2.Cells(2, ApplesCol) 'get all apple types into your output table InputLRow = k1.Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row 'identify column and row numbers, substituting find functions as needed 'Put columns of sums here, not percentages Range(ran.Cells(x + 1, 1), ran.Cells(x + 1, UBound(v))).Value = vĭim ApplesCol As Long, ApplesNumCol As Long, BruisedCol As Long, RottenCol As Long, _ InputLRow As Long, OutputLRow As Long, InputFRow As Long, OutputFRow As Long, _ InputLCol As Long, BruisedCentCol As Long, RottenCentCol As Long Dim AppleTypes As Range Dim Arr() As Variant 'The Keys and Items properties are zero indexed, so start at 0 not 1ĭim v As Variant: Let v = dic.Items(x - 1) 'If the key value doesn't exist in the dictionary, add them. ' Replace the values in the dictionary with the updated values. ' Start on the second value because the first value in the array is the key. 'If the value exists in the dictionary, then iterate through the values and add to existing values 'Transposes the row's data into a 1D array to make dealing with it less combersome.ĭim vals As Variant: vals = Application.Transpose(Application.Transpose(ran.Rows(r))) '(in VBA, your life will be much easier if your data is in a ListObject.) 'Start on the second row because the first row contains the headers. 'Dim dic: Set dic = CreateObject("Scripting.Dictionary")ĭim dic As Dictionary: Set dic = New Dictionary 'for this to work properly as creating a dictionary using the "CreateObject" method loses 'You will need to add a reference (Tools/References.) to "Microsoft Scripting Runtime" 'You will need to change this if your data isn't the only thing on Sheet1ĭim ran As Range: Set ran = Sheet1.UsedRange ![]() You will need to add a reference (Tools/References.) to "Microsoft Scripting Runtime" in the VBA editor for this to work properly as creating a dictionary using the "CreateObject" method loses functionality for some reason. ![]()
0 Comments
Leave a Reply. |