Hi All,
I am new to VBA and this forum so please excuse me if my question seems too easy.
So, I have a large quantity of spreadsheets that contain sales order information over time. The layout of each file is consistent so the columns do not change, only the record counts which can range from 15,000 rows to 150,000 rows of data.
Each sheet contains the following information in the first 4 columns:
Column A: OrderID
Column B: ItemID
Column C: OrderUnit
Column D: OrderQty
For each file, I need to add a fifth column (E) titled (OrderLineNo) to record the Line Number for each ItemID listed by OrderID preferably in sequential order. I have been doing this manually for the past days and it is taking a very long time
Below is the code I have put together so far, (SORRY ABOUT THE FORMAT - I COULDN'T WORK OUT HOW TO DISPLAY IT CORRECTLY WITH THE AVAILABLE TOOLBAR!) but need some help with getting it where it needs to be to finish it off.
Sub LineNumbers()
On Error GoTo ErrorHandler
' Declarations
Dim Wks As Worksheet
Dim startTime As Long, endtime As Long, timeToComplete As Long
Dim OrderRng As Range
Dim OrderID As Long
Dim OrderArray As Variant
Dim msg As String
Dim i As Long
' Activate timer
startTime = Timer
' Turn off screen refreshing
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
' Set reference to worksheet
Set Wks = ThisWorkbook.Sheets("2015")
' Loop through all the orders and construct an array with unique order numbers ->
OrderID = 0
i = 0
For Each OrderRng In Wks.Range("B2", Wks.Range("B" & Rows.Count).End(xlUp))
If OrderID <> OrderRng.Offset(0, 1).Value Then
' Create an array containing the order numbers ->
ReDim Preserve OrderArray(i) As Variant
OrderArray(i) = OrderRng.Offset(0, 1).Value
End If
i = i + 1
Next OrderRng
' Display message in status bar
Application.StatusBar = "Creating order line numbers..."
' Now loop through all the order lines and number them in sequential order ->
' Display message in status bar
Application.StatusBar = "Order line numbering completed..."
' Calculate end time and display message
endtime = Timer
timeToComplete = endtime - startTime
MsgBox "Order line numbers generated.", vbOKOnly, "Task Completed in : " & timeToComplete & " Seconds"
' Turn on screen refreshing
Application.StatusBar = False
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
ErrorHandler:
If Err.Number <> 0 Then
msg = "Error # " & Str(Err.Number) & " was generated by " _
& Err.Source & Chr(13) & Err.Description
MsgBox msg, , "Error"
End If
Resume Next
End Sub
Display More
I have seen similar requests elsewhere but nothing stands out that really matches my needs and am hoping that somebody here might have done some similar previously and is willing to take a look at it for me.
Enclosed is a non-macro file containing sample of the data with expected output in column E.
Thanks,
Fritz