Здравствуйте! Есть ли возможность решить такую задачу: На листе в достаточно объёмной таблице полно формул. Изначально все они возвращают пустые ячейки. При работе с файлом постепенно ячейки заполняются некоторыми значениями. Возможно ли сделать так, чтобы в тех ячейках, в которых формула вернула некое значение (любое отличное от ""), этот результат сохранялся бы "как значение", т.е. чтобы там больше не было бы формулы? Идеально - сразу, как только формула "сработала". Вполне устроит, если при закрытии файла все формулы, которые за время работы с ним вернули какие-то результаты, сохранились бы "как значения"
Здравствуйте! Есть ли возможность решить такую задачу: На листе в достаточно объёмной таблице полно формул. Изначально все они возвращают пустые ячейки. При работе с файлом постепенно ячейки заполняются некоторыми значениями. Возможно ли сделать так, чтобы в тех ячейках, в которых формула вернула некое значение (любое отличное от ""), этот результат сохранялся бы "как значение", т.е. чтобы там больше не было бы формулы? Идеально - сразу, как только формула "сработала". Вполне устроит, если при закрытии файла все формулы, которые за время работы с ним вернули какие-то результаты, сохранились бы "как значения"maverick_77
Уважаемый KSV, Простите мне мою неграмотность в этих вопросах. Но как я ни старался сам что-то сделать, не выходит каменный цветок без помощи квалифицированных специалистов. Выдаёт постоянно ошибку:
Цитата
Run-time error '13': Type mismatch
Не могу прикрепить файл; он весит 800 килобайт.
[vba]
Код
Option Explicit
Sub Macro1() ' должен сохранять ненулевые результаты формул, как значения
Dim i&, f(), v() Dim firstRow As Long Dim firstCol As Long Dim nameCol As Long Dim lastRow As Long Dim Col1 As Long Dim Col2 As Long Dim Col3 As Long Dim Col4 As Long
Sheets("Детали").Activate If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData
' сохраняем значение формул, "как значения" With ThisWorkbook.Worksheets("Детали").Range(Sheets("Детали").Cells(firstRow, Col1), _ Sheets("Детали").Cells(lastRow, Col2)) f = .Formula v = .Value For i = 1 To UBound(v) If Len(v(i, 1)) Then f(i, 1) = v(i, 1) Next .Formula = f End With
End Sub
[/vba]
Что я не так сделал?
Уважаемый KSV, Простите мне мою неграмотность в этих вопросах. Но как я ни старался сам что-то сделать, не выходит каменный цветок без помощи квалифицированных специалистов. Выдаёт постоянно ошибку:
Цитата
Run-time error '13': Type mismatch
Не могу прикрепить файл; он весит 800 килобайт.
[vba]
Код
Option Explicit
Sub Macro1() ' должен сохранять ненулевые результаты формул, как значения
Dim i&, f(), v() Dim firstRow As Long Dim firstCol As Long Dim nameCol As Long Dim lastRow As Long Dim Col1 As Long Dim Col2 As Long Dim Col3 As Long Dim Col4 As Long
Sheets("Детали").Activate If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData
' сохраняем значение формул, "как значения" With ThisWorkbook.Worksheets("Детали").Range(Sheets("Детали").Cells(firstRow, Col1), _ Sheets("Детали").Cells(lastRow, Col2)) f = .Formula v = .Value For i = 1 To UBound(v) If Len(v(i, 1)) Then f(i, 1) = v(i, 1) Next .Formula = f End With
PS не факт, что так просто, но, скорее всего, у вас происходит считывание 1 ячейки, а при этом Excel вместо массива выдает строку (string). В общем, без поллитры (файла), не разобраться.
Шаг 1 Исправьте [vba]
Код
Dim i&, f(), v()
[/vba] на [vba]
Код
Dim i&, f, v
[/vba]
PS не факт, что так просто, но, скорее всего, у вас происходит считывание 1 ячейки, а при этом Excel вместо массива выдает строку (string). В общем, без поллитры (файла), не разобраться.RAN
Быть или не быть, вот в чем загвоздка!
Сообщение отредактировал RAN - Пятница, 31.07.2015, 17:51
=))) залил файл на файлообменник: удалено администрацией ибо сюда можно только до 100кб [moder]Ну так и сделайте до 100 кб и положите сюда![/moder]
=))) залил файл на файлообменник: удалено администрацией ибо сюда можно только до 100кб [moder]Ну так и сделайте до 100 кб и положите сюда![/moder]maverick_77
если нельзя, но очень хочется, то можно!
Сообщение отредактировал Manyasha - Пятница, 31.07.2015, 18:06
maverick_77, формулы, которые могут вернуть ошибку, оберните
Код
=ЕСЛИОШИБКА(<ваша формула>; "")
И зачем вы ищите столбцы через поиск, если это можно делать гораздо проще, а главное - быстрее, используя именованные диапазоны?
[vba]
Код
Sub Macro1() ' должен сохранять ненулевые результаты формул, как значения
Dim i&, r&, c&, f(), v() Dim firstRow As Long Dim firstCol As Long Dim nameCol As Long Dim lastRow As Long Dim Col1 As Long Dim Col2 As Long Dim Col3 As Long Dim Col4 As Long
With Sheets("Детали") If .FilterMode Then .ShowAllData
'!!! см. именованные диапазоны (Ctrl+F3) ' находим все нужные столбцы и первую строку таблицы, используя именованные диапазоны With .Range("Дата_записи") firstRow = .Row + 2 ' находим первую строку всей таблицы (где написано "Дата записи" + 2 строчки) firstCol = .Column ' находим номер первого столбца всей таблицы (где написано "Дата записи") End With
' Находим крайнюю строку таблицы на текущий момент nameCol = .Range("ФИО_НТП").Column ' определяем номер столбца, в котором пишутся ФИО lastRow = .Cells(Rows.Count, nameCol).End(xlUp).Row ' присваиваем переменной значение крайней заполненной строки
' Определяю номер столбца "Куратор (назнач.)" Col1 = .Range("Куратор_назнач").Column ' Определяю номер столбца "Подразделение (назнач.)" Col2 = .Range("Подразделение").Column ' Определяю номер столбца "Склад транзит." Col3 = .Range("Склад_транзит").Column ' Определяю номер столбца "Куратор транзит." Col4 = .Range("Куратор_транзит").Column
' сохраняем значение формул, "как значения" With .Range(.Cells(firstRow, Col1), .Cells(lastRow, Col2)) f = .Formula ' сохраняем в массив формулы в выбранном диапазоне v = .Value ' сохраняем в массив значения в выбранном диапазоне i = UBound(v, 2) ' кол-во столбцов в выбранном диапазоне For r = 1 To UBound(v) ' кол-во строк в выбранном диапазоне For c = 1 To i If Len(v(r, c)) Then f(r, c) = v(r, c) ' если значение ячейки НЕ пустая строка – заменяем формулу значением Next c, r .Formula = f ' записываем измененный массив в выбранный диапазон End With End With End Sub
[/vba]
maverick_77, формулы, которые могут вернуть ошибку, оберните
Код
=ЕСЛИОШИБКА(<ваша формула>; "")
И зачем вы ищите столбцы через поиск, если это можно делать гораздо проще, а главное - быстрее, используя именованные диапазоны?
[vba]
Код
Sub Macro1() ' должен сохранять ненулевые результаты формул, как значения
Dim i&, r&, c&, f(), v() Dim firstRow As Long Dim firstCol As Long Dim nameCol As Long Dim lastRow As Long Dim Col1 As Long Dim Col2 As Long Dim Col3 As Long Dim Col4 As Long
With Sheets("Детали") If .FilterMode Then .ShowAllData
'!!! см. именованные диапазоны (Ctrl+F3) ' находим все нужные столбцы и первую строку таблицы, используя именованные диапазоны With .Range("Дата_записи") firstRow = .Row + 2 ' находим первую строку всей таблицы (где написано "Дата записи" + 2 строчки) firstCol = .Column ' находим номер первого столбца всей таблицы (где написано "Дата записи") End With
' Находим крайнюю строку таблицы на текущий момент nameCol = .Range("ФИО_НТП").Column ' определяем номер столбца, в котором пишутся ФИО lastRow = .Cells(Rows.Count, nameCol).End(xlUp).Row ' присваиваем переменной значение крайней заполненной строки
' Определяю номер столбца "Куратор (назнач.)" Col1 = .Range("Куратор_назнач").Column ' Определяю номер столбца "Подразделение (назнач.)" Col2 = .Range("Подразделение").Column ' Определяю номер столбца "Склад транзит." Col3 = .Range("Склад_транзит").Column ' Определяю номер столбца "Куратор транзит." Col4 = .Range("Куратор_транзит").Column
' сохраняем значение формул, "как значения" With .Range(.Cells(firstRow, Col1), .Cells(lastRow, Col2)) f = .Formula ' сохраняем в массив формулы в выбранном диапазоне v = .Value ' сохраняем в массив значения в выбранном диапазоне i = UBound(v, 2) ' кол-во столбцов в выбранном диапазоне For r = 1 To UBound(v) ' кол-во строк в выбранном диапазоне For c = 1 To i If Len(v(r, c)) Then f(r, c) = v(r, c) ' если значение ячейки НЕ пустая строка – заменяем формулу значением Next c, r .Formula = f ' записываем измененный массив в выбранный диапазон End With End With End Sub