Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Макрос для замены формул на значения в определенном диапазоне ячеек
 
#1
10.12.2018 14:24:49

Всем здравствуйте!

Помогите «докрутить» макрос, методом «тыка» никак не получается.

На лист despatches_RUB  с  листа5 копирую данные. После того, как убеждаюсь, что цены подтянулись правильно и суммы совпадают с 1с, с помощью макроса (в файле его нет) делаю заливку новых строк по шаблону («встаю на первую незакрашенную ячейку в столбце С (в данном случае это с68) и запускаю макрос). Строки закрашиваются. Очень хочется сделать так, чтобы при этом последовательно запустился еще один макрос, который бы заменил формулы на значения в столбце J (цены), потому что если я забуду вручную это сделать и потом на листе price укажу новые цены, то пересчитаются суммы и в «старых отгрузках», а это конечно неправильно. Т.е. в данном случае должны появиться значения вместо формул в диапазоне J68:J70, а в ячейках J71 и ниже формулы должны остаться. Как это сделать?

Я попыталась соорудить вот такую конструкцию:

Скрытый текст

Но она работает не так, как надо – захватывает бОльший диапазон, чем надо. Прочитала, что в этом случае ячейки с формулами и даже ячейка с заливкой уже считаются «не пустой».

Нашла вот такой урок:

Определение последней строки и столбца, а так же адрес ячейки методом Find

Скрытый текст
Но не знаю, как это применить к своей задаче, мозг тупит…
Помогите, пожалуйста!
 
 
 
#2
10.12.2018 14:49:32
Здравствуйте. Может быть вам проще будет "завязаться" на выделение? Вставили в таблицу новый строки, проверили правильность "подтягивания" чисел в формулах, далее, выделяете в столбце С ячейки этих строк и запускаете нечто такое:
Код
Sub ColoredByElena()
Dim objC as Range
For each objC in Selection 'перебираем все ячейки в выделении
'сначала красим строку в таблице для текущей ячейки. Сначала методом Resize увеличиваем диапазон от текущей ячейки по строке и потом его закрашиваем
objC.resize(0, 11).Interior.Color = vbGreen 'или какой захотите задать
'а дальше меняем формулу на значение в ячейке столбца J (J столбец отстоит от столбца С на 7 столбцов). Используем метод Offset
objC.offset(0,7)=objC.offset(0,7).value
Next objC 'переходим к следующей ячейке из выделения
End Sub
Изменено: Пытливый - 10.12.2018 14:50:32
Кому решение нужно - тот пример и рисует.
 
 
 
#3
10.12.2018 14:51:06
Может так?
Код
Columns("j").Value = Columns("j").Value
"Все гениальное просто, а все простое гениально!!!"
 
 
 
#4
10.12.2018 15:00:31
Вот так можно немного подкорректировать, возможно и работать будет быстрей и код гораздо короче
Код
        If arrSrc(i, 16) <> 0 Then
            Set shRes = bkRes.Worksheets(arrSrc(i, 4))
            With shRes
                lr = .Columns("A").Find(What:="*", LookIn:=xlFormulas, LookAt:= _
                    xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False _
                    , SearchFormat:=False).Row + 1
                'Здесь допишите для остальных ячеек по аналогии. Сейчас сделано для двух ячеек.
'               <вот так>
                .Cells(lr, 1).Resize(, 15).Value = shSrc.Cells(i, 2).Resize(, 15).Value
