Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Автоматическая замена в диапазоне ячеек функций их значениями по условию вне этого диапазона
 
#1
10.12.2017 20:30:29
Здравствуйте, уважаемые!
Прошу Вас помочь словом/делом.
Есть: длинная горизонтальная таблица, почти в каждой ячейке которой формулы. Эта таблица и есть ЦЕЛЕВОЙ ДИАПАЗОН (назову её так). В примере строки [5-8].
Над нею строка дат (в примере прямая нумерация: строка 3).
В примере ячейка I1 "несет" контрольное значение (дата "сегодня").
Требуется: Если значение некой ячейки со строки 3 = значению контрольной ячейки I1 -> функции ячеек предыдущего столбика из Целевого Диапазона переписать на их значения. Т.е. в примере: F3 = $I$1 -> в диаразоне [E5:E8] формулы заменить значениями. Подразумевается, что "вчера" значение I1 было равно 4, "позавчера" = 3. Таким образом получается, что все предыдущие (относительно I1) ячейки Целевого Диапазона уже перезаписаны их значениями, а [F5:I8] - содержат формулы и пока считают.

Учусь VBA (недавно начал).
Пытался накорябать польз.функцию и вызывать её при выполнении условия (строка 12 примера). Не-а.))

Код
Public Function МояЗаменяка(rng As Range)
    Dim cell As Range
    Selection.Interior.ColorIndex = 6
    For Each cell In rng
        cell.Formula = cell.Value
    Next cell
'    МояЗаменяка = "заменил"
End Function

Бить по рукам - можно! Даже НУЖНО!!!

Изменено: Ale_ko - 10.12.2017 20:35:00
 
 
 
#2
10.12.2017 21:09:36
В какой момент должен запускаться макрос? Когда пользователь изменяет данные в "I1"? Или когда пользователь изменяет данные в строке 3? Или в каком-то другом случае?
 
 
 
#3
10.12.2017 21:13:40
Данные в I1 изменяются автоматически при запуске файла.
Программа/функция/макрос достаточно запустить также - при запуске файла.
 
 
 
#4
10.12.2017 21:25:35
Макрос находится в модуле книги и запускается при открытии книги. Если при открытии книги вверху есть желтая панель для разрешения макросов, то нужно разрешить макросы.
 
 
 
#5
10.12.2017 21:43:30
Благодарю! Работает прекрасно.
Пробежался, сразу не понял для чего:
Код
lc = shRes.Cells(3, shRes.Columns.Count).End(xlToLeft).Column
arr() = shRes.Range("A3").Resize(, lc).Value
и... Ну тут я мудахнулся...
В искомом файле I1 идет строкой 1... (сегодня там в ячейке YF1)
Код
varI1 = shRes.Range("I1").Value
не получиться так.
Можно поправить?
 
 
 
#6
10.12.2017 21:50:46
Цитата
Ale_ko написал:
Ну тут я мудахнулся...
Не понял Вас. Вы разобрались, для чего нужен код или нет?
Вместо "I1" какую ячейку смотреть? Или ячейка меняется: сегодня одна, завтра другая? Если да, то по какому принципу меняется ячейка в строке 1?
 
 
 
#7
10.12.2017 22:08:49
Цитата
Или ячейка меняется: сегодня одна, завтра другая? Если да, то по какому принципу меняется ячейка в строке 1?
Сегодня одна, завтра - другая.
Принцип (см. строку формул на картинке выше): когда я завтра открою этот файл будет 11.12.2017, что буде соответствовать 201712 в строке 2, и числу 11 в ячейке YG3. Треугольничек завтра будет в YG1 (согласно условия: ЕСЛИ СЕГОДНЯ = дате из таблицы, ТО рисовать треугольничек (дата формируется в удобоваримый для Excel вид в строках 4 и 5)).

Итак. Мне нужно по значению ячейки "сегодня" (YF1) перезаписать значениями диапазон [YE4:YE127].
Завтра "сегодня" будет в (YG1) и перезаписывать будем уже диапазон [YF4:YF127].
Изменено: Ale_ko - 11.12.2017 19:11:38
 
 
 
#8
10.12.2017 22:17:49
Макрос не смотрит данные в строке 1, а смотрит текущую дату с помощью функции "Date".
Посмотрите комментарий в макросе.
Код
Private Sub Workbook_Open()
    Dim shRes As Worksheet, dateToday As Date
    Dim arr(), lc As Long, j As Long
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Set shRes = Worksheets("Лист2")
    lc = shRes.Cells(5, shRes.Columns.Count).End(xlToLeft).Column
    arr() = shRes.Range("A5").Resize(, lc).Value
    dateToday = Date
    For j = 1 To lc
        If arr(1, j) = dateToday Then
            'Здесь вместо строк "5:8" укажите нужные строки, в которых нужно заменить формулы на значения.
            With shRes.Rows("5:8").Columns(j - 1)
                .Value = .Value
            End With
            Exit For
        End If
    Next j
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
End Sub
Изменено: Karataev - 10.12.2017 22:28:38
 
 
 
