PDA

View Full Version : Create Summary



javeed
02-07-2016, 12:22 AM
Hi,

The below script works fine but only when the Column B (product name) and values compulsory from next Column C (count value) then it creates the summary.
Whereas in my case the Column B (product name) and the values are in Column F (count value), it needs some modification in script so that it will get the values from Column F instead of next Column.

Thanks in advance.



Sub Summary()
Dim Rng As Excel.Range
Dim arrProducts() As String
Dim I As Long

Set Rng = Sheet1.Range("B2:B20000")


arrProducts = getSumOfCountArray(Rng)


Sheet2.Range("A1:B1").Value = Array("product name", "count value")


' go through array and output to Sheet2

'For I = 0 To UBound(arrProducts, 2)
For I = 0 To UBound(arrProducts, 2)
Sheet2.Cells(I + 2, "A").Value = arrProducts(0, I)
Sheet2.Cells(I + 2, "B").Value = arrProducts(1, I)
Next
End Sub



' Pass in the range of the products
Function getSumOfCountArray(ByRef rngProduct As Excel.Range) As String()
Dim arrProducts() As String
Dim I As Long, j As Long
Dim index As Long


ReDim arrProducts(1, 0)


For j = 1 To rngProduct.Rows.Count
index = getProductIndex(arrProducts, rngProduct.Cells(j, 1).Value)
If (index = -1) Then
' create value in array
ReDim Preserve arrProducts(1, I)
arrProducts(0, I) = rngProduct.Cells(j, 1).Value ' product name
arrProducts(1, I) = rngProduct.Cells(j, 2).Value ' count value
I = I + 1
Else
' value found, add to id
arrProducts(1, index) = arrProducts(1, index) + rngProduct.Cells(j, 2).Value
End If
Next


getSumOfCountArray = arrProducts
End Function



Function getProductIndex(ByRef arrProducts() As String, ByRef strSearch As String) As Long
' returns the index of the array if found
Dim I As Long
For I = 0 To UBound(arrProducts, 2)
If (arrProducts(0, I) = strSearch) Then
getProductIndex = I
Exit Function
End If
Next


' not found
getProductIndex = -1
End Function

SamT
02-07-2016, 11:59 AM
rngProduct.Cells(j, 2).Value means Cells(j, Column Number) Where Column Number is referenced from the first (only) column in rngProduct