Здравствуйте, уважаемы жители планеты Excel. Прошу Вашего совета, т.к. устал биться головой об стену. В прилагаемом файле есть горизонтальный диапазон ячеек (B1:M1) с именами месяцев. Пытаюсь посредством макроса создать проверку вводимых данных, допустим в диапазоне B3:M3, по следующему условию: целое число, между, минимум: 1, максимум: день конца соответствующего месяца. Вручную сделал запросто. (см. диапазон B2:M2) Но макросом, как ни бьюсь, ничего не получается - ругается на второе условие формулой. Макрорекордер записал формулы кириллицей, но при воспроизведении сего шедевра сбой на строке с добавлением условий проверки.
Скрытый текст
Код
Sub Макрос1()
'
' Макрос1 Макрос
'
'
With Selection.Validation
.Delete
.Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="1", Formula2:= _
"=ДЕНЬ(КОНМЕСЯЦА(ДАТАЗНАЧ(""1-""&ЛЕВБ(B$1;3));0))"
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = True
.ShowError = True
End With
End Sub
Отдельно записанная формула работает без проблем. =ДЕНЬ(КОНМЕСЯЦА(ДАТАЗНАЧ("1-"&ЛЕВБ(B$1;3));0)) Перевёл формулу на аглицкий - результат тот-же - сбой.
Скрытый текст
Код
Sub jjj()
With Range("B3:M3").Validation
.Delete
.Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:=1, Formula2:= _
"=DAY(EOMONTH(DATEVALUE(""1-""&LEFTB(R1C,3)),0))" _
'"=ДЕНЬ(КОНМЕСЯЦА(ДАТАЗНАЧ(""1-""&ЛЕВБ(B$1;3));0))" _
'
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub
Эта же формула на аглицком записывается макросом в ячейку без проблем.
Я так понял, что проверка данных в данном случае не воспринимает русский текст и нужно указывать месяц на английском языке. Или на листе пишите месяца на английском, или в коде сделайте массив - в первом столбце месяца на русском, во втором - на английском и пусть макрос на основе этого массива переводит русские названия в английские. Сейчас подумал, наверное, массив в данном случае не поможет, т.к. Ваша задача вставить в ячейку проверку данных, а не провести какие-то действия с помощью макроса. Значит пока остается один вариант - использовать на листе названия месяцев на английском. Или может быть во время работы макроса подставлять названия месяцев на английском, а затем снова возвращать русские названия.
Karataev, спасибо за Ваш ответ. Да, я могу подставить вместо рус. наименования месяца англ., но тогда формула не будет ссылаться на соответствующую ячейку и когда месяца в ячейках поменяются (допустим месяцев не 12, а четыре), то в такой проверке не будет смысла. Вы меня натолкнули на мысль обернуть формулу в ЕСЛИОШИБКА() (IFERROR), но это не помогло. Пожалуйста, объясните, возможно ли вообще прописать заданное условие проверки макросом не меняя структуру данных (т.к. предполагается обработать кучу файлов с кучей однотипных листов, которые макросом менять не предполагается, а только прописать проверку на ввод числа, в зависимости от месяца в ячейке. Спасибо.
Формула массива (ФМ) вводится Ctrl+Shift+Enter Memento mori
А такой вариант не подходит (я его позже дописал)? Может быть во время работы макроса подставлять на лист названия месяцев на английском, а затем снова возвращать русские названия?
Доброе время суток Иван, у меня сработал такой вариант с привлечением ВПР. Да и формула должна быть таки написана по английски. У меня разделитель списка запятая.
Karataev написал: во время работы макроса подставлять на лист названия месяцев на английском, а затем снова возвращать русские названия
Просто заменил все месяца на англ. - та же ошибка. Спасибо за предложенный вариант. Андрей VG, благодарствую за Ваше внимание. Вас понял, вариант хороший, но, если иного выхода без вмешательства в имеющуюся структуру книги не получится, тогда, с Вашего позволения, воспользуюсь этим вариантом. Спасибо. Общение с вами натолкнуло меня на другую формулу: =ЕСЛИОШИБКА(ДЕНЬ(КОНМЕСЯЦА(ДАТА(ГОД(СЕГОДНЯ());ИНДЕКС({1:2:3:4:5:6:7:8:9:10:11:12};ПОИСКПОЗ(B$1;{"январь":"февраль":"март":"апрель":"май":"июнь":"июль":"август":"сентябрь":"октябрь":"ноябрь":"декабрь"};0));1);0));31) Её аналог для ВБА:
И опять 25. Пробовал точку с запятой менять на двоеточие и запятую - не помогло. Подскажите, пожалуйста, что теперь не так? (ладно, в первом варианте ВБА не понимал рус. названия месяцев, но теперь то чего???)
А если в проверку вставлять не формулу, прям значения ?
Код
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, по поводу новой формулы. Я так понимаю, что ошибка из-за использования фигурных скобок. Я упросил Вашу формулу и если использовать фигурные скобки, то ошибки: "=DATE(YEAR(TODAY()),INDEX({1},1,1),1)"
Хорошо. Уважаемый Karataev и уважаемые форумчане, подскажите, пожалуйста, как для решения данного вопроса обойти ошибку из-за фигурных скобок, т.е. чем их можно заменить? Спасибо.
Формула массива (ФМ) вводится Ctrl+Shift+Enter Memento mori
Юрий М, спасибо за предложенный вариант. Только я не понимаю как это сделать, чтобы формулы были в проверке. Простите, что отнимаю Ваше время. В общем есть над чем думать и варианты для экспериментов ещё есть, но "утро вечера мудренее". Всем хорошо отдохнуть и спасибо за отзывчивость.
Формула массива (ФМ) вводится 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
Можно и так заменить этот кусок (одной функцией) - ПРОСМОТР(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
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
тут проблема еще в чем: у Вас же стиль ссылок-то - А1? А в формулу пытаетесь передать стиль R1C1. Может отсюда и ошибка? Что если попробовать так: "=DAY(EOMONTH(DATEVALUE(""1-""&LEFTB(C1,3)),0))"
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)