#9
10.12.2017 22:21:23
Karataev, простите, я что-то не так делаю наверное...
" + Марос " у меня не открывается...
 
 
 
#10
10.12.2017 22:24:19
Убрал в посте 8 спойлер, наверное в настройках браузера что-то у Вас.
 
 
 
#11
10.12.2017 22:34:25
Аха. Наверное лисица не может.
Спасибо Вам!
С Вашего позволения, Я с ответом повременю до завтра. Внедрю в рабочий файл, посмотрю как работает, и ОБЯЗАТЕЛЬНО отпишусь.

Млин Я второй день череплю, а толку нет. За 2 часа понять "чего он хочет" и выдать... Преклоняюсь!
 
 
 
#12
10.12.2017 23:07:59
Karataev
Не удержался...
Ваш код внедрил (также в "Эта книга"), в указанном месте уточнил диапазон, изменил название листа...

ПРЕКРАСНО!!!
Задача решена. Завтра буду разбираться с кодом. Прекраснейший пример.
 
 
 
#13
11.12.2017 16:15:04
Да не сочтите за наглость моё стремление к совершенству...

2 момента...
1.  Большая часть строк Целевого Диапазона (содержащая формулы)  заблокирована - дабы случайно не "зацепить" формул. Когда лист под  защитой - при запуске файла идет ошибка, макрос не может корректно  отработать. Можно ли в програмно разблокировать ячейку ->  перезаписать -> снова заблокировать. Ну или как-то иначе поступить.
При установке защиты листа галочками разрешаю:
- выделение незаблокированных ячеек
- форматирование ячеек
- изменение объектов
Всё остальное - низя.

2.  Из практики: файл запускается ежедневно. Но иногда бывает дня 2...4  пропуска. Как можно расширить диапазон выполнения перезаписи с одной  колонки на колонок 5 - этого с лихвой достаточно.

3. Я боюсь даже заикаться...
Изменено: Ale_ko - 11.12.2017 19:31:08
 
 
 
#14
23.12.2017 20:28:02
Приведённый выше код, решением не является.
Автору кода спасибо за участие, самореклама НЕ удалась!
Платить Вам за решение таких задач НЕ станут.

Обращение сюда было ошибкой. Жаль.
Мельчает нынче Программист...
 
 
 
#15
23.12.2017 20:58:58
Ale_ko, Ваш пост #14 в чей адрес?
 
 
 
#16
23.12.2017 21:15:20
Юрий М, Вы третий...
"решение" в посте#8 было дано Karataev.
В посте#13 на приложенных скринах показано, что при определенных условиях оно не работает.
При личном обращении (в пределах форума) к автору решения Karataev, был получен ответ, в котором данная задача считается БОЛЬШОЙ и решаться он будет либо за денежное вознаграждение, либо мою МЕГАзадачу бить на куски, а впоследствии собирать воедино.

А теперь по-Русски! Это большая задача?! За это сегодня программист деньги берет? А форум - это... площадка для рекламы своих навыков и привлечения клиентов. Я конечно понимаю, капитализм, и т.д., но...
Цель форума?
И... теперь Вам ясен смысл поста#14?
Собственно ответ: пост#14 был адресован to Karataev
Изменено: Ale_ko - 23.12.2017 21:25:40
 
 
 
#17
23.12.2017 21:32:33
Код, предложенный Каратаевым, рабочий: Вы сами об этом писали в #12. А то, что Вы перед этим ни слова не сказали про защиту листа (решается элементарно) - Ваша вина. И говорить, что "Приведённый выше код, решением не является" - неправильно!
Про "саморекламу": человек потратил своё время на помощь Вам - это самореклама? Следуя Вашей логике, все, кто здесь выкладывает решения, помогая гостям,  занимаются саморекламой?
Ваш #14 - чистой воды хамство. Или это у Вас такой стиль выражения благодарности?
Про цель форума: помогать тем, у кого что-то не получается. А в данном случае Каратаев всё сделал за Вас. Всё! Это помощь?
Про ошибку обращения сюда: в Сети полно форумов по Excel - Вас здесь никто не держит. Да и не нужны нам тут такие хамовитые и наглые посетители. Сомневаюсь, что кто-то захочет Вам ПОМОГАТЬ при таком отношении к помогающим.
Про "Мельчает нынче Программист": учитесь программировать, помогайте потом другим и не "мельчайте".
И последнее: считаю, что Вы нарушили п. 3.4 наших Правил. Реагирую соответственно - бан!
 
 
 
Страницы: 1
Читают тему
Наверх