Всем привет. Есть большая таблица с товарами и их характеристиками, затем она выгружается на ресурс. Для облегчения работы себе в некоторых моментах я создал макрорекордером набор макросов и свел их к одному большому. Да он большой, но рабочий. Есть проблемы, которые (в этой жизни точно) я не смогу решить.
1. Изменяемый диапазон Действия многих макросов у меня привязаны к определенной величине количества строк таблицы ( Range("H5:H1100").Select), которые эпизодически меняются и приходится вносить множественные правки. Например часть кода некоего действия выглядит так: [vba]
Код
Selection.FormatConditions(1).StopIfTrue = False Range("H5:H1100").Select Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=И(F5:F5>0;G5:G5>0)" Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority With Selection.FormatConditions(1).Interior .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorAccent3 .TintAndShade = 0.599963377788629 End With
[/vba]
В первую ячейку я ввел функцию подсчета строк (http://prntscr.com/kfl4w3): [vba]
Код
Function LastRow(Cell) LastRow = Cells(Cells.Rows.Count, Cell.Column).End(xlUp).Row End Function
[/vba]
Вопрос: Как можно переменное значение H1100 из Range("H5:H1100").Select заменить на это функцию автоматического подсчета количества строк ? Первая величина H5 постоянная. Прошу дать решение понятное, что на что заменить и где что подставить (я не владею знаниями программирования) [b]
Всем привет. Есть большая таблица с товарами и их характеристиками, затем она выгружается на ресурс. Для облегчения работы себе в некоторых моментах я создал макрорекордером набор макросов и свел их к одному большому. Да он большой, но рабочий. Есть проблемы, которые (в этой жизни точно) я не смогу решить.
1. Изменяемый диапазон Действия многих макросов у меня привязаны к определенной величине количества строк таблицы ( Range("H5:H1100").Select), которые эпизодически меняются и приходится вносить множественные правки. Например часть кода некоего действия выглядит так: [vba]
Код
Selection.FormatConditions(1).StopIfTrue = False Range("H5:H1100").Select Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=И(F5:F5>0;G5:G5>0)" Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority With Selection.FormatConditions(1).Interior .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorAccent3 .TintAndShade = 0.599963377788629 End With
[/vba]
В первую ячейку я ввел функцию подсчета строк (http://prntscr.com/kfl4w3): [vba]
Код
Function LastRow(Cell) LastRow = Cells(Cells.Rows.Count, Cell.Column).End(xlUp).Row End Function
[/vba]
Вопрос: Как можно переменное значение H1100 из Range("H5:H1100").Select заменить на это функцию автоматического подсчета количества строк ? Первая величина H5 постоянная. Прошу дать решение понятное, что на что заменить и где что подставить (я не владею знаниями программирования) [b]Rama
[/vba] По остальному, в макросе много мусора после рекордера. И вопрос, зачем макросом перезаписывать одни и те же правила УФ, в чем производственный смысл?
Добрый день. Касаемо вопроса. так и пишите [vba]
Код
Range("H5:H" & LastRow)
[/vba] По остальному, в макросе много мусора после рекордера. И вопрос, зачем макросом перезаписывать одни и те же правила УФ, в чем производственный смысл?sboy
Яндекс: 410016850021169
Сообщение отредактировал sboy - Вторник, 07.08.2018, 14:04
Касаемо вопроса. так и пишите Range("H5:H" & LastRow)
Функцию LastRow разместил в модуле с макросом - ошибка. На листе таблицы то же ошибка, но уже в коде Если я меняю на Ваш вариант, то ошибка на первой замене http://prntscr.com/kft04f
По остальному, в макросе много мусора после рекордера. И вопрос, зачем макросом перезаписывать одни и те же правила УФ, в чем производственный смысл?
Есть шесть условий от значений в трех столбцах. Я ничего другого не придумал как по каждому столбцу прописал все шесть условий, всего 18 макрокодов ). Понятно что для знатаков это можно как то объединить, но не для меня.
Касаемо вопроса. так и пишите Range("H5:H" & LastRow)
Функцию LastRow разместил в модуле с макросом - ошибка. На листе таблицы то же ошибка, но уже в коде Если я меняю на Ваш вариант, то ошибка на первой замене http://prntscr.com/kft04f
По остальному, в макросе много мусора после рекордера. И вопрос, зачем макросом перезаписывать одни и те же правила УФ, в чем производственный смысл?
Есть шесть условий от значений в трех столбцах. Я ничего другого не придумал как по каждому столбцу прописал все шесть условий, всего 18 макрокодов ). Понятно что для знатаков это можно как то объединить, но не для меня.Rama
Mikael, не надо учить плохому не нужен тут select вместо [vba]
Код
Range("a5:a" & LastRow([a1])).Select Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=СЖПРОБЕЛЫ(A5)<>A5" Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority With Selection.FormatConditions(1).Interior .PatternColorIndex = xlAutomatic .Color = 255 .TintAndShade = 0 End With ' Первый столбик / Красное / Проверка УФ на дубликаты названий Selection.FormatConditions.AddUniqueValues Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority Selection.FormatConditions(1).DupeUnique = xlDuplicate 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
[/vba] пишем [vba]
Код
With Range("a5:a" & LastRow([a1])).FormatConditions With .Add(xlExpression, Formula1:="=СЖПРОБЕЛЫ(RC)<>RC") .SetFirstPriority With .Interior .PatternColorIndex = xlAutomatic .Color = 255 .TintAndShade = 0 End With End With ' Первый столбик / Красное / Проверка УФ на дубликаты названий With .AddUniqueValues .SetFirstPriority .DupeUnique = xlDuplicate With .Font .Color = -16383844 .TintAndShade = 0 End With With .Interior .PatternColorIndex = xlAutomatic .Color = 13551615 .TintAndShade = 0 End With .StopIfTrue = False End With End With
[/vba]
Вместо[vba]
Код
Range("H5:H1100").Select 'Columns("H:H").Select Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=И(F5:F5>0;G5:G5>0)" Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority With Selection.FormatConditions(1).Interior .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorAccent3 .TintAndShade = 0.599963377788629 End With ' Формула 3-2 / Зеленый / В корзину (в наличии) (цена ДА / остаток ДА / разрешить покупку НЕТ) Selection.FormatConditions(1).StopIfTrue = False Range("H5:H1100").Select 'Columns("H:H").Select Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=И(F5:F5>0;G5:G5>0;H5:H5>0)" Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority With Selection.FormatConditions(1).Interior .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorAccent3 .TintAndShade = 0.599963377788629 End With ' Формула 3-1 / Зеленый / Запросить цену (в наличии) цена НЕТ / остаток ДА / разрешить покупку ДА Selection.FormatConditions(1).StopIfTrue = False Range("H5:H1100").Select 'Columns("H:H").Select Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=И(F5:F5<=0;G5:G5>0)" Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority With Selection.FormatConditions(1).Interior .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorAccent3 .TintAndShade = 0.599963377788629 End With
' Формула 2-2 / Желтое / В корзину (под заказ) (цена ДА/ остаток НЕТ / разрешить покупку ДА) Selection.FormatConditions(1).StopIfTrue = False Range("H5:H1100").Select 'Columns("H:H").Select Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=И(F5:F5>=0;G5:G5<=0)" Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority With Selection.FormatConditions(1).Interior .PatternColorIndex = xlAutomatic .Color = 10079487 .TintAndShade = 0 .PatternTintAndShade = 0 End With ' Формула 2-1 / Желтое / Запросить цену (под заказ) (цена НЕТ / остаток НЕТ / разрешить покупку ДА) Selection.FormatConditions(1).StopIfTrue = False Range("H5:H1100").Select 'Columns("H:H").Select Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=И(F5:F5<=0;G5:G5<=0)" Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority With Selection.FormatConditions(1).Interior .PatternColorIndex = xlAutomatic .Color = 10079487 .TintAndShade = 0 .PatternTintAndShade = 0 End With
' Формула 1-2 / Красный цвет / Подписаться (цена ДА / остаток НЕТ / разрешить покупку НЕТ) Selection.FormatConditions(1).StopIfTrue = False Range("H5:H1100").Select 'Columns("H:H").Select Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=И(F5:F5>0;G5:G5<=0;H5:H5>0)" Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority With Selection.FormatConditions(1).Interior .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorAccent2 .TintAndShade = 0.599963377788629 End With ' Формула 1-1 / Красный цвет / Запросить цену (не доступно) (цена ДА / остаток НЕТ / разрешить покупку НЕТ) Selection.FormatConditions(1).StopIfTrue = False Range("H5:H1100").Select 'Columns("H:H").Select Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=И(F5:F5>0;G5:G5<=0;H5:H5>0)" Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority With Selection.FormatConditions(1).Interior .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorAccent2 .TintAndShade = 0.599963377788629 End With
[/vba] пишем [vba]
Код
With Range("h5:h" & LastRow([h1])).FormatConditions With .Add(xlExpression, Formula1:="=И(R[]C6>0;R[]C7>0)") .SetFirstPriority With .Interior .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorAccent3 .TintAndShade = 0.599963377788629 End With .StopIfTrue = False End With ' Формула 3-2 / Зеленый / В корзину (в наличии) (цена ДА / остаток ДА / разрешить покупку НЕТ) With .Add(xlExpression, Formula1:="=И(R[]C6>0;R[]C7>0;RC>0)") .SetFirstPriority With .Interior .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorAccent3 .TintAndShade = 0.599963377788629 End With .StopIfTrue = False End With ' Формула 3-1 / Зеленый / Запросить цену (в наличии) цена НЕТ / остаток ДА / разрешить покупку ДА With .Add(xlExpression, Formula1:="=И(R[]C6<=0;R[]C7>0)") .SetFirstPriority With .Interior .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorAccent3 .TintAndShade = 0.599963377788629 End With .StopIfTrue = False End With ' Формула 2-2 / Желтое / В корзину (под заказ) (цена ДА/ остаток НЕТ / разрешить покупку ДА) With .Add(xlExpression, Formula1:="=И(R[]C6>=0;R[]C7<=0)") .SetFirstPriority With .Interior .PatternColorIndex = xlAutomatic .Color = 10079487 .TintAndShade = 0 .PatternTintAndShade = 0 End With .StopIfTrue = False End With ' Формула 2-1 / Желтое / Запросить цену (под заказ) (цена НЕТ / остаток НЕТ / разрешить покупку ДА) With .Add(xlExpression, Formula1:="=И(R[]C6<=0;R[]C7<=0)") .SetFirstPriority With .Interior .PatternColorIndex = xlAutomatic .Color = 10079487 .TintAndShade = 0 .PatternTintAndShade = 0 End With .StopIfTrue = False End With ' Формула 1-2 / Красный цвет / Подписаться (цена ДА / остаток НЕТ / разрешить покупку НЕТ) With .Add(xlExpression, Formula1:="=И(R[]C6>0;R[]C7<=0;RC>0)") .SetFirstPriority With Selection.FormatConditions(1).Interior .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorAccent2 .TintAndShade = 0.599963377788629 End With .StopIfTrue = False End With ' Формула 1-1 / Красный цвет / Запросить цену (не доступно) (цена ДА / остаток НЕТ / разрешить покупку НЕТ) With .Add(xlExpression, Formula1:="=И(R[]C6>0;R[]C7<=0;RC>0)") .SetFirstPriority With .Interior .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorAccent2 .TintAndShade = 0.599963377788629 End With .StopIfTrue = False End With End With
[/vba] и т.д. по аналогии [p.s.]при таком подходе необходимо использовать R1C1 адресацию в формулах
Mikael, не надо учить плохому не нужен тут select вместо [vba]
Код
Range("a5:a" & LastRow([a1])).Select Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=СЖПРОБЕЛЫ(A5)<>A5" Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority With Selection.FormatConditions(1).Interior .PatternColorIndex = xlAutomatic .Color = 255 .TintAndShade = 0 End With ' Первый столбик / Красное / Проверка УФ на дубликаты названий Selection.FormatConditions.AddUniqueValues Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority Selection.FormatConditions(1).DupeUnique = xlDuplicate 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
[/vba] пишем [vba]
Код
With Range("a5:a" & LastRow([a1])).FormatConditions With .Add(xlExpression, Formula1:="=СЖПРОБЕЛЫ(RC)<>RC") .SetFirstPriority With .Interior .PatternColorIndex = xlAutomatic .Color = 255 .TintAndShade = 0 End With End With ' Первый столбик / Красное / Проверка УФ на дубликаты названий With .AddUniqueValues .SetFirstPriority .DupeUnique = xlDuplicate With .Font .Color = -16383844 .TintAndShade = 0 End With With .Interior .PatternColorIndex = xlAutomatic .Color = 13551615 .TintAndShade = 0 End With .StopIfTrue = False End With End With
[/vba]
Вместо[vba]
Код
Range("H5:H1100").Select 'Columns("H:H").Select Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=И(F5:F5>0;G5:G5>0)" Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority With Selection.FormatConditions(1).Interior .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorAccent3 .TintAndShade = 0.599963377788629 End With ' Формула 3-2 / Зеленый / В корзину (в наличии) (цена ДА / остаток ДА / разрешить покупку НЕТ) Selection.FormatConditions(1).StopIfTrue = False Range("H5:H1100").Select 'Columns("H:H").Select Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=И(F5:F5>0;G5:G5>0;H5:H5>0)" Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority With Selection.FormatConditions(1).Interior .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorAccent3 .TintAndShade = 0.599963377788629 End With ' Формула 3-1 / Зеленый / Запросить цену (в наличии) цена НЕТ / остаток ДА / разрешить покупку ДА Selection.FormatConditions(1).StopIfTrue = False Range("H5:H1100").Select 'Columns("H:H").Select Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=И(F5:F5<=0;G5:G5>0)" Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority With Selection.FormatConditions(1).Interior .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorAccent3 .TintAndShade = 0.599963377788629 End With
' Формула 2-2 / Желтое / В корзину (под заказ) (цена ДА/ остаток НЕТ / разрешить покупку ДА) Selection.FormatConditions(1).StopIfTrue = False Range("H5:H1100").Select 'Columns("H:H").Select Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=И(F5:F5>=0;G5:G5<=0)" Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority With Selection.FormatConditions(1).Interior .PatternColorIndex = xlAutomatic .Color = 10079487 .TintAndShade = 0 .PatternTintAndShade = 0 End With ' Формула 2-1 / Желтое / Запросить цену (под заказ) (цена НЕТ / остаток НЕТ / разрешить покупку ДА) Selection.FormatConditions(1).StopIfTrue = False Range("H5:H1100").Select 'Columns("H:H").Select Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=И(F5:F5<=0;G5:G5<=0)" Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority With Selection.FormatConditions(1).Interior .PatternColorIndex = xlAutomatic .Color = 10079487 .TintAndShade = 0 .PatternTintAndShade = 0 End With
' Формула 1-2 / Красный цвет / Подписаться (цена ДА / остаток НЕТ / разрешить покупку НЕТ) Selection.FormatConditions(1).StopIfTrue = False Range("H5:H1100").Select 'Columns("H:H").Select Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=И(F5:F5>0;G5:G5<=0;H5:H5>0)" Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority With Selection.FormatConditions(1).Interior .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorAccent2 .TintAndShade = 0.599963377788629 End With ' Формула 1-1 / Красный цвет / Запросить цену (не доступно) (цена ДА / остаток НЕТ / разрешить покупку НЕТ) Selection.FormatConditions(1).StopIfTrue = False Range("H5:H1100").Select 'Columns("H:H").Select Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=И(F5:F5>0;G5:G5<=0;H5:H5>0)" Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority With Selection.FormatConditions(1).Interior .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorAccent2 .TintAndShade = 0.599963377788629 End With
[/vba] пишем [vba]
Код
With Range("h5:h" & LastRow([h1])).FormatConditions With .Add(xlExpression, Formula1:="=И(R[]C6>0;R[]C7>0)") .SetFirstPriority With .Interior .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorAccent3 .TintAndShade = 0.599963377788629 End With .StopIfTrue = False End With ' Формула 3-2 / Зеленый / В корзину (в наличии) (цена ДА / остаток ДА / разрешить покупку НЕТ) With .Add(xlExpression, Formula1:="=И(R[]C6>0;R[]C7>0;RC>0)") .SetFirstPriority With .Interior .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorAccent3 .TintAndShade = 0.599963377788629 End With .StopIfTrue = False End With ' Формула 3-1 / Зеленый / Запросить цену (в наличии) цена НЕТ / остаток ДА / разрешить покупку ДА With .Add(xlExpression, Formula1:="=И(R[]C6<=0;R[]C7>0)") .SetFirstPriority With .Interior .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorAccent3 .TintAndShade = 0.599963377788629 End With .StopIfTrue = False End With ' Формула 2-2 / Желтое / В корзину (под заказ) (цена ДА/ остаток НЕТ / разрешить покупку ДА) With .Add(xlExpression, Formula1:="=И(R[]C6>=0;R[]C7<=0)") .SetFirstPriority With .Interior .PatternColorIndex = xlAutomatic .Color = 10079487 .TintAndShade = 0 .PatternTintAndShade = 0 End With .StopIfTrue = False End With ' Формула 2-1 / Желтое / Запросить цену (под заказ) (цена НЕТ / остаток НЕТ / разрешить покупку ДА) With .Add(xlExpression, Formula1:="=И(R[]C6<=0;R[]C7<=0)") .SetFirstPriority With .Interior .PatternColorIndex = xlAutomatic .Color = 10079487 .TintAndShade = 0 .PatternTintAndShade = 0 End With .StopIfTrue = False End With ' Формула 1-2 / Красный цвет / Подписаться (цена ДА / остаток НЕТ / разрешить покупку НЕТ) With .Add(xlExpression, Formula1:="=И(R[]C6>0;R[]C7<=0;RC>0)") .SetFirstPriority With Selection.FormatConditions(1).Interior .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorAccent2 .TintAndShade = 0.599963377788629 End With .StopIfTrue = False End With ' Формула 1-1 / Красный цвет / Запросить цену (не доступно) (цена ДА / остаток НЕТ / разрешить покупку НЕТ) With .Add(xlExpression, Formula1:="=И(R[]C6>0;R[]C7<=0;RC>0)") .SetFirstPriority With .Interior .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorAccent2 .TintAndShade = 0.599963377788629 End With .StopIfTrue = False End With End With
[/vba] и т.д. по аналогии [p.s.]при таком подходе необходимо использовать R1C1 адресацию в формулахkrosav4ig