'               <вместо этого всего:>
'                shRes.Cells(lr, "A").Value = shSrc.Cells(i, "B").Value
'                shRes.Cells(lr, "B").Value = shSrc.Cells(i, "C").Value
'                shRes.Cells(lr, "C").Value = shSrc.Cells(i, "D").Value
'                shRes.Cells(lr, "D").Value = shSrc.Cells(i, "E").Value
'                shRes.Cells(lr, "E").Value = shSrc.Cells(i, "F").Value
'                shRes.Cells(lr, "F").Value = shSrc.Cells(i, "G").Value
'                shRes.Cells(lr, "G").Value = shSrc.Cells(i, "H").Value
'                shRes.Cells(lr, "H").Value = shSrc.Cells(i, "I").Value
'                shRes.Cells(lr, "I").Value = shSrc.Cells(i, "J").Value
'                shRes.Cells(lr, "J").Value = shSrc.Cells(i, "K").Value
'                shRes.Cells(lr, "K").Value = shSrc.Cells(i, "L").Value
'                shRes.Cells(lr, "L").Value = shSrc.Cells(i, "M").Value
'                shRes.Cells(lr, "M").Value = shSrc.Cells(i, "N").Value
'                shRes.Cells(lr, "N").Value = shSrc.Cells(i, "O").Value
'                shRes.Cells(lr, "O").Value = shSrc.Cells(i, "P").Value
            End With
        End If
Изменено: Nordheim - 10.12.2018 15:02:44
"Все гениальное просто, а все простое гениально!!!"
 
 
 
#5
10.12.2018 16:12:06
Пытливый, спасибо, заменить формулы на значения получилось. Но вот с заливкой у меня там все сложно... там для каждого покупателя свой цвет, "шаблон" находится на листе products и для заливки у меня был вот такой макрос:
Скрытый текст
Как его теперь преобразовать под Ваш способ, я вообще не представляю... А я бы хотела, чтобы оба эти макроса запускались одной кнопкой, поэтому надо тогда чтобы оба срабатывали или на активную ячейку или на выделенный диапазон. Как это сделать?  
 
 
 
#6
10.12.2018 16:18:27
Nordheim, извините, но у меня не хватает знаний, чтобы разобраться в Вашем предложении... Я с макросами не то что "на Вы", я на них только "смотрю"... Спасибо за ответ, но я что-то совсем не соображаю уже.... Оно нормально в принципе работает, хотя в таблице уже больше 13 тыс. строк... Просто не успеваю вручную заносить данные (как изначально было), вот и приходится изобретать "велосипед". Наверное настоящий программист все это сделал бы намного проще и красивее, но увы, у нас такого нет.
 
 
 
#7
10.12.2018 16:29:49
Можно как-то вот так.

Код
Sub ColoredByElena()
Dim objC as Range, objR as range
For each objC in Selection 'перебираем все ячейки в выделении
'для определения, в какой цвет красить запускаем метод Find на соотв.листе
Set objR = Worksheets("products").Columns(1).Find(What:=Cells(objC.row, 5).Value)'для обозначения строки с искомым, используем номер строки обрабатываемой ячейки
'если поиск успешен, то красим диапазон в нужный цвет (предварительно его определяем от обрабатываемой ячейки
'через Resize. А вот если поиск не успешен - я бы добавил ругань на неверные данные и выход из процедуры
If Not objR Is Nothing Then 
objC.resize(0, 11).Interior.Color = objR.interior.Color
'а дальше меняем формулу на значение в ячейке столбца J (J столбец отстоит от столбца С на 7 столбцов). Используем метод Offset
objC.offset(0,7)=objC.offset(0,7).value
Else
MsgBox "Такого товара нет, в какой цвет красить - непонятно!", vbcritical 'если не нашли товар в списке, ругаемся
Exit Sub 'и выходим из процедуры
End If
Next objC 'переходим к следующей ячейке из выделения
End Sub
Кому решение нужно - тот пример и рисует.
 
 
 
#8
10.12.2018 16:49:05
Не получается, выделяет как ошибку вот эту строку:
objC.Resize(0, 11).Interior.Color = objR.Interior.Color
 
 
 
#9
10.12.2018 16:54:55
Ну да, это я затупил. Надо так:
Код
objC.resize(1, 11).Interior.Color = objR.interior.Color
а то получается, переопредялеям диапазон из 0 строк и 11 столбцов - так не бывает. Хоть одна строка должна быть. :)
Кому решение нужно - тот пример и рисует.
 
 
 
#10
11.12.2018 08:10:28
Пытливый, большое спасибо! Теперь работает!
 
 
 
Страницы: 1
Наверх