nvnazeer
01-17-2008, 11:57 AM
Hi! Xld,
I need your expert help again please. I used the below VBA code provided by you. It seemed to work until I hit a snag in one of my spreadsheets.
For some reason the macro is not working on one of my spreadsheets. I have traced down the error to the attached extract from my spreadsheet. I tried my best to sort this out, but could not. I need some help please. After you run the macro you can see that the total quantity does not match and also there are still some line with same CMN Part No.
If you can tell me the reason then I can maybe re-format the spreadsheet to avoid such occurence in future.
Thanks
Nazeer
Public Sub ProcessData()
Dim i As Long
Dim LastRow As Long
Dim StartRow As Long
Dim rng As Range
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With
With ActiveSheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
StartRow = 1
For i = 2 To LastRow
If .Cells(i, "A").Value <> .Cells(i - 1, "A").Value Then
.Cells(i, "C").Value = Application.SumIf(.Columns(1), .Cells(i, "A").Value, .Columns(3))
Else
If rng Is Nothing Then
Set rng = .Rows(i)
Else
Set rng = Union(rng, .Rows(i))
End If
End If
Next i
If Not rng Is Nothing Then rng.Delete
End With
With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With
End Sub kind Forum member
I need your expert help again please. I used the below VBA code provided by you. It seemed to work until I hit a snag in one of my spreadsheets.
For some reason the macro is not working on one of my spreadsheets. I have traced down the error to the attached extract from my spreadsheet. I tried my best to sort this out, but could not. I need some help please. After you run the macro you can see that the total quantity does not match and also there are still some line with same CMN Part No.
If you can tell me the reason then I can maybe re-format the spreadsheet to avoid such occurence in future.
Thanks
Nazeer
Public Sub ProcessData()
Dim i As Long
Dim LastRow As Long
Dim StartRow As Long
Dim rng As Range
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With
With ActiveSheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
StartRow = 1
For i = 2 To LastRow
If .Cells(i, "A").Value <> .Cells(i - 1, "A").Value Then
.Cells(i, "C").Value = Application.SumIf(.Columns(1), .Cells(i, "A").Value, .Columns(3))
Else
If rng Is Nothing Then
Set rng = .Rows(i)
Else
Set rng = Union(rng, .Rows(i))
End If
End If
Next i
If Not rng Is Nothing Then rng.Delete
End With
With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With
End Sub kind Forum member