dimanche 1 mars 2015

VBA Excel : Insert Subtotal before every set of unique row data

I'm new to Excel VBA.


Attached is the list of Toll Charge Information for Vehicles. I want to insert blank row before every continuous Vehicle Data (Column D). Also I want to sum the Toll charges (subtotal) of the repeated vehicle to the blank row.


I'm stuck. Can anybody help?



Sub AddBlankRows()
'
Dim iRow As Integer, iCol As Integer
Dim iRow1 As Integer, iCol1 As Integer
Dim iSum As Integer

Dim oRng As Range

'Set oRng = Range("a1")

'iRow = oRng.Column
'iCol = oRng.Column
iRow = 1
iCol = 4
iSum = 0
iSum1 = 0

iRow1 = 2
iCol1 = 4
Do

While Cells(iRow1, iCol1) = Cells(iRow1 + 1, iCol1)
'iSum = iSum + Cells(iRow1 + 1, iCol1 + 3)
iSum = Cells(iRow1, iCol1 + 3)
iSum1 = iSum + Cells(iRow1 + 1, iCol1 + 3)
iSum = 0
iRow1 = iRow1 + 1
Wend

If Cells(iRow + 1, iCol) <> Cells(iRow, iCol) Then
'Do

'Loop While Not Cells(iRow, iCol).Text = ""
'Cells(iRow + 1, iCol).EntireRow.Insert shift:=xlDown
Rows(iRow + 1 & ":" & iRow + 4).Insert
Cells(iRow + 1, iCol - 3).Value = "<table1_Group1 vcTollPoint=""Transactions for Tag # " & _
Cells(iRow + 1, iCol - 3).Offset(4, 2).Value & " Plate # " & _
Cells(iRow + 1, iCol - 3).Offset(4, 3).Value & """" & " textbox60=""Transactions for Tag # " & _
Cells(iRow + 1, iCol - 3).Offset(4, 2).Value & " Plate # " & _
Cells(iRow + 1, iCol - 3).Offset(4, 3).Value & ":"" textbox64=" & iSum1
iRow = iRow + 5
Else
iRow = iRow + 1
End If
'
Loop While Not Cells(iRow, iCol).Text = ""
'
End Sub

Aucun commentaire:

Enregistrer un commentaire