Advertisement
bl00dt3ars

fill_art_vol

Aug 7th, 2022
648
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. Sub fill_art_vol()
  2.  
  3. Application.ScreenUpdating = False
  4. On Error Resume Next
  5. Dim t As Single
  6. t = Timer
  7. Dim answer As VbMsgBoxResult
  8.     answer = MsgBox("Do you want to fill article volumes?", vbYesNo, "Run Macro")
  9.     If answer = vbYes Then
  10.  
  11.         Dim WS_Count As Integer
  12.         Dim i As Integer
  13.         Dim x As Integer
  14.         Dim art As Long
  15.         Dim rows As Integer
  16.         Dim current_workbook As Workbook
  17.         Dim art_workbook As Workbook
  18.         Dim find_col As Integer
  19.         Dim find_art As Integer
  20.         Set current_workbook = ActiveWorkbook
  21.         Set art_workbook = Workbooks("Ñïðàâêà Öåíòðàëíè ðàçïðåäåëåíèÿ.xlsx")
  22.  
  23.         If art_workbook.ActiveSheet.FilterMode = True Then
  24.             art_workbook.ActiveSheet.ShowAllData
  25.             End If
  26.         WS_Count = ActiveWorkbook.Worksheets.Count
  27.        
  28.         For i = 1 To WS_Count
  29.             ActiveWorkbook.Worksheets(i).Select
  30.             If ActiveSheet.FilterMode = True Then
  31.                 ActiveSheet.ShowAllData
  32.                 End If
  33.             If Range("A2") = "" Then
  34.                 Range("B2").Select
  35.                 rows = Range(Selection, Selection.End(xlDown)).Count + 1
  36.                
  37.                 For x = 1 To rows
  38.                     If Range("F" & x) = "TOTAL" Then
  39.                         art = Range("B" & x).Value
  40.                         If Not art_workbook.ActiveSheet.Range("E:E").Find(What:=art, LookIn:=xlValues) Is Nothing Then
  41.                             found_art = art_workbook.ActiveSheet.Range("E:E").Find(What:=art, LookIn:=xlValues).Row
  42.                             art_workbook.ActiveSheet.Range("R" & found_art).Value = Cells(x, "J").Value
  43.                             art_workbook.ActiveSheet.Range("S" & found_art).Value = Cells(x, "K").Value
  44.                             art_workbook.ActiveSheet.Range("T" & found_art).Value = Cells(x, "M").Value
  45.                             art_workbook.ActiveSheet.Range("U" & found_art).Value = Cells(x, "O").Value
  46.                             art_workbook.ActiveSheet.Range("V" & found_art).Value = Cells(x, "P").Value
  47.                             art_workbook.ActiveSheet.Range("A" & found_art, "V" & found_art).Interior.Color = RGB(146, 208, 80)
  48.                         Else
  49.                             MsgBox ("Article " & art & " not found")
  50.                             End If
  51.                         End If
  52.                 Next x
  53.             Else
  54.                 art = ActiveSheet.Name
  55.                 find_col = Range("5:5").Find(What:="Íàëè÷íîñò âúâ ôèëèàëà", LookIn:=xlValues).Column
  56.                 If Not art_workbook.ActiveSheet.Range("E:E").Find(What:=art, LookIn:=xlValues) Is Nothing Then
  57.                     found_art = art_workbook.ActiveSheet.Range("E:E").Find(What:=art, LookIn:=xlValues).Row
  58.                     art_workbook.ActiveSheet.Range("R" & found_art, "V" & found_art).Value = Range(Cells(7, find_col), Cells(7, find_col + 4)).Value
  59.                     art_workbook.ActiveSheet.Range("A" & found_art, "V" & found_art).Interior.Color = RGB(146, 208, 80)
  60.                 Else
  61.                     MsgBox ("Article " & art & " not found")
  62.                     End If
  63.                 End If
  64.         Next i
  65.         End If
  66.  
  67. Application.CutCopyMode = False
  68. MsgBox "The task is completed in " & Round(Timer - t, 0) & "s"
  69.  
  70. End Sub
  71.  
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement