На листе 1, к значениям проставляемых в столбцах ABCDE, с помощью ВПР, подтягиваются значения с листа 2.
Как сделать так что бы подтянувшись раз они фиксировались, и при дальнейшем изменении данных на листе 2, не менялись на листе 1 ? Очень нужно как то придумать следующую схему: 1. На листе 1 вводим данные, в новую строку, к ним подтягиваются данные с листа 2. 2. Результат фиксируется. 3. На листе 2 обновляются данные (при этом на листе 1 изменения не происходят) 4. При введении данных на новой строке листа 1, данные снова подтягиваются. 5. Результат фиксируется.
В общем нужно как то сделать одноразовый ВПР Возможно ли такое в Екселе?
установить число итераций при вычислениях в 1 написать формулу, ссылающуюся на саму себя( на свою ячейку) - если ячейка пуста, то подтянуть данные с другого листа, в прот случае ничего не делать
В принципе, если строка на Лист1 в столбцах А, С, Е и т.д. заполняется полностью (т.е. отсутствует такая ситуация, когда в А 4 значения, а в С 5), то можно применить простенький макрос:
Код
Sub FixigValue()
Dim lngI As Long
For lngI = 2 To Cells(Rows.Count, 1).End(xlUp).Row
Cells(lngI, 2) = Cells(lngI, 2).Value: Cells(lngI, 4) = Cells(lngI, 4).Value
Cells(lngI, 6) = Cells(lngI, 6).Value: Cells(lngI, 8) = Cells(lngI, 8).Value
Cells(lngI, 10) = Cells(lngI, 10).Value
Next
End Sub
Пытливый, как то странно работает. До четвертой строки отрабатывает хорошо. Подтянув в ячейку данные убивает формулу. А дальше четвертой строки почему то не работает.
сейчас ввел в 6 строку все данные в А, С, E, G, I, запустил макрос, формула заменилась на значение с 2 до 6 строки. Ниже формула не трогается, т.к. пока не введены данные в указанных столбцах, там должна быть формула, макрос обрабатывает строки, заполненные значениями в столбце А.
А что не устраивает? НД? Тогда надо формулу немного изменить, вместо:
Код
=ВПР(A:A;Лист2!A:B;2;0)
надо
=ЕСЛИОШИБКА(ВПР(A:A;Лист2!A:B;2;0);"")
Тогда и НД в незаполненных строках лезть не будут, и реакция на введение данных не пропадет.
З.Ы. Я процедуру в модуль запихивал и оттуда запускал, а из модуля листа действительно, почему-то ругань.... хм.... "end of statement" ему... ишь....
1.Копия с работающим кодом - прилагаю (код макроса - в стандартном модуле, а не в модуле листа) 2.Можно присобачить запуск кода к какому-либо событию листа, например, изменению в ячейке. Просто надо решить, к какому. Например, если его "привязать" к изменению значений ячеек в столбце А, то только вы ввели чего в А столбце - он сработал и "убил" формулы (заменил на значения) во всех соответствующих ячейках строки, причем в С, Е и т.д. вы значения еще не вводили. А формулы там уже убитые. Я поэтому и спрашивал, бывает такая ситуация, когда строка в А, С, Е и т.д. заполнена не вся, т.е. в А заполнено 4 строки, а в С - 3? Если бывает, и надо убивать конкретно при введении кода в соответствующем столбце - понадобиться слегка подправить макрос и привязать его к соответствующему событию листа. Если надо - напишите об этом.
Пытливый написал: 2.Можно присобачить запуск кода к какому-либо событию листа, например, изменению в ячейке. Просто надо решить, к какому.
У меня к ячейкам столбца А уже привязан макрос, который реагирует на изменение этих ячеек. Если сюда еще привязать - макросы не будут конфликтовать? Был бы крайне благодарен, чему-то вроде этого: При добавлении данных в А3, скрипт убивает формулу в B2, D2, F2, H2, J2, L2, N2, При добавлении данных в А4, скрипт убивает формулу в B3, D3, F3, H3, J3, L3, N3 и т.д.
Самый естественный способ - макросом вставить формулы и заменить их на значения
.Мне всегда казалось, что при использовании макросов, естественее всего от формул отказаться вовсе, считать все в коде и выгружать на лист готовые значения. Тем более по теме вроде как и нужны именно значения. ИМХО
Согласие есть продукт при полном непротивлении сторон
Sanja написал: Мне всегда казалось, что при использовании макросов, естественее всего от формул отказаться вовсе
Зависит от задачи, конечно. Если нужно получить результат на листе И это можно сделать несложной формулой, то почему бы нет? Попробуйте сделать то, что по ссылке (ВПР с данными из закрытой книги) - без формул. Сколько строк в коде будет, сколько времени его писать и отлаживать?
Алексей, ну это крайне редко, когда нужно что-то типа ВПР с данными из закрытой книги - как правило, простейшие вычисления пытаются сделать формулой, которую нужно макросом размножить))
Объединил 2 макроса на 1 листе, но вначале вылезала ошибка ambiguous name detected worksheet_change Потом понял, что матерится на одинаковые имена. В итоге засунул оба кода в одну процедуру и все получилось :
Код
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns("a")) Is Nothing Then Exit Sub
lr = Sheets(2).Cells(Rows.Count, "A").End(xlUp).Row + 1
Sheets(2).Range("A" & lr, "M" & lr) = Me.[R12:AD12].Value
Dim intI As Integer
If Target.Cells.Count > 1 Then
Exit Sub
ElseIf Not Intersect(Target, Range("A:A")) Is Nothing Then
' Call FixigValue
For intI = 2 To 14 Step 2
Cells(Target.Row - 1, intI) = Cells(Target.Row - 1, intI).Value
Next intI
End If
End Sub
Если не сложно, поясните пож-та, не до конца ясен механизм.
Я понял что за то, какие именно ячейки нужно очистить от формул - B, D, F, H, J, L, N, отвечает эта часть кода:
Код
For intI = 2 To 14 Step 2
Скажите а где тут указано что нужно убивать в вышестоящей строке, а не в той в которой происходит событие?
Вот эта часть кода заменяет формулу на ее значение. Как совершенно верно указал ув.Юрий М, в данной строке номер строки изменяемой ячейки (Target) уменьшается на 1, т.е. работаем с предыдущей строкой. А переменная itnI определяет, с каким столбцом работаем - она циклически меняется от 2 до 14 с шагом 2, т.е. принимает значения 2, 4, 6 и т.д.