Hi,
From my understanding to your questions, you are going to perform one summarization of each Buyer ID and decide whether the cell is in the last
row.
[I assume the worksheet as below:]
A B
1 Buyer ID value
2 12 2
3 23 34
4 34 2
5 5 234
6 6 2
7 6 234
8 7 2
9 7 234
10 34 2
11 23 45
12 12 2
13 5 234
1. sum each buyer ID value
'--Code begin -----------------------------------
Sub subtotal()
Dim oSht As Worksheet
Dim oWorkingRange, cell As Range
Dim oArray()
Dim subtotal As Integer
Set oSht = ActiveSheet
oArray = Array(0)
'specify the range
Set oWorkingRange = oSht.Range("A2:A13")
For Each cell In oWorkingRange.Cells
If Not CheckWhetherExisting(oArray, cell.value) Then
subtotal = 0
Call InputNewValuetoArray(oArray, cell.value)
Call sum(subtotal, cell.value, oWorkingRange)
End If
Next
End Sub
Sub sum(ByRef total As Integer, value, oRange)
Dim cell As Range
For Each cell In oRange.Cells
If cell.value = value Then
'sum the value
total = total + Range(CStr("b" & cell.Row)).value
End If
Next
'output the result for each ID
Debug.Print "ID:" & value & " Total:" & total
End Sub
Function IncreaseArrayByOne(oArray)
ReDim Preserve oArray(UBound(oArray) + 1)
IncreaseArrayByOne = oArray
End Function
Sub InputNewValuetoArray(ByRef oArray, value)
oArray = IncreaseArrayByOne(oArray)
oArray(UBound(oArray)) = CStr(value)
End Sub
Function CheckWhetherExisting(oArray, value)
If UBound(oArray) <> 0 Then
Dim boundary
boundary = UBound(oArray)
For i = 1 To boundary
If oArray(i) = CStr(value) Then
CheckWhetherExisting = True
Exit Function
End If
Next
Else
CheckWhetherExisting = False
End If
End Function
'--Code end ------------------------------------
2. check whether the cell is the last one in the column
We can use the UsedRange property to obtain the used range object. Then we can obtain the cells count in the Range. We can check whether
the row of the cell is equal to the one of the last cell in the used Range.
'--Code start ------------------------------------
Sub ValidateLastCell()
Dim oSht As Worksheet
Dim oAdr As String
Dim cellsCount, lastRow As Integer
Set oSht = ActiveSheet
cellsCount = oSht.UsedRange.Cells.Count
lastRow = oSht.UsedRange.Cells(cellsCount).Row
If lastRow = cell.Row Then
'Perform the operation according to your scenario
End If
End Sub
'--Code end -------------------------------------
Please feel free to let me know if you have any further questions.
Best Regards,
Wei-Dong Xu
Microsoft Product Support Services
Get Secure! -
www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.