Добрый день.
Друзья, совсем не силен в написании кода.
Делаю проверку большого объема данных простым форматированием.
Помогите немного оптимизировать код.
Сделано через одно место, по этому и прошу помощи.
Не ругайте, помогите кто чем может))
Друзья, совсем не силен в написании кода.
Делаю проверку большого объема данных простым форматированием.
Помогите немного оптимизировать код.
Сделано через одно место, по этому и прошу помощи.
Не ругайте, помогите кто чем может))
Код |
---|
Sub ПроверкаНаПробелы() ' 'УДАЛЕНИЕ 'УДАЛЕНИЕ 'УДАЛЕНИЕ 'Удаление удаленных позиций Dim ra As Range, delra As Range, ТекстДляПоиска As String Application.ScreenUpdating = False ' отключаем обновление экрана ТекстДляПоиска = "Помечен" ' удаляем строки с таким текстом ' перебираем все строки в используемом диапазоне листа For Each ra In ActiveSheet.UsedRange.Rows ' если в строке найден искомый текст If Not ra.Find(ТекстДляПоиска, , xlValues, xlPart) Is Nothing Then ' добавляем строку в диапазон для удаления If delra Is Nothing Then Set delra = ra Else Set delra = Union(delra, ra) End If Next ' если подходящие строки найдены - удаляем их If Not delra Is Nothing Then delra.EntireRow.Delete 'ПРОВЕРКИ 'ПРОВЕРКИ 'ПРОВЕРКИ 'ПРОВЕРКА НА ПУСТОТУ (пункт три правил) 'Первый уровень Columns("A:A").FormatConditions.Add Type:=xlExpression, Formula1:="=$A1=0" Columns("A:A").FormatConditions(Columns("A:A").FormatConditions.Count).SetFirstPriority With Columns("A:A").FormatConditions(1).Interior .PatternColorIndex = xlAutomatic .Color = 13421823 .TintAndShade = 0 End With Columns("A:A").FormatConditions(1).StopIfTrue = False 'Второй уровень Columns("B:B").FormatConditions.Add Type:=xlExpression, Formula1:="=$B1=0" Columns("B:B").FormatConditions(Columns("B:B").FormatConditions.Count).SetFirstPriority With Columns("B:B").FormatConditions(1).Interior .PatternColorIndex = xlAutomatic .Color = 13421823 .TintAndShade = 0 End With Columns("B:B").FormatConditions(1).StopIfTrue = False 'Наименование для печати Columns("K:K").FormatConditions.Add Type:=xlExpression, Formula1:="=$K1=0" Columns("K:K").FormatConditions(Columns("K:K").FormatConditions.Count).SetFirstPriority With Columns("K:K").FormatConditions(1).Interior .PatternColorIndex = xlAutomatic .Color = 13421823 .TintAndShade = 0 End With Columns("K:K").FormatConditions(1).StopIfTrue = False 'Коссифоикатор аналогов Columns("L:L").FormatConditions.Add Type:=xlExpression, Formula1:="=$L1=0" Columns("L:L").FormatConditions(Columns("L:L").FormatConditions.Count).SetFirstPriority With Columns("L:L").FormatConditions(1).Interior .PatternColorIndex = xlAutomatic .Color = 13421823 .TintAndShade = 0 End With Columns("L:L").FormatConditions(1).StopIfTrue = False 'Страна происхождения Columns("AD:AD").FormatConditions.Add Type:=xlExpression, Formula1:="=$AD1=0" Columns("AD:AD").FormatConditions(Columns("AD:AD").FormatConditions.Count).SetFirstPriority With Columns("AD:AD").FormatConditions(1).Interior .PatternColorIndex = xlAutomatic .Color = 13421823 .TintAndShade = 0 End With Columns("AD:AD").FormatConditions(1).StopIfTrue = False 'ВТМ Columns("AE:AE").FormatConditions.Add Type:=xlExpression, Formula1:="=$AE1=0" Columns("AE:AE").FormatConditions(Columns("AE:AE").FormatConditions.Count).SetFirstPriority With Columns("AE:AE").FormatConditions(1).Interior .PatternColorIndex = xlAutomatic .Color = 13421823 .TintAndShade = 0 End With Columns("AE:AE").FormatConditions(1).StopIfTrue = False 'Производитель Columns("AG:AG").FormatConditions.Add Type:=xlExpression, Formula1:="=$AG1=0" Columns("AG:AG").FormatConditions(Columns("AG:AG").FormatConditions.Count).SetFirstPriority With Columns("AG:AG").FormatConditions(1).Interior .PatternColorIndex = xlAutomatic .Color = 13421823 .TintAndShade = 0 End With Columns("AG:AG").FormatConditions(1).StopIfTrue = False 'Марка Columns("AH:AH").FormatConditions.Add Type:=xlExpression, Formula1:="=$AH1=0" Columns("AH:AH").FormatConditions(Columns("AH:AH").FormatConditions.Count).SetFirstPriority With Columns("AH:AH").FormatConditions(1).Interior .PatternColorIndex = xlAutomatic .Color = 13421823 .TintAndShade = 0 End With Columns("AH:AH").FormatConditions(1).StopIfTrue = False 'Вид упаковки Columns("U:U").FormatConditions.Add Type:=xlExpression, Formula1:="=$U1=0" Columns("U:U").FormatConditions(Columns("U:U").FormatConditions.Count).SetFirstPriority With Columns("U:U").FormatConditions(1).Interior .PatternColorIndex = xlAutomatic .Color = 13421823 .TintAndShade = 0 End With Columns("U:U").FormatConditions(1).StopIfTrue = False 'Темпиратура мин Columns("V:V").FormatConditions.Add Type:=xlExpression, Formula1:="=$V1=0" Columns("V:V").FormatConditions(Columns("V:V").FormatConditions.Count).SetFirstPriority With Columns("V:V").FormatConditions(1).Interior .PatternColorIndex = xlAutomatic .Color = 13421823 .TintAndShade = 0 End With Columns("V:V").FormatConditions(1).StopIfTrue = False 'Темпиратура макс Columns("W:W").FormatConditions.Add Type:=xlExpression, Formula1:="=$W1=0" Columns("W:W").FormatConditions(Columns("W:W").FormatConditions.Count).SetFirstPriority With Columns("W:W").FormatConditions(1).Interior .PatternColorIndex = xlAutomatic .Color = 13421823 .TintAndShade = 0 End With Columns("W:W").FormatConditions(1).StopIfTrue = False 'Срок годности Columns("W:W").FormatConditions.Add Type:=xlExpression, Formula1:="=$W1=0" Columns("W:W").FormatConditions(Columns("W:W").FormatConditions.Count).SetFirstPriority With Columns("W:W").FormatConditions(1).Interior .PatternColorIndex = xlAutomatic .Color = 13421823 .TintAndShade = 0 End With Columns("W:W").FormatConditions(1).StopIfTrue = False 'Срок годности процент остатка Columns("Z:Z").FormatConditions.Add Type:=xlExpression, Formula1:="=$Z1=0" Columns("Z:Z").FormatConditions(Columns("Z:Z").FormatConditions.Count).SetFirstPriority With Columns("Z:Z").FormatConditions(1).Interior .PatternColorIndex = xlAutomatic .Color = 13421823 .TintAndShade = 0 End With Columns("Z:Z").FormatConditions(1).StopIfTrue = False 'ПроверкаНаПробелы Макрос 'Двойной пробел 'Рабочее наимнование, наименование для печати, Классификатор аналогов Columns("J:L").Select Selection.FormatConditions.Add Type:=xlTextString, String:=" ", _ TextOperator:=xlContains Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority With Selection.FormatConditions(1).Font .Color = -16383844 .TintAndShade = 0 End With With Selection.FormatConditions(1).Interior .PatternColorIndex = xlAutomatic .Color = 13551615 .TintAndShade = 0 End With Selection.FormatConditions(1).StopIfTrue = False 'Двойной пробел 'Страна происхождения, ВТМ, Производитель Columns("AD:AJ").Select Selection.FormatConditions.Add Type:=xlTextString, String:=" ", _ TextOperator:=xlContains Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority With Selection.FormatConditions(1).Font .Color = -16383844 .TintAndShade = 0 End With With Selection.FormatConditions(1).Interior .PatternColorIndex = xlAutomatic .Color = 13551615 .TintAndShade = 0 End With Selection.FormatConditions(1).StopIfTrue = False 'Один пробел 'Артикул Columns("I:I").Select Selection.FormatConditions.Add Type:=xlTextString, String:=" ", _ TextOperator:=xlContains Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority With Selection.FormatConditions(1).Font .Color = -16383844 .TintAndShade = 0 End With With Selection.FormatConditions(1).Interior .PatternColorIndex = xlAutomatic .Color = 13551615 .TintAndShade = 0 End With Selection.FormatConditions(1).StopIfTrue = False 'Один пробел 'Код Columns("G:G").Select Selection.FormatConditions.Add Type:=xlTextString, String:=" ", _ TextOperator:=xlContains Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority With Selection.FormatConditions(1).Font .Color = -16383844 .TintAndShade = 0 End With With Selection.FormatConditions(1).Interior .PatternColorIndex = xlAutomatic .Color = 13551615 .TintAndShade = 0 End With Selection.FormatConditions(1).StopIfTrue = False 'Один пробел 'Код БРЛ Columns("M:M").Select Selection.FormatConditions.Add Type:=xlTextString, String:=" ", _ TextOperator:=xlContains Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority With Selection.FormatConditions(1).Font .Color = -16383844 .TintAndShade = 0 End With With Selection.FormatConditions(1).Interior .PatternColorIndex = xlAutomatic .Color = 13551615 .TintAndShade = 0 End With Selection.FormatConditions(1).StopIfTrue = False 'АЛКОГОЛЬ 'АЛКОГОЛЬ 'АЛКОГОЛЬ Dim i As Integer Dim x As String Dim x2 As String Dim x3 As String Dim x4 As String Dim x42 As String Dim x5 As String For i = 2 To Cells(1, 1).End(xlDown).Row 'проверка со второй строки по последнюю в файле x = "A" & i x2 = "K" & i x4 = """АЛКОГОЛЬ""" x42 = """ПИВО""" x5 = """*%*""" x3 = "=И(ИЛИ(" & x & "=" & x4 & ";" & x & "=" & x42 & ");СЧЁТЕСЛИ(" & x2 & ";" & x5 & "))" Application.StatusBar = "Выполнено:" & x3: DoEvents Range(x2).FormatConditions.Add Type:=xlExpression, Formula1:=x3 Range(x2).FormatConditions(Range(x2).FormatConditions.Count).SetFirstPriority With Range(x2).FormatConditions(1).Interior .PatternColorIndex = xlAutomatic .Color = 13421823 .TintAndShade = 0 End With Range(x2).FormatConditions(1).StopIfTrue = False 'проверка на пустоту у пива и алкоголя (артикул) Dim y1 As String Dim y2 As String y2 = "I" & i y1 = "=И(" & x & "=" & x4 & ";ЕПУСТО(" & y2 & "))" Application.StatusBar = "Выполнено:" & x3: DoEvents Range(y2).FormatConditions.Add Type:=xlExpression, Formula1:=y1 Range(y2).FormatConditions(Range(y2).FormatConditions.Count).SetFirstPriority With Range(y2).FormatConditions(1).Interior .PatternColorIndex = xlAutomatic .Color = 13421823 .TintAndShade = 0 End With Range(y2).FormatConditions(1).StopIfTrue = False 'проверка на пустоту у пива и алкоголя (крепость) Dim y11 As String Dim y21 As String y21 = "AR" & i y11 = "=И(ИЛИ(" & x & "=" & x4 & ";" & x & "=" & x42 & ");ЕПУСТО(" & y21 & "))" Application.StatusBar = "Выполнено:" & y3: DoEvents Range(y21).FormatConditions.Add Type:=xlExpression, Formula1:=y11 Range(y21).FormatConditions(Range(y21).FormatConditions.Count).SetFirstPriority With Range(y21).FormatConditions(1).Interior .PatternColorIndex = xlAutomatic .Color = 13421823 .TintAndShade = 0 End With Range(y21).FormatConditions(1).StopIfTrue = False 'проверка на пустоту у пива и алкоголя (вид продукции) Dim y12 As String Dim y22 As String y22 = "AQ" & i y12 = "=И(ИЛИ(" & x & "=" & x4 & ";" & x & "=" & x42 & ");ЕПУСТО(" & y22 & "))" Application.StatusBar = "Выполнено:" & y3: DoEvents Range(y22).FormatConditions.Add Type:=xlExpression, Formula1:=y12 Range(y22).FormatConditions(Range(y22).FormatConditions.Count).SetFirstPriority With Range(y22).FormatConditions(1).Interior .PatternColorIndex = xlAutomatic .Color = 13421823 .TintAndShade = 0 End With Range(y22).FormatConditions(1).StopIfTrue = False 'проверка на пустоту у пива и алкоголя (происхождение) Dim y13 As String Dim y23 As String y23 = "AS" & i y13 = "=И(ИЛИ(" & x & "=" & x4 & ";" & x & "=" & x42 & ");ЕПУСТО(" & y23 & "))" Application.StatusBar = "Выполнено:" & y3: DoEvents Range(y23).FormatConditions.Add Type:=xlExpression, Formula1:=y13 Range(y23).FormatConditions(Range(y23).FormatConditions.Count).SetFirstPriority With Range(y23).FormatConditions(1).Interior .PatternColorIndex = xlAutomatic .Color = 13421823 .TintAndShade = 0 End With Range(y23).FormatConditions(1).StopIfTrue = False 'проверка на пустоту у пива и алкоголя (производитель импортер) Dim y14 As String Dim y24 As String y24 = "AU" & i y14 = "=И(ИЛИ(" & x & "=" & x4 & ";" & x & "=" & x42 & ");ЕПУСТО(" & y24 & "))" Application.StatusBar = "Выполнено:" & y3: DoEvents Range(y24).FormatConditions.Add Type:=xlExpression, Formula1:=y14 Range(y24).FormatConditions(Range(y24).FormatConditions.Count).SetFirstPriority With Range(y24).FormatConditions(1).Interior .PatternColorIndex = xlAutomatic .Color = 13421823 .TintAndShade = 0 End With Range(y24).FormatConditions(1).StopIfTrue = False 'проверка на пустоту у пива и алкоголя (группа финансового учета) Dim y15 As String Dim y25 As String y25 = "AZ" & i y15 = "=И(ИЛИ(" & x & "=" & x4 & ";" & x & "=" & x42 & ");ЕПУСТО(" & y25 & "))" Application.StatusBar = "Выполнено:" & y3: DoEvents Range(y25).FormatConditions.Add Type:=xlExpression, Formula1:=y15 Range(y25).FormatConditions(Range(y25).FormatConditions.Count).SetFirstPriority With Range(y25).FormatConditions(1).Interior .PatternColorIndex = xlAutomatic .Color = 13421823 .TintAndShade = 0 End With Range(y25).FormatConditions(1).StopIfTrue = False 'проверка на пустоту у пива и алкоголя (объем) Dim y16 As String Dim y26 As String y26 = "O" & i y16 = "=И(ИЛИ(" & x & "=" & x4 & ";" & x & "=" & x42 & ");ЕПУСТО(" & y26 & "))" Application.StatusBar = "Выполнено:" & y3: DoEvents Range(y26).FormatConditions.Add Type:=xlExpression, Formula1:=y16 Range(y26).FormatConditions(Range(y26).FormatConditions.Count).SetFirstPriority With Range(y26).FormatConditions(1).Interior .PatternColorIndex = xlAutomatic .Color = 13421823 .TintAndShade = 0 End With Range(y26).FormatConditions(1).StopIfTrue = False 'проверка на пустоту у без алкогольных (объем) Dim yx16 As String Dim yx26 As String yx26 = "O" & i x444 = """НАПИТКИ БЕЗАЛКОГОЛЬНЫЕ""" x4442 = """БЕЗАЛКОГОЛЬНОЕ ПИВО""" yx16 = "=И(ИЛИ(" & x & "=" & x444 & ";" & x & "=" & x4442 & ");ЕПУСТО(" & yx26 & "))" Application.StatusBar = "Выполнено:" & y3: DoEvents Range(yx26).FormatConditions.Add Type:=xlExpression, Formula1:=yx16 Range(yx26).FormatConditions(Range(yx26).FormatConditions.Count).SetFirstPriority With Range(yx26).FormatConditions(1).Interior .PatternColorIndex = xlAutomatic .Color = 13421823 .TintAndShade = 0 End With Range(yx26).FormatConditions(1).StopIfTrue = False 'проверка на весс нетто заполнен у алкоголя Dim y17 As String Dim y27 As String y27 = "N" & i y17 = "=И(ИЛИ(" & x & "=" & x4 & ";" & x & "=" & x42 & ");(" & y27 & ">0))" Application.StatusBar = "Выполнено:" & y3: DoEvents Range(y27).FormatConditions.Add Type:=xlExpression, Formula1:=y17 Range(y27).FormatConditions(Range(y27).FormatConditions.Count).SetFirstPriority With Range(y27).FormatConditions(1).Interior .PatternColorIndex = xlAutomatic .Color = 13421823 .TintAndShade = 0 End With Range(y27).FormatConditions(1).StopIfTrue = False 'проверка на весс нетто заполнен у не алкоголя Dim y317 As String Dim y327 As String Dim y328(1 To 4) As String y328(1) = """СИГАРЕТЫ""" y328(2) = """НОН-ФУД""" y328(3) = """ПРОДУКТЫ""" y328(4) = """ТМЦ""" y327 = "N" & i y317 = "=И(ИЛИ(" & x & "=" & y328(1) & ";" & x & "=" & y328(2) & ";" & x & "=" & y328(3) & ";" & x & "=" & y328(4) & ");ЕПУСТО(" & y327 & "))" Application.StatusBar = "Выполнено:" & y3: DoEvents Range(y327).FormatConditions.Add Type:=xlExpression, Formula1:=y317 Range(y327).FormatConditions(Range(y327).FormatConditions.Count).SetFirstPriority With Range(y327).FormatConditions(1).Interior .PatternColorIndex = xlAutomatic .Color = 13421823 .TintAndShade = 0 End With Range(y327).FormatConditions(1).StopIfTrue = False 'проверка рабочего наименования и наименования для печати Dim y2317 As String Dim y2327 As String Dim y2328(1 To 4) As String y2328(1) = """СИГАРЕТЫ""" y2328(2) = """НОН-ФУД""" y2328(3) = """ПРОДУКТЫ""" y2328(4) = """ТМЦ""" y2327 = "J" & i y23271 = "K" & i y2317 = "=И(ИЛИ(" & x & "=" & y2328(2) & ";" & x & "=" & y2328(3) & ";" & x & "=" & y2328(4) & ");НЕ(СОВПАД(" & y2327 & ";" & y23271 & ")))" Application.StatusBar = "Выполнено:" & y3: DoEvents Range(y2327).FormatConditions.Add Type:=xlExpression, Formula1:=y2317 Range(y2327).FormatConditions(Range(y2327).FormatConditions.Count).SetFirstPriority With Range(y2327).FormatConditions(1).Interior .PatternColorIndex = xlAutomatic .Color = 13421823 .TintAndShade = 0 End With Range(y2327).FormatConditions(1).StopIfTrue = False 'проверка на импорт Dim y18 As String Dim y28 As String Dim y281 As String Dim y282 As String y28 = "AD" & i y282 = "AS" & i y18 = "=И(ИЛИ(" & x & "=""ПИВО"";" & x & "=""АЛКОГОЛЬ"");" & y28 & "=""РОССИЯ"";" & y282 & "=""ИМПОРТ"")" Application.StatusBar = "Выполнено:" & y3: DoEvents Range(y282).FormatConditions.Add Type:=xlExpression, Formula1:=y18 Range(y282).FormatConditions(Range(y282).FormatConditions.Count).SetFirstPriority With Range(y282).FormatConditions(1).Interior .PatternColorIndex = xlAutomatic .Color = 13421823 .TintAndShade = 0 End With Range(y282).FormatConditions(1).StopIfTrue = False 'проверка на импорт Dim y19 As String Dim y29 As String Dim y291 As String Dim y292 As String y29 = "AD" & i y292 = "AS" & i y19 = "=И(ИЛИ(" & x & "=""ПИВО"";" & x & "=""АЛКОГОЛЬ"");" & y29 & "<>""РОССИЯ"";" & y292 & "=""РОССИЯ"")" Application.StatusBar = "Выполнено:" & y3: DoEvents Range(y29).FormatConditions.Add Type:=xlExpression, Formula1:=y19 Range(y29).FormatConditions(Range(y29).FormatConditions.Count).SetFirstPriority With Range(y29).FormatConditions(1).Interior .PatternColorIndex = xlAutomatic .Color = 13421823 .TintAndShade = 0 End With Range(y29).FormatConditions(1).StopIfTrue = False 'проверка на вид упаковки Dim y30 As String Dim y31 As String y31 = "U" & i y30 = "=ЕПУСТО(" & y31 & ")" Application.StatusBar = "Выполнено:" & y3: DoEvents Range(y31).FormatConditions.Add Type:=xlExpression, Formula1:=y30 Range(y31).FormatConditions(Range(y31).FormatConditions.Count).SetFirstPriority With Range(y31).FormatConditions(1).Interior .PatternColorIndex = xlAutomatic .Color = 13421823 .TintAndShade = 0 End With Range(y31).FormatConditions(1).StopIfTrue = False 'СИГАРЕТЫ 'СИГАРЕТЫ 'СИГАРЕТЫ 'проверка на весс нетто заполнен у не алкоголя Dim y417 As String Dim y427 As String Dim y428(1 To 4) As String y428(1) = """СИГАРЕТЫ""" y427 = "Q" & i y417 = "=И(" & x & "=(" & y428(1) & ");ЕПУСТО(" & y427 & "))" Application.StatusBar = "Выполнено:" & y3: DoEvents Range(y427).FormatConditions.Add Type:=xlExpression, Formula1:=y417 Range(y427).FormatConditions(Range(y427).FormatConditions.Count).SetFirstPriority With Range(y427).FormatConditions(1).Interior .PatternColorIndex = xlAutomatic .Color = 13421823 .TintAndShade = 0 End With Range(y427).FormatConditions(1).StopIfTrue = False 'проверка на строчные буквы в сигаретах Dim y517 As String Dim y527 As String Dim y528(1 To 4) As String y528(1) = """СИГАРЕТЫ""" y527 = "K" & i y517 = "=И(" & x & "=(" & y528(1) & ");НЕ(СОВПАД(" & y527 & ";ПРОПИСН(" & y527 & "))))" Application.StatusBar = "Выполнено:" & y3: DoEvents Range(y527).FormatConditions.Add Type:=xlExpression, Formula1:=y517 Range(y527).FormatConditions(Range(y527).FormatConditions.Count).SetFirstPriority With Range(y527).FormatConditions(1).Interior .PatternColorIndex = xlAutomatic .Color = 13421823 .TintAndShade = 0 End With Range(y527).FormatConditions(1).StopIfTrue = False 'проверка на строчные буквы в сигаретах Dim y5172 As String Dim y5272 As String Dim y5282(1 To 4) As String y5282(1) = """СИГАРЕТЫ""" y5272 = "U" & i y5172 = "=И(" & x & "=(" & y5282(1) & ");НЕ(СОВПАД(" & y5272 & ";(""ПАЧКА""))))" Application.StatusBar = "Выполнено:" & y3: DoEvents Range(y5272).FormatConditions.Add Type:=xlExpression, Formula1:=y5172 Range(y5272).FormatConditions(Range(y5272).FormatConditions.Count).SetFirstPriority With Range(y5272).FormatConditions(1).Interior .PatternColorIndex = xlAutomatic .Color = 13421823 .TintAndShade = 0 End With Range(y5272).FormatConditions(1).StopIfTrue = False 'Э-ЖИДКОСТИ 'Э-ЖИДКОСТИ 'Э-ЖИДКОСТИ 'проверка на ОКПД в ТМЦ Dim yy As String Dim y717 As String Dim y727 As String Dim y728(1 To 4) As String yy = "C" & i y728(1) = """Э-ЖИДКОСТИ""" y727 = "Q" & i y717 = "=И(" & yy & "=(" & y728(1) & ");ЕПУСТО(" & y727 & "))" Application.StatusBar = "Выполнено:" & y3: DoEvents Range(y727).FormatConditions.Add Type:=xlExpression, Formula1:=y717 Range(y727).FormatConditions(Range(y727).FormatConditions.Count).SetFirstPriority With Range(y727).FormatConditions(1).Interior .PatternColorIndex = xlAutomatic .Color = 13421823 .TintAndShade = 0 End With Range(y727).FormatConditions(1).StopIfTrue = False 'РАЗЛИВНОЕ ПИВО 'РАЗЛИВНОЕ ПИВО 'РАЗЛИВНОЕ ПИВО 'проверка на ОКПД в ТМЦ Dim yyy As String Dim y817 As String Dim y827 As String Dim y828(1 To 4) As String yyy = "B" & i y828(1) = """РАЗЛИВНОЕ ПИВО""" y828(2) = """ЯЙЦА""" y827 = "BF" & i y817 = "=И(ИЛИ(" & yyy & "=(" & y828(2) & ");" & yyy & "=(" & y828(1) & "));ЕПУСТО(" & y827 & "))" Application.StatusBar = "Выполнено:" & y3: DoEvents Range(y827).FormatConditions.Add Type:=xlExpression, Formula1:=y817 Range(y827).FormatConditions(Range(y827).FormatConditions.Count).SetFirstPriority With Range(y827).FormatConditions(1).Interior .PatternColorIndex = xlAutomatic .Color = 13421823 .TintAndShade = 0 End With Range(y827).FormatConditions(1).StopIfTrue = False 'НЕ ТМЦ 'НЕ ТМЦ 'НЕ ТМЦ 'проверка на нецелочисленные продажи (розница) Dim y617 As String Dim y627 As String Dim y6271 As String Dim y628(1 To 4) As String y628(1) = """ТМЦ""" y627 = "AX" & i y6271 = "AY" & i y617 = "=И(" & x & "<>(" & y628(1) & ");ИЛИ(ЕПУСТО(" & y627 & ");ЕПУСТО(" & y6271 & ")))" Application.StatusBar = "Выполнено:" & y3: DoEvents Range(y627).FormatConditions.Add Type:=xlExpression, Formula1:=y617 Range(y627).FormatConditions(Range(y627).FormatConditions.Count).SetFirstPriority With Range(y627).FormatConditions(1).Interior .PatternColorIndex = xlAutomatic .Color = 13421823 .TintAndShade = 0 End With Range(y627).FormatConditions(1).StopIfTrue = False 'проверка на нецелочисленные продажи (опт) Dim y2617 As String Dim y2627 As String Dim y26271 As String Dim y2628(1 To 4) As String y2628(1) = """ТМЦ""" y2627 = "AX" & i y26271 = "AY" & i y2617 = "=И(" & x & "<>(" & y2628(1) & ");ИЛИ(ЕПУСТО(" & y26271 & ");ЕПУСТО(" & y2627 & ")))" Application.StatusBar = "Выполнено:" & y3: DoEvents Range(y26271).FormatConditions.Add Type:=xlExpression, Formula1:=y2617 Range(y26271).FormatConditions(Range(y26271).FormatConditions.Count).SetFirstPriority With Range(y26271).FormatConditions(1).Interior .PatternColorIndex = xlAutomatic .Color = 13421823 .TintAndShade = 0 End With Range(y26271).FormatConditions(1).StopIfTrue = False 'САХАР 'САХАР 'САХАР 'проверка вин Dim yyy2 As String Dim y2817 As String Dim y2827 As String Dim y2828(1 To 4) As String yyy2 = "B" & i y2828(1) = """ВИНА, ВИННЫЕ НАПИТКИ""" y2828(2) = """ИГРИСТЫЕ ВИНА, НАПИТКИ И ШАМПАНСКОЕ""" y2827 = "AB" & i y2817 = "=И(ИЛИ(" & yyy2 & "=(" & y2828(2) & ");" & yyy2 & "=(" & y2828(1) & "));ЕПУСТО(" & y2827 & "))" Application.StatusBar = "Выполнено:" & y3: DoEvents Range(y2827).FormatConditions.Add Type:=xlExpression, Formula1:=y2817 Range(y2827).FormatConditions(Range(y2827).FormatConditions.Count).SetFirstPriority With Range(y2827).FormatConditions(1).Interior .PatternColorIndex = xlAutomatic .Color = 13421823 .TintAndShade = 0 End With Range(y2827).FormatConditions(1).StopIfTrue = False 'проверка у сидора Dim yy3 As String Dim y22717 As String Dim y22727 As String Dim y22728(1 To 4) As String yy3 = "J" & i y22728(1) = """Сидор""" y22727 = "AB" & i y22717 = "=ЕСЛИОШИБКА(ПОИСК(" & y22728(1) & ";" & yy3 & ");0)>0" Application.StatusBar = "Выполнено:" & y3: DoEvents Range(y22727).FormatConditions.Add Type:=xlExpression, Formula1:=y22717 Range(y22727).FormatConditions(Range(y22727).FormatConditions.Count).SetFirstPriority With Range(y22727).FormatConditions(1).Interior .PatternColorIndex = xlAutomatic .Color = 13421823 .TintAndShade = 0 End With Range(y22727).FormatConditions(1).StopIfTrue = False 'проверка у вермуты Dim yy4 As String Dim y322717 As String Dim y322727 As String Dim y322728(1 To 4) As String yy4 = "C" & i y322728(1) = """ВЕРМУТЫ""" y322727 = "AB" & i y322717 = "=И(" & yy4 & "=(" & y322728(1) & ");ЕПУСТО(" & y322727 & "))" Application.StatusBar = "Выполнено:" & y3: DoEvents Range(y322727).FormatConditions.Add Type:=xlExpression, Formula1:=y322717 Range(y322727).FormatConditions(Range(y322727).FormatConditions.Count).SetFirstPriority With Range(y322727).FormatConditions(1).Interior .PatternColorIndex = xlAutomatic .Color = 13421823 .TintAndShade = 0 End With Range(y322727).FormatConditions(1).StopIfTrue = False 'ЦВЕТ 'ЦВЕТ 'ЦВЕТ 'проверка у сидора Dim yyx3 As String Dim yx22717 As String Dim yx22727 As String Dim yx22728(1 To 4) As String yy3 = "J" & i yx22728(1) = """Сидор""" yx22727 = "AC" & i yx22717 = "=ЕСЛИОШИБКА(ПОИСК(" & yx22728(1) & ";" & yyx3 & ");0)>0" Application.StatusBar = "Выполнено:" & y3: DoEvents Range(yx22727).FormatConditions.Add Type:=xlExpression, Formula1:=yx22717 Range(yx22727).FormatConditions(Range(yx22727).FormatConditions.Count).SetFirstPriority With Range(yx22727).FormatConditions(1).Interior .PatternColorIndex = xlAutomatic .Color = 13421823 .TintAndShade = 0 End With Range(yx22727).FormatConditions(1).StopIfTrue = False 'проверка у вермуты Dim yyx4 As String Dim yx322717 As String Dim yx322727 As String Dim yx322728(1 To 4) As String yyx4 = "C" & i yx322728(1) = """ВЕРМУТЫ""" yx322727 = "AC" & i yx322717 = "=И(" & yyx4 & "=(" & yx322728(1) & ");ЕПУСТО(" & yx322727 & "))" Application.StatusBar = "Выполнено:" & y3: DoEvents Range(yx322727).FormatConditions.Add Type:=xlExpression, Formula1:=yx322717 Range(yx322727).FormatConditions(Range(yx322727).FormatConditions.Count).SetFirstPriority With Range(yx322727).FormatConditions(1).Interior .PatternColorIndex = xlAutomatic .Color = 13421823 .TintAndShade = 0 End With Range(yx322727).FormatConditions(1).StopIfTrue = False 'проверка ВЕРмутов и рома Dim yyw4 As String Dim yw322717 As String Dim yw322727 As String Dim yw322728(1 To 4) As String yyw4 = "C" & i yw322728(1) = """ВЕРМУТЫ""" yw322728(2) = """РОМ, РОМОВЫЕ НАПИТКИ""" yw322727 = "AC" & i yw322717 = "=И(ИЛИ(" & yyw4 & "=(" & yw322728(1) & ");" & yyw4 & "=(" & yw322728(2) & "));ЕПУСТО(" & yw322727 & "))" Application.StatusBar = "Выполнено:" & y3: DoEvents Range(yw322727).FormatConditions.Add Type:=xlExpression, Formula1:=yw322717 Range(yw322727).FormatConditions(Range(yw322727).FormatConditions.Count).SetFirstPriority With Range(yw322727).FormatConditions(1).Interior .PatternColorIndex = xlAutomatic .Color = 13421823 .TintAndShade = 0 End With Range(yw322727).FormatConditions(1).StopIfTrue = False 'проверка ПИВО Dim yyxw4 As String Dim yxw317 As String Dim yxw327 As String Dim yxw328(1 To 4) As String yyxw4 = "A" & i yxw328(1) = """ПИВО""" yxw327 = "AC" & i yxw317 = "=И(" & yyxw4 & "=(" & yxw328(1) & ");ЕПУСТО(" & yxw327 & "))" Application.StatusBar = "Выполнено:" & y3: DoEvents Range(yxw327).FormatConditions.Add Type:=xlExpression, Formula1:=yxw317 Range(yxw327).FormatConditions(Range(yxw327).FormatConditions.Count).SetFirstPriority With Range(yxw327).FormatConditions(1).Interior .PatternColorIndex = xlAutomatic .Color = 13421823 .TintAndShade = 0 End With Range(yxw327).FormatConditions(1).StopIfTrue = False 'убери руки ирод! Next i Application.StatusBar = False End Sub |