Страницы: 1
RSS
Как установить макросом проверку данных на вводимое число относительно имени месяца, VBA Validation.Add Method (Excel)
 
Здравствуйте, уважаемы жители планеты Excel.
Прошу Вашего совета, т.к. устал биться головой об стену.
В прилагаемом файле есть горизонтальный диапазон ячеек (B1:M1) с именами месяцев.
Пытаюсь посредством макроса создать проверку вводимых данных, допустим в диапазоне B3:M3, по следующему условию:
целое число, между, минимум: 1, максимум: день конца соответствующего месяца.
Вручную сделал запросто. (см. диапазон B2:M2)
Но макросом, как ни бьюсь, ничего не получается - ругается на второе условие формулой.
Макрорекордер записал формулы кириллицей, но при воспроизведении сего шедевра сбой на строке с добавлением условий проверки.
Скрытый текст
Отдельно записанная формула работает без проблем.
=ДЕНЬ(КОНМЕСЯЦА(ДАТАЗНАЧ("1-"&ЛЕВБ(B$1;3));0))

Перевёл формулу на аглицкий - результат тот-же - сбой.
Скрытый текст
Эта же формула на аглицком записывается макросом в ячейку без проблем.
Код
ActiveCell.FormulaR1C1 = "=DAY(EOMONTH(DATEVALUE(""1-""&LEFTB(R1C,3)),0))"
Помогите, пожалуйста, решить возникшее затруднение.
Спасибо всем зашедшим, прошедшим и ответившим. :)
Изменено: JayBhagavan - 26.03.2016 16:54:54 (очепятка...)

Формула массива (ФМ) вводится Ctrl+Shift+Enter
Memento mori
 
Я так понял, что проверка данных в данном случае не воспринимает русский текст и нужно указывать месяц на английском языке.
Или на листе пишите месяца на английском, или в коде сделайте массив - в первом столбце месяца на русском, во втором - на английском и пусть макрос на основе этого массива переводит русские названия в английские.
Сейчас подумал, наверное, массив в данном случае не поможет, т.к. Ваша задача вставить в ячейку проверку данных, а не провести какие-то действия с помощью макроса. Значит пока остается один вариант - использовать на листе названия месяцев на английском. Или может быть во время работы макроса подставлять названия месяцев на английском, а затем снова возвращать русские названия.
Изменено: Karataev - 26.03.2016 19:54:47
 
