VBA Loop to Replicate List of Hundreds of SKUs for Hundreds of Locations

[vlswanson]

I admittedly use VBA very infrequently, but seems like this should have been a simple exercise!

I have a file with 2 lists, one with a thousand SKUs and one with a hundred locations. I want to end up with 2 columns and 100,000 rows, with the 1,000 sku's with a location number next to them

I've named ranges "SKU_List" and a "Location_List", and would appreciate help in making this happen!

Thank you!

-Vikki

 

[ranman256]

paste this code into a module
load the sku's data into sheet : SKU
load the locations into sheet : LOC
then run : combineSku

Sub combineSku()
Dim colSKU As New Collection
Dim colLOC As New Collection
Dim S As Integer, L As Integer
  
On Error GoTo ErrSku

   'LOAD SKU
Sheets("SKU").Activate
Range("A2").Select
While ActiveCell <> ""
  colSKU.Add ActiveCell.Value
  ActiveCell.Offset(1, 0).Select 'NEXT ROW
Wend


   'LOAD LOCations
Sheets("LOC").Activate
Range("A2").Select
While ActiveCell <> ""
  colLOC.Add ActiveCell.Value
  ActiveCell.Offset(1, 0).Select 'NEXT ROW
Wend


'MERGE
Sheets.Add
ActiveSheet.Name = "merge"
Range("a1").Value = "MERGE"
Range("a2").Select

For S = 1 To colSKU.Count
   For L = 1 To colLoc.Count
      ActiveCell.Offset(0, 0).Value = colSKU(S)
      ActiveCell.Offset(0, 1).Value = colLOC(L)
     
      ActiveCell.Offset(1, 0).Select 'NEXT ROW
   Next
skip2Sku:
Next

MsgBox "done"
Exit Sub

ErrSku:
Resume skip2Sku
End Sub

 

[steve the fish]

Where is this list going? Would need to know where to place it. I wouldnt use that last code with 100k rows.

 

[vlswanson]

paste this code into a module
load the sku's data into sheet : SKU
load the locations into sheet : LOC
then run : combineSku

Sub combineSku()
Dim colSKU As New Collection
Dim colLOC As New Collection
Dim S As Integer, L As Integer
 
On Error GoTo ErrSku

   'LOAD SKU
Sheets("SKU").Activate
Range("A2").Select
While ActiveCell <> ""
  colSKU.Add ActiveCell.Value
  ActiveCell.Offset(1, 0).Select 'NEXT ROW
Wend


   'LOAD LOCations
Sheets("LOC").Activate
Range("A2").Select
While ActiveCell <> ""
  colLOC.Add ActiveCell.Value
  ActiveCell.Offset(1, 0).Select 'NEXT ROW
Wend


'MERGE
Sheets.Add
ActiveSheet.Name = "merge"
Range("a1").Value = "MERGE"
Range("a2").Select

For S = 1 To colSKU.Count
   For L = 1 To colLoc.Count
      ActiveCell.Offset(0, 0).Value = colSKU(S)
      ActiveCell.Offset(0, 1).Value = colLOC(L)
    
      ActiveCell.Offset(1, 0).Select 'NEXT ROW
   Next
skip2Sku:
Next

MsgBox "done"
Exit Sub

ErrSku:
Resume skip2Sku
End Sub

While it took a while to run, I now have a list of 126k SKU /Location combinations (there was more than 1000 SKUs and 100 locations ), which is exactly what i was looking for Thank you!