API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Apr 27th, 2017
95
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
VB.NET 36.42 KB | None | 0 0
raw download clone embed print report
  1. Private Sub CommandButton1_Click()
  2.  Dim a As Range, ASIN As Variant, X As Integer
  3. ' Dim ASIN(2000, 2000) As Variant
  4.  c = 1
  5.  l = 0
  6. For Each a In Sheets("Final Input").Rows
  7.  
  8.   If Sheets("Final Input").Cells(a.Row, 1).Value = "" Then
  9.   Exit For
  10.   End If
  11.  
  12.   l = l + 1
  13. '  Next a
  14.  
  15. '   MsgBox l
  16.  
  17. '  count = j
  18. 'While (count > 0)
  19. If a.Row > 1 Then
  20. ASIN = Worksheets("Final Input").Cells(a.Row, 1)
  21. End If
  22. result = checkNew(ASIN)
  23. If result = 0 Then
  24.  
  25.  Worksheets("Final GL").Range("Q1").Offset(c) = ASIN
  26.  c = c + 1
  27. '     count = count - 1
  28.      End If
  29. '     Wend
  30. Next a
  31.  
  32. End Sub
  33.  
  34. Public Function checkNew(CheckValue As Variant) As Integer
  35.  
  36. j = 0
  37. For Each o In Sheets("Final Input").Rows
  38.   If Sheets("Final Input").Cells(o.Row, 15).Value = "" Then
  39.   Exit For
  40.   End If
  41.  
  42.   j = j + 1
  43. Next o
  44.  
  45. 'MsgBox j
  46.  
  47. For Z = 1 To j
  48.  y = Worksheets("Final Input").Range("O1").Offset(Z)
  49.  If y = CheckValue Then
  50.  checkNew = 1
  51.  Exit Function
  52.  Else: checkNew = 0
  53.  End If
  54.  Next Z
  55. End Function
  56.  
  57.  
  58. Private Sub CommandButton2_Click()
  59.  
  60.  
  61. Dim i As Integer, X As Integer, w As Integer, j As Integer
  62. Dim ASIN2(2000, 2000) As Variant
  63.  Dim CMTVar As Integer
  64.  Dim count As Integer, OrderV As Integer
  65.  
  66. Dim ASIN As Integer, HPC As Integer, Beauty As Integer
  67.  
  68. ASIN = ThisWorkbook.Sheets("Final Output").ASIN.Value
  69. GLHPC = ThisWorkbook.Sheets("Final Output").GLHPC.Value
  70. GLBeauty = ThisWorkbook.Sheets("Final Output").GLBeauty.Value
  71. GLBaby = ThisWorkbook.Sheets("final Output").GLBaby.Value
  72. GLGrocery = ThisWorkbook.Sheets("Final Output").GLGrocery.Value
  73. GLOS = ThisWorkbook.Sheets("Final Output").GLOS.Value
  74. GLHK = ThisWorkbook.Sheets("Final Output").GLHK.Value
  75.  
  76.  Call HULHPCGL
  77.  
  78. End Sub
  79.  
  80. Sub HULHPCGL()
  81.  
  82. Dim gl As Integer
  83.  
  84. gl = (GLHPC * ASIN) / 100
  85. 'MsgBox gl
  86. Call HULASINList("Health & Personal Care", gl)
  87. Call HULBabyGL
  88.  
  89. End Sub
  90. Sub HULBabyGL()
  91. Dim gl As Integer
  92.  
  93. gl = (GLBaby * ASIN) / 100
  94. 'MsgBox gl
  95.  
  96. HULASINList "Baby", gl
  97. Call HULBeautyGL
  98.  
  99. End Sub
  100. Sub HULBeautyGL()
  101. Dim gl As Integer
  102.  
  103. gl = (GLBeauty * ASIN) / 100
  104. 'MsgBox gl
  105.  
  106. HULASINList "Beauty", gl
  107. Call HULGroceryGL
  108.  
  109. End Sub
  110. Sub HULGroceryGL()
  111. Dim gl As Integer
  112.  
  113. gl = (GLGrocery * ASIN) / 100
  114.  
  115. HULASINList "Grocery", gl
  116.  
  117. End Sub
  118.  
  119. Sub HULASINList(HULGLvalue As Variant, GLNumber)
  120.  
  121.    Sheets("Final Input").Select
  122.     Sheets("Final Input").Range("A1").Select
  123.     Selection.AutoFilter
  124.     ActiveSheet.Range("$A:$O").AutoFilter Field:=2, Criteria1:="=HING3" _
  125.         , Operator:=xlOr, Criteria2:="=HING3 20"
  126.     ActiveSheet.Range("$A:$O").AutoFilter Field:=12, Criteria1:= _
  127.         HULGLvalue
  128.        
  129. '    Sheets("Final Input").Select
  130.      Sheets("Final Input").Range("A:F").Select
  131.     Application.CutCopyMode = False
  132.     Selection.Copy
  133.     Sheets("Final GL").Select
  134.    
  135.     ThisWorkbook.Sheets("Final GL").Range("A1").Select
  136.     ActiveSheet.Paste
  137.    
  138.     Sheets("Final Input").Select
  139.  
  140.     Sheets("Final Input").Columns("I:I").Select
  141.     Application.CutCopyMode = False
  142.     Selection.Copy
  143.     Sheets("Final GL").Select
  144.     Sheets("Final GL").Range("G1").Select
  145.     ActiveSheet.Paste
  146.     Sheets("Final Input").Select
  147.     Sheets("Final Input").Columns("J:K").Select
  148.     Application.CutCopyMode = False
  149.     Selection.Copy
  150.     Sheets("Final GL").Select
  151.     Sheets("Final GL").Range("H1").Select
  152.     ActiveSheet.Paste
  153.     Sheets("Final Input").Select
  154.     Sheets("Final Input").Columns("N:N").Select
  155.     Application.CutCopyMode = False
  156.     Selection.Copy
  157.     Sheets("Final GL").Select
  158.     Sheets("Final GL").Columns("J:J").Select
  159.     ActiveSheet.Paste
  160.     Sheets("Final Input").Select
  161.     Sheets("Final Input").Columns("L:L").Select
  162.     Application.CutCopyMode = False
  163.     Selection.Copy
  164.     Sheets("Final GL").Select
  165.     Sheets("Final GL").Columns("R:R").Select
  166.     ActiveSheet.Paste
  167.     Sheets("Final GL").Range("G1:J1").Select
  168.     Application.CutCopyMode = False
  169.     With Selection.Interior
  170.         .Pattern = xlSolid
  171.         .PatternColorIndex = xlAutomatic
  172.         .ThemeColor = xlThemeColorLight1
  173.         .TintAndShade = 0
  174.         .PatternTintAndShade = 0
  175.     End With
  176.     Sheets("Final GL").Range("R1").Select
  177.     With Selection.Interior
  178.         .Pattern = xlSolid
  179.         .PatternColorIndex = xlAutomatic
  180.         .ThemeColor = xlThemeColorLight1
  181.         .TintAndShade = 0
  182.         .PatternTintAndShade = 0
  183.     End With
  184.    
  185.     With Selection.Borders(xlEdgeTop)
  186.         .LineStyle = xlContinuous
  187.         .ColorIndex = 0
  188.         .TintAndShade = 0
  189.         .Weight = xlThin
  190.     End With
  191.     With Selection.Borders(xlEdgeBottom)
  192.         .LineStyle = xlContinuous
  193.         .ColorIndex = 0
  194.         .TintAndShade = 0
  195.         .Weight = xlThin
  196.     End With
  197.     With Selection.Borders(xlEdgeRight)
  198.         .LineStyle = xlContinuous
  199.         .ColorIndex = 0
  200.         .TintAndShade = 0
  201.         .Weight = xlThin
  202.     End With
  203.     With Selection.Borders(xlInsideVertical)
  204.         .LineStyle = xlContinuous
  205.         .ColorIndex = 0
  206.         .TintAndShade = 0
  207.         .Weight = xlThin
  208.     End With
  209.     With Selection.Borders(xlInsideHorizontal)
  210.         .LineStyle = xlContinuous
  211.         .ColorIndex = 0
  212.         .TintAndShade = 0
  213.         .Weight = xlThin
  214.     End With
  215.    
  216.    
  217.     With Selection.Borders(xlEdgeTop)
  218.         .LineStyle = xlContinuous
  219.         .ColorIndex = 0
  220.         .TintAndShade = 0
  221.         .Weight = xlThin
  222.     End With
  223.     With Selection.Borders(xlEdgeBottom)
  224.         .LineStyle = xlContinuous
  225.         .ColorIndex = 0
  226.         .TintAndShade = 0
  227.         .Weight = xlThin
  228.     End With
  229.     With Selection.Borders(xlEdgeRight)
  230.         .LineStyle = xlContinuous
  231.         .ColorIndex = 0
  232.         .TintAndShade = 0
  233.         .Weight = xlThin
  234.     End With
  235.     With Selection.Borders(xlInsideVertical)
  236.         .LineStyle = xlContinuous
  237.         .ColorIndex = 0
  238.         .TintAndShade = 0
  239.         .Weight = xlThin
  240.     End With
  241.     With Selection.Borders(xlInsideHorizontal)
  242.         .LineStyle = xlContinuous
  243.         .ColorIndex = 0
  244.         .TintAndShade = 0
  245.         .Weight = xlThin
  246.     End With
  247.     With Selection.Font
  248.         .ThemeColor = xlThemeColorDark1
  249.         .TintAndShade = 0
  250.     End With
  251.     With Selection.Font
  252.         .ColorIndex = xlAutomatic
  253.         .TintAndShade = 0
  254.     End With
  255.     Selection.Borders(xlDiagonalDown).LineStyle = xlNone
  256.     Selection.Borders(xlDiagonalUp).LineStyle = xlNone
  257.     With Selection.Borders(xlEdgeLeft)
  258.         .LineStyle = xlContinuous
  259.         .ThemeColor = 1
  260.         .TintAndShade = 0
  261.         .Weight = xlThin
  262.     End With
  263.     With Selection.Borders(xlEdgeTop)
  264.         .LineStyle = xlContinuous
  265.         .ThemeColor = 1
  266.         .TintAndShade = 0
  267.         .Weight = xlThin
  268.     End With
  269.     With Selection.Borders(xlEdgeBottom)
  270.         .LineStyle = xlContinuous
  271.         .ThemeColor = 1
  272.         .TintAndShade = 0
  273.         .Weight = xlThin
  274.     End With
  275.     With Selection.Borders(xlEdgeRight)
  276.         .LineStyle = xlContinuous
  277.         .ThemeColor = 1
  278.         .TintAndShade = 0
  279.         .Weight = xlThin
  280.     End With
  281.     With Selection.Borders(xlInsideVertical)
  282.         .LineStyle = xlContinuous
  283.         .ThemeColor = 1
  284.         .TintAndShade = 0
  285.         .Weight = xlThin
  286.     End With
  287.     With Selection.Borders(xlInsideHorizontal)
  288.         .LineStyle = xlContinuous
  289.         .ThemeColor = 1
  290.         .TintAndShade = 0
  291.         .Weight = xlThin
  292.     End With
  293.     Sheets("Final GL").Range("A1:J1").Select
  294.     With Selection.Font
  295.         .ThemeColor = xlThemeColorDark1
  296.         .TintAndShade = 0
  297.     End With
  298.      
  299. ASINListOutput GLNumber
  300. End Sub
  301.  
  302.  
  303. Private Sub list_click()
  304.  
  305. Dim i As Integer, X As Integer, w As Integer, j As Integer
  306. Dim ASIN2(2000, 2000) As Variant
  307.  Dim CMTVar As Integer
  308.  Dim count As Integer, OrderV As Integer
  309.  
  310. Dim ASIN As Integer, HPC As Integer, Beauty As Integer
  311.  
  312. ASIN = ThisWorkbook.Sheets("Final Output").ASIN.Value
  313. GLHPC = ThisWorkbook.Sheets("Final Output").GLHPC.Value
  314. GLBeauty = ThisWorkbook.Sheets("Final Output").GLBeauty.Value
  315. GLBaby = ThisWorkbook.Sheets("final Output").GLBaby.Value
  316. GLGrocery = ThisWorkbook.Sheets("Final Output").GLGrocery.Value
  317. GLOS = ThisWorkbook.Sheets("Final Output").GLOS.Value
  318. GLHK = ThisWorkbook.Sheets("Final Output").GLHK.Value
  319.  
  320.  Call BabyGL
  321.  
  322. End Sub
  323.  
  324. 'Sub HPCGL()
  325. '
  326. 'Dim gl As Integer
  327. '
  328. 'gl = (GLHPC * ASIN) / 100
  329. 'MsgBox gl
  330. 'Call ASINList("Health & Personal Care", gl)
  331. '
  332. ''Call HomeKitchen
  333. 'Call BabyGL
  334. '
  335. 'End Sub
  336. Sub BabyGL()
  337. Dim gl As Integer
  338. gl = (GLBaby * ASIN) / 100
  339. MsgBox gl
  340. ASINList "Baby", gl
  341.  
  342. 'Call BeautyGL
  343.  
  344. 'End Sub
  345. 'Sub BeautyGL()
  346. 'Dim gl As Integer
  347. '
  348. 'gl = (GLBeauty * ASIN) / 100
  349. 'MsgBox gl
  350. 'ASINList "Beauty", gl
  351. 'Call GroceryGL
  352. '
  353. 'End Sub
  354. 'Sub GroceryGL()
  355. 'Dim gl As Integer
  356. 'gl = (GLGrocery * ASIN) / 100
  357. 'MsgBox gl
  358. 'ASINList "Grocery", gl
  359. 'Call OfficeProduct
  360. '
  361. 'End Sub
  362. 'Sub OfficeProduct()
  363. 'Dim gl As Integer
  364. 'gl = (GLOS * ASIN) / 100
  365. 'MsgBox gl
  366. 'ASINList "Office Product", gl
  367. 'Call HomeKitchen
  368.  
  369. End Sub
  370. Sub HomeKitchen()
  371.  
  372. Dim gl As Integer
  373. gl = (GLHK * ASIN) / 100
  374. MsgBox gl
  375. Sheets("Final GL").Select
  376.   Sheets("Final GL").Range("A2").Select
  377.     Sheets("Final GL").Range(Selection, Selection.End(xlDown)).Select
  378.     Sheets("Final GL").Range(Selection, Selection.End(xlToRight)).Select
  379.     Selection.ClearContents
  380.    
  381.     Sheets("Final Input").Select
  382.     Sheets("Final Input").Range("A1").Select
  383.     Selection.AutoFilter
  384.     ActiveSheet.Range("$A$1:$S$4340").AutoFilter Field:=12, Criteria1:=Array( _
  385.         "Home", "Home Improvement", "Kitchen"), Operator:=xlFilterValues
  386.     Sheets("Final Input").Columns("A:F").Select
  387.     Selection.Copy
  388.     Sheets("Final GL").Select
  389.     Sheets("Final GL").Range("A1").Select
  390.     ActiveSheet.Paste
  391.     Sheets("Final Input").Select
  392.     Sheets("Final Input").Columns("I:K").Select
  393.     Application.CutCopyMode = False
  394.     Selection.Copy
  395.     Sheets("Final GL").Select
  396.     Sheets("Final GL").Range("G1").Select
  397.     ActiveSheet.Paste
  398.     Sheets("Final Input").Select
  399.  
  400.     Sheets("Final Input").Columns("N:N").Select
  401.     Application.CutCopyMode = False
  402.     Selection.Copy
  403.     Sheets("Final GL").Select
  404.     Sheets("Final GL").Range("J1").Select
  405.     ActiveSheet.Paste
  406.  
  407.     Sheets("Final Input").Select
  408.     Sheets("Final Input").Columns("L:L").Select
  409.     Application.CutCopyMode = False
  410.    Selection.Copy
  411.     Sheets("Final GL").Select
  412.    Sheets("Final GL").Columns("R:R").Select
  413.     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  414.         :=False, Transpose:=False
  415.  
  416.  
  417.     Sheets("Final GL").Range("G1:J1").Select
  418.     Application.CutCopyMode = False
  419.     With Selection.Interior
  420.         .Pattern = xlSolid
  421.         .PatternColorIndex = xlAutomatic
  422.         .ThemeColor = xlThemeColorLight1
  423.         .TintAndShade = 0
  424.         .PatternTintAndShade = 0
  425.     End With
  426. '   MsgBox gl
  427.  
  428. ASINListOutput gl
  429.  
  430.  
  431.  
  432.  
  433.  
  434. End Sub
  435.  
  436. Public Function ASINList(GLValue As Variant, GLNumber As Integer)
  437.  
  438. 'Sort
  439.   ThisWorkbook.Sheets("Final GL").Select
  440.  
  441.     ThisWorkbook.Sheets("Final GL").Range("A2").Select
  442.    ThisWorkbook.Sheets("Final GL").Range(Selection, Selection.End(xlDown)).Select
  443.  ThisWorkbook.Sheets("Final GL").Range("A:J").Select
  444.    Selection.ClearContents
  445.  
  446.     ThisWorkbook.Sheets("Final Input").Select
  447.  
  448.    ThisWorkbook.Sheets("Final Input").Rows("1:1").Select
  449.    Selection.AutoFilter
  450.    ThisWorkbook.Sheets("Final Input").Range("$A$1:$R$2088").AutoFilter Field:=12, Criteria1:= _
  451.         GLValue
  452.   ThisWorkbook.Sheets("Final Input").Columns("A:F").Select
  453.     Selection.Copy
  454.    ThisWorkbook.Sheets("Final GL").Select
  455.    ThisWorkbook.Sheets("Final GL").Columns("A:A").Select
  456.    ActiveSheet.Paste
  457.  
  458.    Sheets("Final Input").Select
  459.     Sheets("Final Input").Columns("I:K").Select
  460.     Application.CutCopyMode = False
  461.    Selection.Copy
  462.     Sheets("Final GL").Select
  463.    Sheets("Final GL").Columns("G:G").Select
  464.     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  465.         :=False, Transpose:=False
  466.        
  467.        
  468.       Sheets("Final Input").Select
  469.     Sheets("Final Input").Columns("L:L").Select
  470.     Application.CutCopyMode = False
  471.    Selection.Copy
  472.     Sheets("Final GL").Select
  473.    Sheets("Final GL").Columns("R:R").Select
  474.     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  475.         :=False, Transpose:=False
  476.        
  477.        
  478.        
  479.        Sheets("Final Input").Select
  480.     Sheets("Final Input").Columns("N:N").Select
  481.     Application.CutCopyMode = False
  482.     Selection.Copy
  483.     Sheets("Final GL").Select
  484.    Sheets("Final GL").Columns("J:J").Select
  485.     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  486.         :=False, Transpose:=False
  487.     Sheets("Final GL").Range("A1:J1").Select
  488.     Application.CutCopyMode = False
  489.     Selection.AutoFilter
  490.     ActiveSheet.Range("$A$1:$J$2000").AutoFilter Field:=2, Criteria1:="=HING3", _
  491.         Operator:=xlOr, Criteria2:="=HING3 20"
  492.     Sheets("Final GL").Rows("10:10").Select
  493.     Sheets("Final GL").Range(Selection, Selection.End(xlDown)).Select
  494.    Selection.Delete Shift:=xlUp
  495.     Selection.AutoFilter
  496.     Sheets("Final GL").Range("A2").Select
  497.    Sheets("Final GL").Columns("I:I").Select
  498.     Selection.NumberFormat = "0.0%"
  499.     Sheets("Final GL").Columns("J:J").Select
  500.     Selection.NumberFormat = "0.0%"
  501.  
  502. ASINListOutput GLNumber
  503.  
  504. End Function
  505.  
  506.  
  507. Sub ASINListOutput(GLNumber As Variant)
  508.  
  509. Dim i As Integer, X As Integer, w As Integer, j As Integer
  510. Dim ASIN2(2000, 2000) As Variant
  511.  Dim count As Integer
  512.  
  513. OrderValue = ThisWorkbook.Sheets("Final Output").Order.Value
  514. CMTValue = ThisWorkbook.Sheets("Final Output").CMTV.Value
  515. PriceMValue = ThisWorkbook.Sheets("Final Output").PriceMove.Value
  516. MarginValue = ThisWorkbook.Sheets("Final Output").Margin.Value
  517. 'NArrivalValue = ThisWorkbook.Sheets("Final Output").NewASIN.Value
  518. SlowMoveValue = ThisWorkbook.Sheets("Final Output").SlowMove.Value
  519. '
  520. 'MsgBox OrderValue
  521. 'MsgBox CMTValue
  522. 'MsgBox GLNumber
  523.  
  524. OrderV = (GLNumber * OrderValue / 100)
  525. CMTVar = (GLNumber * CMTValue / 100)
  526. PriceMV = (GLNumber * PriceMValue) / 100
  527. MarginV = (GLNumber * MarginValue) / 100
  528. SMoveV = (GLNumber * SlowMoveValue) / 100
  529. 'NArrival = (GLNumber * NArrivalValue) / 100
  530.  
  531. 'MsgBox OrderV
  532. 'MsgBox CMTVar
  533.  
  534.   'Sort the quantity column
  535.  Worksheets("Final GL").Select
  536.     Worksheets("Final GL").Range("G:G").Select
  537.     ActiveWorkbook.Worksheets("Final GL").Sort.SortFields.Clear
  538.     ActiveWorkbook.Worksheets("Final GL").Sort.SortFields.Add Key:=Range("G1"), _
  539.         SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
  540.     With ActiveWorkbook.Worksheets("Final GL").Sort
  541.         .SetRange Range("A2:j575")
  542.         .Header = xlNo
  543.         .MatchCase = False
  544.         .Orientation = xlTopToBottom
  545.         .SortMethod = xlPinYin
  546.         .Apply
  547.     End With
  548.  
  549. With ThisWorkbook.Worksheets("Final GL")
  550.  
  551. i = 500
  552. X = 0
  553. w = 11
  554. For j = 1 To i
  555.      ASIN2(j, X) = .Range("A1").Offset(j, X)
  556.      .Range("A1").Offset(j, w).Value = ASIN2(j, X)
  557.  Next j
  558.  End With
  559.  
  560. For Each k In Sheets("Final Output").Rows
  561.   If Sheets("Final Output").Cells(k.Row, 1).Value = "" Then
  562.   Exit For
  563.   End If
  564.  
  565.   l = l + 1
  566.  
  567. Next k
  568.   j = l - 1
  569. 'MsgBox l
  570.  
  571. count = Order
  572. c = 1
  573. r = 0
  574. While (count > 0)
  575. MASIN = Worksheets("Final GL").Range("L1").Offset(c)
  576. MVendor = Worksheets("Final GL").Range("B1").Offset(c)
  577. MTitle = Worksheets("Final GL").Range("C1").Offset(c)
  578. MM = Worksheets("Final GL").Range("D1").Offset(c)
  579. MS = Worksheets("Final GL").Range("E1").Offset(c)
  580. MMG = Worksheets("Final GL").Range("F1").Offset(c)
  581. Productgl = Worksheets("Final GL").Range("R1").Offset(c)
  582.  
  583.  
  584. result = check(MASIN, l)
  585. If result = 0 Then
  586.  
  587.  Worksheets("Final Output").Range("A1").Offset(l + r) = MASIN
  588.  Worksheets("Final Output").Range("B1").Offset(l + r) = MVendor
  589.  Worksheets("Final Output").Range("C1").Offset(l + r) = MTitle
  590.  Worksheets("Final Output").Range("D1").Offset(l + r) = MM
  591.  Worksheets("Final Output").Range("E1").Offset(l + r) = MS
  592.  Worksheets("Final Output").Range("F1").Offset(l + r) = MMG
  593.  Worksheets("Final Output").Range("G1").Offset(l + r) = Productgl
  594.  
  595.      count = count - 1
  596.      r = r + 1
  597.      End If
  598.      c = c + 1
  599.      Wend
  600.  
  601.  
  602. Worksheets("Final GL").Select
  603.     Worksheets("Final GL").Range("H:H").Select
  604.     ActiveWorkbook.Worksheets("Final GL").Sort.SortFields.Clear
  605.     ActiveWorkbook.Worksheets("Final GL").Sort.SortFields.Add Key:=Range("H1"), _
  606.         SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
  607.     With ActiveWorkbook.Worksheets("Final GL").Sort
  608.         .SetRange Range("A2:J5775")
  609.         .Header = xlNo
  610.         .MatchCase = False
  611.         .Orientation = xlTopToBottom
  612.         .SortMethod = xlPinYin
  613.         .Apply
  614.     End With
  615.  
  616. With ThisWorkbook.Worksheets("Final GL")
  617. i = 500
  618. For j = 1 To i
  619.    X = 0
  620.    P = 12
  621.    ASIN2(j, X) = .Range("A1").Offset(j, X)
  622.    .Range("A1").Offset(j, P).Value = ASIN2(j, X)
  623.    Next j
  624. End With
  625.  
  626. count = CMTVar
  627. Start = l + OrderV
  628. k = 1
  629. r = 0
  630.  While (count > 0)
  631.    CMTASIN = Worksheets("Final GL").Range("M1").Offset(k)
  632.    CMTVendor = Worksheets("Final GL").Range("B1").Offset(k)
  633.    CMTitle = Worksheets("Final GL").Range("C1").Offset(k)
  634.    CMTMRP = Worksheets("Final GL").Range("D1").Offset(k)
  635.    CMTSP = Worksheets("Final GL").Range("E1").Offset(k)
  636.    CMTMG = Worksheets("Final GL").Range("F1").Offset(k)
  637.    Productgl = Worksheets("Final GL").Range("R1").Offset(k)
  638.  
  639.   result = check(CMTASIN, Start)
  640.   If result = 0 Then
  641. '      j = (CMT + 1) - count
  642.       Worksheets("Final Output").Range("A1").Offset(Start + r) = CMTASIN
  643.       Worksheets("Final Output").Range("B1").Offset(Start + r) = CMTVendor
  644.       Worksheets("Final Output").Range("C1").Offset(Start + r) = CMTitle
  645.       Worksheets("Final Output").Range("D1").Offset(Start + r) = CMTMRP
  646.       Worksheets("Final Output").Range("E1").Offset(Start + r) = CMTSP
  647.       Worksheets("Final Output").Range("F1").Offset(Start + r) = CMTMG
  648.       Worksheets("Final Output").Range("G1").Offset(Start + r) = Productgl
  649.  
  650.       r = r + 1
  651.       count = count - 1
  652. End If
  653. k = k + 1
  654. Wend
  655.  
  656. Worksheets("Final GL").Select
  657.     Worksheets("Final GL").Range("I:I").Select
  658.     ActiveWorkbook.Worksheets("Final GL").Sort.SortFields.Clear
  659.     ActiveWorkbook.Worksheets("Final GL").Sort.SortFields.Add Key:=Range("I1"), _
  660.         SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
  661.     With ActiveWorkbook.Worksheets("Final GL").Sort
  662.         .SetRange Range("A2:J5775")
  663.         .Header = xlNo
  664.         .MatchCase = False
  665.         .Orientation = xlTopToBottom
  666.         .SortMethod = xlPinYin
  667.         .Apply
  668.     End With
  669.  
  670. With ThisWorkbook.Worksheets("Final GL")
  671. i = 500
  672. For j = 1 To i
  673.    X = 0
  674.    P = 13
  675.    ASIN2(j, X) = .Range("A1").Offset(j, X)
  676.    .Range("A1").Offset(j, P).Value = ASIN2(j, X)
  677.    Next j
  678. End With
  679.  
  680. count = PriceMV
  681. Start = l + OrderV + CMTVar
  682. k = 1
  683. r = 0
  684. While (count > 0)
  685. PASIN = Worksheets("Final GL").Range("N1").Offset(k)
  686. PVendor = Worksheets("Final GL").Range("B1").Offset(k)
  687. PTitle = Worksheets("Final GL").Range("C1").Offset(k)
  688. PM = Worksheets("Final GL").Range("D1").Offset(k)
  689. PS = Worksheets("Final GL").Range("E1").Offset(k)
  690. PMG = Worksheets("Final GL").Range("F1").Offset(k)
  691. Productgl = Worksheets("Final GL").Range("R1").Offset(k)
  692.  
  693. result = check(PASIN, Start)
  694. If result = 0 Then
  695.  
  696.         Worksheets("Final Output").Range("A1").Offset(r + Start) = PASIN
  697.         Worksheets("Final Output").Range("B1").Offset(r + Start) = PVendor
  698.         Worksheets("Final Output").Range("C1").Offset(r + Start) = PTitle
  699.         Worksheets("Final Output").Range("D1").Offset(r + Start) = PM
  700.         Worksheets("Final Output").Range("E1").Offset(r + Start) = PS
  701.         Worksheets("Final Output").Range("F1").Offset(r + Start) = PMG
  702.         Worksheets("Final Output").Range("G1").Offset(r + Start) = Productgl
  703.    
  704.      r = r + 1
  705.      count = count - 1
  706.      End If
  707.      k = k + 1
  708.      Wend
  709.  
  710.  
  711. Worksheets("Final GL").Select
  712.     Worksheets("Final GL").Range("F:F").Select
  713.     ActiveWorkbook.Worksheets("Final GL").Sort.SortFields.Clear
  714.     ActiveWorkbook.Worksheets("Final GL").Sort.SortFields.Add Key:=Range("F1"), _
  715.         SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
  716.     With ActiveWorkbook.Worksheets("Final GL").Sort
  717.         .SetRange Range("A2:J5775")
  718.         .Header = xlNo
  719.         .MatchCase = False
  720.         .Orientation = xlTopToBottom
  721.         .SortMethod = xlPinYin
  722.         .Apply
  723.     End With
  724.  
  725. With ThisWorkbook.Worksheets("Final GL")
  726. i = 500
  727. For j = 1 To i
  728.    X = 0
  729.    P = 14
  730.    ASIN2(j, X) = .Range("A1").Offset(j, X)
  731.    .Range("A1").Offset(j, P).Value = ASIN2(j, X)
  732.    Next j
  733. End With
  734.  
  735. count = MarginV
  736. Start = Start + PriceMV
  737. k = 1
  738. r = 0
  739. While (count > 0)
  740. MASIN = Worksheets("Final GL").Range("O1").Offset(k)
  741. MVendor = Worksheets("Final GL").Range("B1").Offset(k)
  742. MTitle = Worksheets("Final GL").Range("C1").Offset(k)
  743. MM = Worksheets("Final GL").Range("D1").Offset(k)
  744. MS = Worksheets("Final GL").Range("E1").Offset(k)
  745. MMG = Worksheets("Final GL").Range("F1").Offset(k)
  746. Productgl = Worksheets("Final GL").Range("R1").Offset(k)
  747.  
  748. result = check(MASIN, Start)
  749. If result = 0 Then
  750.  
  751.  Worksheets("Final Output").Range("A1").Offset(r + Start) = MASIN
  752.  Worksheets("Final Output").Range("B1").Offset(r + Start) = MVendor
  753.  Worksheets("Final Output").Range("C1").Offset(r + Start) = MTitle
  754.  Worksheets("Final Output").Range("D1").Offset(r + Start) = MM
  755.  Worksheets("Final Output").Range("E1").Offset(r + Start) = MS
  756.  Worksheets("Final Output").Range("F1").Offset(r + Start) = MMG
  757.  Worksheets("Final Output").Range("G1").Offset(r + Start) = Productgl
  758.    
  759.     r = r + 1
  760.      count = count - 1
  761.      End If
  762.      k = k + 1
  763.      Wend
  764.  
  765. Worksheets("Final GL").Select
  766.     Worksheets("Final GL").Range("J:J").Select
  767.     ActiveWorkbook.Worksheets("Final GL").Sort.SortFields.Clear
  768.     ActiveWorkbook.Worksheets("Final GL").Sort.SortFields.Add Key:=Range("J1"), _
  769.         SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
  770.     With ActiveWorkbook.Worksheets("Final GL").Sort
  771.         .SetRange Range("A2:J5775")
  772.         .Header = xlNo
  773.         .MatchCase = False
  774.         .Orientation = xlTopToBottom
  775.         .SortMethod = xlPinYin
  776.         .Apply
  777.     End With
  778.  
  779. With ThisWorkbook.Worksheets("Final GL")
  780. i = 500
  781. For j = 1 To i
  782.    X = 0
  783.    P = 15
  784.    ASIN2(j, X) = .Range("A1").Offset(j, X)
  785.    .Range("A1").Offset(j, P).Value = ASIN2(j, X)
  786.    Next j
  787. End With
  788.  
  789. count = SMoveV
  790. Start = Start + MarginV
  791.  
  792. r = 0
  793. k = 1
  794. While (count > 0)
  795. MASIN = Worksheets("Final GL").Range("P1").Offset(k)
  796. MVendor = Worksheets("Final GL").Range("B1").Offset(k)
  797. MTitle = Worksheets("Final GL").Range("C1").Offset(k)
  798. MM = Worksheets("Final GL").Range("D1").Offset(k)
  799. MS = Worksheets("Final GL").Range("E1").Offset(k)
  800. MMG = Worksheets("Final GL").Range("F1").Offset(k)
  801. Productgl = Worksheets("Final GL").Range("R1").Offset(k)
  802.  
  803. result = check(MASIN, Start)
  804. If result = 0 Then
  805.  
  806.  Worksheets("Final Output").Range("A1").Offset(Start + r) = MASIN
  807.  Worksheets("Final Output").Range("B1").Offset(Start + r) = MVendor
  808.  Worksheets("Final Output").Range("C1").Offset(Start + r) = MTitle
  809.  Worksheets("Final Output").Range("D1").Offset(Start + r) = MM
  810.  Worksheets("Final Output").Range("E1").Offset(Start + r) = MS
  811.  Worksheets("Final Output").Range("F1").Offset(Start + r) = MMG
  812.  Worksheets("Final Output").Range("g1").Offset(Start + r) = Productgl
  813.  
  814.      count = count - 1
  815.      r = r + 1
  816.      End If
  817.      k = k + 1
  818.      Wend
  819.  
  820. End Sub
  821.  
  822. Public Function check(CheckValue As Variant, NumberOfElement As Variant) As Integer
  823.  
  824. For Z = 1 To NumberOfElement
  825.  y = Worksheets("Final Output").Range("A1").Offset(Z)
  826.  If y = CheckValue Then
  827.  check = 1
  828.  Exit Function
  829.  Else: check = 0
  830.  End If
  831.  Next Z
  832. End Function
  833.  
  834.  
  835.  
  836.  
  837.  
  838. 'Worksheets("Final GL").Select
  839. '    Worksheets("Final GL").Range("J:J").Select
  840. '    ActiveWorkbook.Worksheets("Final GL").Sort.SortFields.Clear
  841. '    ActiveWorkbook.Worksheets("Final GL").Sort.SortFields.Add Key:=Range("J1"), _
  842. '        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
  843. '    With ActiveWorkbook.Worksheets("Final GL").Sort
  844. '        .SetRange Range("A2:J5775")
  845. '        .Header = xlNo
  846. '        .MatchCase = False
  847. '        .Orientation = xlTopToBottom
  848. '        .SortMethod = xlPinYin
  849. '        .Apply
  850. '    End With
  851. '
  852. 'With ThisWorkbook.Worksheets("Final GL")
  853. 'i = 500
  854. 'For j = 1 To i
  855. '   x = 0
  856. '   P = 15
  857. '   ASIN2(j, x) = .Range("A1").Offset(j, x)
  858. '   .Range("A1").Offset(j, P).Value = ASIN2(j, x)
  859. '   Next j
  860. 'End With
  861. '
  862. 'count1 = SMove
  863. 'SM = Mgn + Margin
  864. '
  865. 'k = 1
  866. 'While (count1 > 0)
  867. 'MASIN = Worksheets("Final GL").Range("P1").Offset(k)
  868. 'MVendor = Worksheets("Final GL").Range("B1").Offset(k)
  869. 'MTitle = Worksheets("Final GL").Range("C1").Offset(k)
  870. 'MM = Worksheets("Final GL").Range("D1").Offset(k)
  871. 'MS = Worksheets("Final GL").Range("E1").Offset(k)
  872. 'MMG = Worksheets("Final GL").Range("F1").Offset(k)
  873. '
  874. 'k = k + 1
  875. '
  876. 'result = check(MASIN, SM)
  877. 'If result = 0 Then
  878. '     r = (SMove + 1) - count1
  879. '     f = SM + r
  880. '
  881. ' Worksheets("Final Output").Range("A1").Offset(f) = MASIN
  882. ' Worksheets("Final Output").Range("B1").Offset(f) = MVendor
  883. ' Worksheets("Final Output").Range("C1").Offset(f) = MTitle
  884. ' Worksheets("Final Output").Range("D1").Offset(f) = MM
  885. ' Worksheets("Final Output").Range("E1").Offset(f) = MS
  886. ' Worksheets("Final Output").Range("F1").Offset(f) = MMG
  887. '
  888. '     count1 = count1 - 1
  889. '     End If
  890. '     Wend
  891. '
  892. '  StartLine
  893.  
  894. 'Dim baby As Variant
  895. '
  896. 'GL ("Baby")
  897. '
  898.  
  899.  
  900.  
  901. 'Public Function GL(GLValue As Variant)
  902. 'MsgBox "Hello"
  903. 'ThisWorkbook.Sheets("Final GL").Select
  904. '
  905. '    ThisWorkbook.Sheets("Final GL").Range("A2").Select
  906. '   ThisWorkbook.Sheets("Final GL").Range(Selection, Selection.End(xlDown)).Select
  907. ' ThisWorkbook.Sheets("Final GL").Range("A:J").Select
  908. '   Selection.ClearContents
  909. '
  910. '    ThisWorkbook.Sheets("Final Input").Select
  911. '
  912. '   ThisWorkbook.Sheets("Final Input").Rows("1:1").Select
  913. '   Selection.AutoFilter
  914. '   ThisWorkbook.Sheets("Final Input").Range("$A$1:$R$2088").AutoFilter Field:=12, Criteria1:= _
  915. '        GLValue
  916. '  ThisWorkbook.Sheets("Final Input").Columns("A:F").Select
  917. '    Selection.Copy
  918. '   ThisWorkbook.Sheets("Final GL").Select
  919. '   ThisWorkbook.Sheets("Final GL").Columns("A:A").Select
  920. '    ActiveSheet.Paste
  921. '
  922. '   Sheets("Final Input").Select
  923. '    Sheets("Final Input").Columns("I:K").Select
  924. '    Application.CutCopyMode = False
  925. '   Selection.Copy
  926. '    Sheets("Final GL").Select
  927. '   Sheets("Final GL").Columns("G:G").Select
  928. '    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  929. '        :=False, Transpose:=False
  930. '       Sheets("Final Input").Select
  931. '    Sheets("Final Input").Columns("N:N").Select
  932. '    Application.CutCopyMode = False
  933. '    Selection.Copy
  934. '    Sheets("Final GL").Select
  935. '   Sheets("Final GL").Columns("J:J").Select
  936. '    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  937. '        :=False, Transpose:=False
  938. '    Sheets("Final GL").Range("A1:J1").Select
  939. '    Application.CutCopyMode = False
  940. '    Selection.AutoFilter
  941. '    ActiveSheet.Range("$A$1:$J$2000").AutoFilter Field:=2, Criteria1:="=HING3", _
  942. '        Operator:=xlOr, Criteria2:="=HING3 20"
  943. '    Sheets("Final GL").Rows("10:10").Select
  944. '    Sheets("Final GL").Range(Selection, Selection.End(xlDown)).Select
  945. '   Selection.Delete Shift:=xlUp
  946. '    Selection.AutoFilter
  947. '    Sheets("Final GL").Range("A2").Select
  948. '   Sheets("Final GL").Columns("I:I").Select
  949. '    Selection.NumberFormat = "0.0%"
  950. '    Sheets("Final GL").Columns("J:J").Select
  951. '    Selection.NumberFormat = "0.0%"
  952. '
  953. ' End Function
  954.  
  955.  
  956. 'HPC = (GLHPC * ASIN) / 100
  957. 'Baby = (GLBaby * ASIN) / 100
  958. 'NArrivals = (HPC * 10) / 100
  959. '
  960. 'Order = (Baby * 30 / 100)
  961. 'CMT = (Baby * 20 / 100)
  962. 'PriceM = (Baby * 20) / 100
  963. 'Margin = (Baby * 10) / 100
  964. 'SMove = (Baby * 10) / 100
  965. '
  966. '' 'Sort the quantity column
  967. ' Worksheets("Final GL").Select
  968. '    Worksheets("Final GL").Range("G:G").Select
  969. '    ActiveWorkbook.Worksheets("Final GL").Sort.SortFields.Clear
  970. '    ActiveWorkbook.Worksheets("Final GL").Sort.SortFields.Add Key:=Range("G1"), _
  971. '        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
  972. '    With ActiveWorkbook.Worksheets("Final GL").Sort
  973. '        .SetRange Range("A2:j575")
  974. '        .Header = xlNo
  975. '        .MatchCase = False
  976. '        .Orientation = xlTopToBottom
  977. '        .SortMethod = xlPinYin
  978. '        .Apply
  979. '    End With
  980. '
  981. 'With ThisWorkbook.Worksheets("Final GL")
  982. '
  983. 'i = 500
  984. 'x = 0
  985. 'w = 11
  986. '
  987. 'Bstartrow = (HPC + 1) - NArrivals
  988. 'For j = 1 To i
  989. '     ASIN2(j, x) = .Range("A1").Offset(j, x)
  990. '     .Range("A1").Offset(j, w).Value = ASIN2(j, x)
  991. ' Next j
  992. ' End With
  993. '
  994. 'For c = 1 To Order
  995. 'd = 0
  996. 'Worksheets("Final Output").Range("A1").Offset(StartLine).Value = Worksheets("Final GL").Range("L1").Offset(c, d).Value
  997. 'Worksheets("Final Output").Range("A1").Offset(StartLine, 1).Value = Worksheets("Final GL").Range("B1").Offset(c, d).Value
  998. 'Worksheets("Final Output").Range("A1").Offset(StartLine, 2).Value = Worksheets("Final GL").Range("C1").Offset(c, d).Value
  999. 'Worksheets("Final Output").Range("A1").Offset(StartLine, 3).Value = Worksheets("Final GL").Range("D1").Offset(c, d).Value
  1000. 'Worksheets("Final Output").Range("A1").Offset(StartLine, 4).Value = Worksheets("Final GL").Range("E1").Offset(c, d).Value
  1001. 'Worksheets("Final Output").Range("A1").Offset(StartLine, 5).Value = Worksheets("Final GL").Range("F1").Offset(c, d).Value
  1002. 'Bstartrow = Bstartrow + 1
  1003. 'Next c
  1004. '
  1005. '
  1006. 'Worksheets("Final GL").Select
  1007. '    Worksheets("Final GL").Range("H:H").Select
  1008. '    ActiveWorkbook.Worksheets("Final GL").Sort.SortFields.Clear
  1009. '    ActiveWorkbook.Worksheets("Final GL").Sort.SortFields.Add Key:=Range("H1"), _
  1010. '        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
  1011. '    With ActiveWorkbook.Worksheets("Final GL").Sort
  1012. '        .SetRange Range("A2:J5775")
  1013. '        .Header = xlNo
  1014. '        .MatchCase = False
  1015. '        .Orientation = xlTopToBottom
  1016. '        .SortMethod = xlPinYin
  1017. '        .Apply
  1018. '    End With
  1019. '
  1020. 'With ThisWorkbook.Worksheets("Final GL")
  1021. 'i = 500
  1022. 'For j = 1 To i
  1023. '   x = 0
  1024. '   P = 12
  1025. '   ASIN2(j, x) = .Range("A1").Offset(j, x)
  1026. '   .Range("A1").Offset(j, P).Value = ASIN2(j, x)
  1027. '   Next j
  1028. 'End With
  1029. '
  1030. ''For c = 1 To Order
  1031. ''d = 0
  1032. ''Worksheets("Final Output").Range("A1").Offset(c, d).Value = Worksheets("Final GL").Range("L1").Offset(c, d).Value
  1033. ''Next c
  1034. '
  1035. 'count = CMT
  1036. ' k = 1
  1037. ' While (count > 0)
  1038. '   Z = Worksheets("Final GL").Range("M1").Offset(k)
  1039. '   Vendor = Worksheets("Final GL").Range("B1").Offset(k)
  1040. '   Title = Worksheets("Final GL").Range("C1").Offset(k)
  1041. '   M = Worksheets("Final GL").Range("D1").Offset(k)
  1042. '   S = Worksheets("Final GL").Range("E1").Offset(k)
  1043. '   MG = Worksheets("Final GL").Range("F1").Offset(k)
  1044. '
  1045. '      k = k + 1
  1046. '      CMT2 = Bstartrow - 1
  1047. '   result = check(Z, CMT2)
  1048. '  If result = 0 Then
  1049. '      j = (CMT + 1) - count
  1050. '      e = CMT2 + j
  1051. '      Worksheets("Final Output").Range("A1").Offset(e) = Z
  1052. '      Worksheets("Final Output").Range("B1").Offset(e) = Vendor
  1053. '      Worksheets("Final Output").Range("C1").Offset(e) = Title
  1054. '      Worksheets("Final Output").Range("D1").Offset(e) = M
  1055. '      Worksheets("Final Output").Range("E1").Offset(e) = S
  1056. '      Worksheets("Final Output").Range("F1").Offset(e) = MG
  1057. '
  1058. '      count = count - 1
  1059. 'End If
  1060. 'Wend
  1061. '
  1062. 'Worksheets("Final GL").Select
  1063. '    Worksheets("Final GL").Range("I:I").Select
  1064. '    ActiveWorkbook.Worksheets("Final GL").Sort.SortFields.Clear
  1065. '    ActiveWorkbook.Worksheets("Final GL").Sort.SortFields.Add Key:=Range("I1"), _
  1066. '        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
  1067. '    With ActiveWorkbook.Worksheets("Final GL").Sort
  1068. '        .SetRange Range("A2:J5775")
  1069. '        .Header = xlNo
  1070. '        .MatchCase = False
  1071. '        .Orientation = xlTopToBottom
  1072. '        .SortMethod = xlPinYin
  1073. '        .Apply
  1074. '    End With
  1075. '
  1076. 'With ThisWorkbook.Worksheets("Final GL")
  1077. 'i = 500
  1078. 'For j = 1 To i
  1079. '   x = 0
  1080. '   P = 13
  1081. '   ASIN2(j, x) = .Range("A1").Offset(j, x)
  1082. '   .Range("A1").Offset(j, P).Value = ASIN2(j, x)
  1083. '   Next j
  1084. 'End With
  1085. '
  1086. 'count1 = PriceM
  1087. 'SlowMoving = CMT2 + CMT
  1088. 'k = 1
  1089. 'While (count1 > 0)
  1090. 'PASIN = Worksheets("Final GL").Range("N1").Offset(k)
  1091. 'PVendor = Worksheets("Final GL").Range("B1").Offset(k)
  1092. 'PTitle = Worksheets("Final GL").Range("C1").Offset(k)
  1093. 'PM = Worksheets("Final GL").Range("D1").Offset(k)
  1094. 'PS = Worksheets("Final GL").Range("E1").Offset(k)
  1095. 'PMG = Worksheets("Final GL").Range("F1").Offset(k)
  1096. '
  1097. 'k = k + 1
  1098. 'result = check(PASIN, SlowMoving)
  1099. 'If result = 0 Then
  1100. '     r = (PriceM + 1) - count1
  1101. '     f = SlowMoving + r
  1102. ' Worksheets("Final Output").Range("A1").Offset(f) = PASIN
  1103. ' Worksheets("Final Output").Range("B1").Offset(f) = PVendor
  1104. '      Worksheets("Final Output").Range("C1").Offset(f) = PTitle
  1105. '      Worksheets("Final Output").Range("D1").Offset(f) = PM
  1106. '      Worksheets("Final Output").Range("E1").Offset(f) = PS
  1107. '      Worksheets("Final Output").Range("F1").Offset(f) = PMG
  1108. '
  1109. '     count1 = count1 - 1
  1110. '     End If
  1111. '     Wend
  1112. '
  1113. '
  1114. 'Worksheets("Final GL").Select
  1115. '    Worksheets("Final GL").Range("F:F").Select
  1116. '    ActiveWorkbook.Worksheets("Final GL").Sort.SortFields.Clear
  1117. '    ActiveWorkbook.Worksheets("Final GL").Sort.SortFields.Add Key:=Range("F1"), _
  1118. '        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
  1119. '    With ActiveWorkbook.Worksheets("Final GL").Sort
  1120. '        .SetRange Range("A2:J5775")
  1121. '        .Header = xlNo
  1122. '        .MatchCase = False
  1123. '        .Orientation = xlTopToBottom
  1124. '        .SortMethod = xlPinYin
  1125. '        .Apply
  1126. '    End With
  1127. '
  1128. 'With ThisWorkbook.Worksheets("Final GL")
  1129. 'i = 500
  1130. 'For j = 1 To i
  1131. '   x = 0
  1132. '   P = 14
  1133. '   ASIN2(j, x) = .Range("A1").Offset(j, x)
  1134. '   .Range("A1").Offset(j, P).Value = ASIN2(j, x)
  1135. '   Next j
  1136. 'End With
  1137. '
  1138. 'count1 = Margin
  1139. 'Mgn = SlowMoving + PriceM
  1140. 'k = 1
  1141. 'While (count1 > 0)
  1142. 'MASIN = Worksheets("Final GL").Range("O1").Offset(k)
  1143. 'MVendor = Worksheets("Final GL").Range("B1").Offset(k)
  1144. 'MTitle = Worksheets("Final GL").Range("C1").Offset(k)
  1145. 'MM = Worksheets("Final GL").Range("D1").Offset(k)
  1146. 'MS = Worksheets("Final GL").Range("E1").Offset(k)
  1147. 'MMG = Worksheets("Final GL").Range("F1").Offset(k)
  1148. '
  1149. 'k = k + 1
  1150. '
  1151. 'result = check(MASIN, Mgn)
  1152. 'If result = 0 Then
  1153. '     r = (Margin + 1) - count1
  1154. '     f = Mgn + r
  1155. '
  1156. ' Worksheets("Final Output").Range("A1").Offset(f) = MASIN
  1157. ' Worksheets("Final Output").Range("B1").Offset(f) = MVendor
  1158. ' Worksheets("Final Output").Range("C1").Offset(f) = MTitle
  1159. ' Worksheets("Final Output").Range("D1").Offset(f) = MM
  1160. ' Worksheets("Final Output").Range("E1").Offset(f) = MS
  1161. ' Worksheets("Final Output").Range("F1").Offset(f) = MMG
  1162. '
  1163. '     count1 = count1 - 1
  1164. '     End If
  1165. '     Wend
  1166. '
  1167. '
  1168. '
  1169. 'Worksheets("Final GL").Select
  1170. '    Worksheets("Final GL").Range("J:J").Select
  1171. '    ActiveWorkbook.Worksheets("Final GL").Sort.SortFields.Clear
  1172. '    ActiveWorkbook.Worksheets("Final GL").Sort.SortFields.Add Key:=Range("J1"), _
  1173. '        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
  1174. '    With ActiveWorkbook.Worksheets("Final GL").Sort
  1175. '        .SetRange Range("A2:J5775")
  1176. '        .Header = xlNo
  1177. '        .MatchCase = False
  1178. '        .Orientation = xlTopToBottom
  1179. '        .SortMethod = xlPinYin
  1180. '        .Apply
  1181. '    End With
  1182. '
  1183. 'With ThisWorkbook.Worksheets("Final GL")
  1184. 'i = 500
  1185. 'For j = 1 To i
  1186. '   x = 0
  1187. '   P = 15
  1188. '   ASIN2(j, x) = .Range("A1").Offset(j, x)
  1189. '   .Range("A1").Offset(j, P).Value = ASIN2(j, x)
  1190. '   Next j
  1191. 'End With
  1192. '
  1193. 'count1 = SMove
  1194. 'SM = Mgn + Margin
  1195. '
  1196. 'k = 1
  1197. 'While (count1 > 0)
  1198. 'MASIN = Worksheets("Final GL").Range("P1").Offset(k)
  1199. 'MVendor = Worksheets("Final GL").Range("B1").Offset(k)
  1200. 'MTitle = Worksheets("Final GL").Range("C1").Offset(k)
  1201. 'MM = Worksheets("Final GL").Range("D1").Offset(k)
  1202. 'MS = Worksheets("Final GL").Range("E1").Offset(k)
  1203. 'MMG = Worksheets("Final GL").Range("F1").Offset(k)
  1204. '
  1205. 'k = k + 1
  1206. '
  1207. 'result = check(MASIN, SM)
  1208. 'If result = 0 Then
  1209. '     r = (SMove + 1) - count1
  1210. '     f = SM + r
  1211. '
  1212. ' Worksheets("Final Output").Range("A1").Offset(f) = MASIN
  1213. ' Worksheets("Final Output").Range("B1").Offset(f) = MVendor
  1214. ' Worksheets("Final Output").Range("C1").Offset(f) = MTitle
  1215. ' Worksheets("Final Output").Range("D1").Offset(f) = MM
  1216. ' Worksheets("Final Output").Range("E1").Offset(f) = MS
  1217. ' Worksheets("Final Output").Range("F1").Offset(f) = MMG
  1218. '
  1219. '     count1 = count1 - 1
  1220. '     End If
  1221. '     Wend
  1222. '
  1223. 'End Function
  1224. '
  1225. 'Public Function check(CheckValue As Variant, NumberOfElement As Variant) As Integer
  1226. '
  1227. 'For Z = 1 To NumberOfElement
  1228. ' y = Worksheets("Final Output").Range("A1").Offset(Z)
  1229. ' If y = CheckValue Then
  1230. ' check = 1
  1231. ' Exit Function
  1232. ' Else: check = 0
  1233. ' End If
  1234. ' Next Z
  1235. 'End Function
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!