Karataev, спасибо за Ваш ответ. Да, я могу подставить вместо рус. наименования месяца англ., но тогда формула не будет ссылаться на соответствующую ячейку и когда месяца в ячейках поменяются (допустим месяцев не 12, а четыре), то в такой проверке не будет смысла. Вы меня натолкнули на мысль обернуть формулу в ЕСЛИОШИБКА() (IFERROR), но это не помогло.
Пожалуйста, объясните, возможно ли вообще прописать заданное условие проверки макросом не меняя структуру данных (т.к. предполагается обработать кучу файлов с кучей однотипных листов, которые макросом менять не предполагается, а только прописать проверку на ввод числа, в зависимости от месяца в ячейке.
Спасибо.

Формула массива (ФМ) вводится Ctrl+Shift+Enter
Memento mori
 
А такой вариант не подходит (я его позже дописал)? Может быть во время работы макроса подставлять на лист названия месяцев на английском, а затем снова возвращать русские названия?
Изменено: Karataev - 26.03.2016 20:40:25
 
Доброе время суток
Иван, у меня сработал такой вариант с привлечением ВПР. Да и формула должна быть таки написана по английски. У меня разделитель списка запятая.
Код
        .Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, _
        Operator:=xlBetween, Formula1:="1", Formula2:= _
        "=DAY(EOMONTH(DATE(2016,VLOOKUP(LEFT(LOWER(B1),3),Подставить,2),1),0))"

Успехов
Изменено: Андрей VG - 26.03.2016 21:04:16 (Кракозяблы-с)
 
Или даже без ВПР
Код
=DAY(EOMONTH(DATE(2016,(SEARCH(LOWER(LEFT(B1,3)),"янвфевмарапрмайиюниюлавгсеноктноядек")-1)/3+1,1),0))
 
Цитата
Karataev написал: во время работы макроса подставлять на лист названия месяцев на английском, а затем снова возвращать русские названия
Просто заменил все месяца на англ. - та же ошибка. Спасибо за предложенный вариант.
Андрей VG, благодарствую за Ваше внимание. Вас понял, вариант хороший, но, если иного выхода без вмешательства в имеющуюся структуру книги не получится, тогда, с Вашего позволения, воспользуюсь этим вариантом. Спасибо.
Общение с вами натолкнуло меня на другую формулу:
=ЕСЛИОШИБКА(ДЕНЬ(КОНМЕСЯЦА(ДАТА(ГОД(СЕГОДНЯ());ИНДЕКС({1:2:3:4:5:6:7:8:9:10:11:12};ПОИСКПОЗ(B$1;{"январь":"февраль":"март":"апрель":"май":"июнь":"июль":"август":"сентябрь":"октябрь":"ноябрь":"декабрь"};0));1);0));31)

Её аналог для ВБА:
Код
"=IFERROR(DAY(EOMONTH(DATE(YEAR(TODAY()),INDEX({1;2;3;4;5;6;7;8;9;10;11;12},MATCH(R1C,{""январь"";""февраль"";""март"";""апрель"";""май"";""июнь"";""июль"";""август"";""сентябрь"";""октябрь"";""ноябрь"";""декабрь""},0)),1),0)),31)"
И опять 25. Пробовал точку с запятой менять на двоеточие и запятую - не помогло.
Подскажите, пожалуйста, что теперь не так? (ладно, в первом варианте ВБА не понимал рус. названия месяцев, но теперь то чего???)
Изменено: JayBhagavan - 26.03.2016 22:12:08 (очепятка...)

Формула массива (ФМ) вводится Ctrl+Shift+Enter
Memento mori
 
А если в проверку вставлять не формулу, прям значения ?
Код
Sub Test1()
    Dim M As Long
    On Error GoTo L1 ' на всякий случай '
    For M = 1 To 12
        With Cells(3, M + 1).Validation
            .Delete
            .Add xlValidateWholeNumber, , xlBetween, 1, Day(DateSerial(Year(Date), M + 1, 1) - 1)
        End With
    Next
    Exit Sub
L1:
    Err.Clear
End Sub
 
С.М., спасибо за Ваше внимание к данной теме. Этот вариант, к моему глубочайшему сожалению, не подойдёт, простите, потому, что тогда проверка не будет зависеть от месяца в ячейке и когда месяца в ячейках поменяются (допустим месяцев не 12, а четыре), то в такой проверке не будет смысла. То есть задача, один раз обработать энцать файлов с энцатью листами единоразово (а может и повторная обработка понадобится не раз) макросом, чтобы проверка осуществлялась штатным средством и максимальное число зависело от месяца в ячейке. Спасибо.
ЗЫ У меня сильное недопонимание, почему то, что вручную можно сделать, у чего есть алгоритм для автоматизации, а автоматизировать не получается.

Формула массива (ФМ) вводится Ctrl+Shift+Enter
Memento mori
 
А в первой строке писать не текст, а даты, но отображать форматом месяц, допустимо?
 
JayBhagavan, во вложенном файле с английскими месяцами работает, а с русскими нет.
А что другую формулу нельзя придумать? Нужно заменить функцию "DATEVALUE" чем-нибудь другим. Сначала придумать без проверки данных, а затем адаптировать к проверке данных.
 
Юрий М, спасибо за Ваше внимание. Я запомню Ваше предложение, но пока будем считать, что нельзя заменять месяца текстом на даты с форматом. (файлы не мои с меня только макрос для добавления проверки вводимых данных в фиксированный диапазон ячеек на основании имени месяца в ячейках выше и на, казалось бы такой простой вещи, такая засада...)
Karataev, спасибо за Ваш вариант - попробую его развить. Другую формулу я выложил ранее.
Изменено: JayBhagavan - 26.03.2016 23:05:11 (очепятки...)

Формула массива (ФМ) вводится Ctrl+Shift+Enter
Memento mori
 
JayBhagavan, по поводу новой формулы. Я так понимаю, что ошибка из-за использования фигурных скобок.
Я упросил Вашу формулу и если использовать фигурные скобки, то ошибки:
"=DATE(YEAR(TODAY()),INDEX({1},1,1),1)"
Изменено: Karataev - 26.03.2016 23:24:01
 
Хорошо. Уважаемый Karataev и уважаемые форумчане, подскажите, пожалуйста, как для решения данного вопроса обойти ошибку из-за фигурных скобок, т.е. чем их можно заменить? Спасибо.

Формула массива (ФМ) вводится Ctrl+Shift+Enter
Memento mori
 
JayBhagavan, кстати фигурные скобки нельзя вставить в проверку данных и в самом Excel'е.
 
Karataev, спасибо. Понял. Буду искать решение данной задачи дальше.

Формула массива (ФМ) вводится Ctrl+Shift+Enter
Memento mori
 
Ну ещё длинный вариант с Select Case ((
 
Юрий М, спасибо за предложенный вариант. Только я не понимаю как это сделать, чтобы формулы были в проверке. Простите, что отнимаю Ваше время. В общем есть над чем думать и варианты для экспериментов ещё есть, но "утро вечера мудренее".
Всем хорошо отдохнуть и спасибо за отзывчивость.

Формула массива (ФМ) вводится Ctrl+Shift+Enter
Memento mori
 
Только с високосным годом нужно будет проверку ещё делать:
Код
Sub qqq()
Dim i As Long, LastDay
    For i = 2 To 4
        Select Case Cells(1, i)
            Case Is = "Январь": LastDay = 31
            Case Is = "Февраль": LastDay = 29
            Case Is = "Март": LastDay = 31
            '...
        End Select
        With Cells(4, i).Validation
            .Delete
            .Add xlValidateWholeNumber, , xlBetween, 1, LastDay
        End With
    Next
End Sub
 
Цитата
JayBhagavan написал #9:
С.М., спасибо за Ваше внимание к данной теме. Этот вариант, к моему глубочайшему сожалению, не подойдёт, простите, потому, что тогда проверка не будет зависеть от месяца в ячейке и когда месяца в ячейках поменяются (допустим месяцев не 12, а четыре), то в такой проверке не будет смысла.
Война - фигня, главное - манёвры (с)  :) . Если макросом вставить формулы трудно, а "вручную сделать запросто" [#1] :
Код
Rem Выделяем диапазон и запускаем:
Sub Test2()
    Dim Ms(), SelRng As Range, Column As Range
    Set SelRng = ActiveWindow.RangeSelection
    Ms = Array("январь", "февраль", "март", "апрель", "май", "июнь", "июль", "август", "сентябрь", "октябрь", "ноябрь", "декабрь")
    Worksheets("Лист1").Cells(2, 2).Copy '<--- ячейка с Validation-формулами, вставленными руками --- '
    For Each Column In SelRng.Columns
        If Not IsError(Application.Match(Cells(1, Column.Column), Ms, 0)) Then
        Rem если ячейка первой строки листа содержит название месяца, копируем условия проверки:
            Column.Validation.Delete
            Column.PasteSpecial xlPasteValidation
        End If
    Next
    Application.CutCopyMode = 0
    SelRng.Select
End Sub
 
Иван, а вариант из #6 без ВПР по решению Казанского тоже не работает? У меня с ним всё хорошо.
 
Прошу меня простить. Вынужден приостановить разбирательства с данным вопросом. Позже к нему обязательно вернусь. Всем спасибо и будьте здоровы.

Формула массива (ФМ) вводится Ctrl+Shift+Enter
Memento mori
 
Цитата
JayBhagavan написал:
ИНДЕКС({1:2:3:4:5:6:7:8:9:10:11:12};ПОИСКПОЗ(B$1;{"январь":"февраль":"март":"апрель":"май":"июнь":"июль":"август":"сентябрь":"октябрь":"ноябрь":"декабрь"};0))
Можно и так заменить этот кусок (одной функцией) -
ПРОСМОТР(C1;{"август";"апрель";"декабрь";"июль";"июнь";"май";"март";"ноябрь";"октябрь";"сентябрь";"февраль";"январь"};{8;4;12;7;6;5;3;11;10;9;2;1})
только на англ LOOKUP:
LOOKUP(C1,{""август"",""апрель"",""декабрь"",""июль"",""июнь"",""май"",""март"",""ноябрь"",""октябрь"",""сентябрь"",""февраль"",""январь""},{8,4,12,7,6,5,3,11,10,9,2,1})

но, к сожалению, тоже не работает...
- я думаю, всё-таки дело в этих рус.яз названиях месяцев...
потому что, если вы сделаете, например,  строку с нумерацией месяцев ячеек над ней (например, первую) - и ссылаться будете на эту ячейку с Номером месяца, а не выковыривать № с помощью одной из указанных формул - то, формула из #7 работает!..
Код
Sub WORKING()
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, _
        Operator:=xlBetween, Formula1:="1", Formula2:= _
        "=IFERROR(DAY(EOMONTH(DATE(YEAR(TODAY()),C1,1),0)),31)"
                        
        .IgnoreBlank = True
        .InCellDropdown = True
        .ShowInput = True
        .ShowError = True
    End With
End Sub
Изменено: JeyCi - 28.03.2016 10:57:50
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
Цитата
JeyCi написала: Можно и так заменить этот кусок (одной функцией) -
=ПОИСК(ЛЕВБ(B$1;3);"]]янвфевмарапрмайиюниюлавгсеноктноядек")/3
 
Предположение:
Цитата
JayBhagavan написал:
Перевёл формулу на аглицкий - результат тот-же - сбой.
Цитата
JayBhagavan написал:
"=DAY(EOMONTH(DATEVALUE(""1-""&LEFTB(R1C,3)),0))"
тут проблема еще в чем: у Вас же стиль ссылок-то - А1? А в формулу пытаетесь передать стиль R1C1. Может отсюда и ошибка? Что если попробовать так:
"=DAY(EOMONTH(DATEVALUE(""1-""&LEFTB(C1,3)),0))"
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
да, vikttur, такая проходит!
"=IFERROR(DAY(EOMONTH(DATE(YEAR(TODAY()),SEARCH(LEFTB(B$1,3),""]]янвфевмарапрмайиюниюлавгсеноктноядек"")/3,1),0)),31)"
Изменено: JeyCi - 28.03.2016 12:11:12
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
Страницы: 1
Наверх