StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Right click on the sheet1 tab > View Code<br> <img src="https://i.stack.imgur.com/KXcPs.png" alt="sheet1 view code"> <br> paste this code<br></p> <pre><code>Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 4 Then If Target.Row = 4 Or Target.Row = 5 Or Target.Row = 6 Then Totals End If End Sub </code></pre> <p>then add a <code>module</code> right click <code>Sheet1</code> in the <code>Project Explorer</code> in the <code>VBE window</code> and <code>Insert > Module</code> <br> then pate this code</p> <pre><code>Sub Totals() Range("C10:D" & Range("C10:C" & Rows.Count).End(xlDown).Row).ClearContents Dim startAtRow As Long startAtRow = 10 ' Set starting row Dim lr As Long, i As Long, j As Long lr = Range("J" & Rows.Count).End(xlUp).Row ReDim arr(lr - 4) As String For i = 5 To lr arr(i - 5) = Range("J" & i).Value Next i Dim arr2() As String arr2 = arr RemoveDuplicate arr For i = LBound(arr) To UBound(arr) - 1 Range("C" & (i + startAtRow)).Value = arr(i) For j = LBound(arr2) To UBound(arr2) - 1 If arr(i) = arr2(j) Then Range("D" & (i + startAtRow)).Value = Range("D" & i + startAtRow).Value + Range("I" & (j + 5)).Value End If Next j Next i End Sub Private Sub RemoveDuplicate(ByRef StringArray() As String) Dim lowBound$, UpBound&, A&, B&, cur&, tempArray() As String If (Not StringArray) = True Then Exit Sub lowBound = LBound(StringArray): UpBound = UBound(StringArray) ReDim tempArray(lowBound To UpBound) cur = lowBound: tempArray(cur) = StringArray(lowBound) For A = lowBound + 1 To UpBound For B = lowBound To cur If LenB(tempArray(B)) = LenB(StringArray(A)) Then If InStrB(1, StringArray(A), tempArray(B), vbBinaryCompare) = 1 Then Exit For End If Next B If B > cur Then cur = B: tempArray(cur) = StringArray(A) Next A ReDim Preserve tempArray(lowBound To cur): StringArray = tempArray End Sub </code></pre> <p>Now every time user is changing the values of D4,D5,D6 your results should update. The categories will be displayed starting at C10 down, and the totals at D10 down. Looks like this ( sample version ) <br> <img src="https://i.stack.imgur.com/TDOt4.png" alt="results"></p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload