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