I'm using an array that converts two columns into different string arrays and sorts the row based on the contents of the arrays to a different worksheet. The sorting code works but skips the first row of data whilst also removing the leading Zeros.
I already have a working code that loops through each cell in the 2 columns and adds it to the arrays without removing leading zeros or skipping the first row but for speed reasons I'm trying to avoid that. I'm not entirely sure if this would increase the speed of the macro so please correct me if I'm wrong.
I'm new to coding so any help you can provide in solving this would be greatly appreciated.
I already have a working code that loops through each cell in the 2 columns and adds it to the arrays without removing leading zeros or skipping the first row but for speed reasons I'm trying to avoid that. I'm not entirely sure if this would increase the speed of the macro so please correct me if I'm wrong.
I'm new to coding so any help you can provide in solving this would be greatly appreciated.
Code:
Sub Sort_Legacy()
Dim MarketQ As Worksheet: Set MarketQ = ThisWorkbook.Worksheets("Weekly")
Dim ContractCodes() As String, WS_Name() As String
Dim a As Long, c As Long, y As Long, d As Long: a = MarketQ.Cells(Rows.Count, 1).End(xlUp).Row ' a = the last used row number on Worksheet "Weekly"
Column_ContractC = "AG" ' not all values are numbers
Column_WorksheetN = "AH"
ReDim ContractCodes(1 To a - 1)
ReDim WS_Name(1 To a - 1)
Dim CCRange As Range: Set CCRange = Range(MarketQ.Cells(2, Column_ContractC), MarketQ.Cells(a, Column_ContractC))
Dim WSNR As Range: Set WSNR = Range(MarketQ.Cells(2, Column_WorksheetN), MarketQ.Cells(a, Column_WorksheetN))
WS_Name = Split(Join(WorksheetFunction.Transpose(WSNR), ","), ",")
ContractCodes = Split(Join(WorksheetFunction.Transpose(CCRange), ","), ",") 'Converting range into string array(works)
For y = 2 To a 'Sorting code works but not for first row of data
For c = 1 To UBound(ContractCodes)
If MarketQ.Cells(y, Column_ContractC).Value2 = ContractCodes(c) Then
Dim MarketW As Worksheet: Set MarketW = ThisWorkbook.Worksheets(WS_Name(c))
d = MarketW.Cells(Rows.Count, 1).End(xlUp).Row
Range(MarketW.Cells(d + 1, 1), MarketW.Cells(d + 1, Column_ContractC)).Value2 = Range(MarketQ.Cells(y, "A"), MarketQ.Cells(y, Column_ContractC)).Value2
End If
Next c
Next
End Sub