Брайану нужно обработать рабочий лист, прежде чем его можно будет раздать другим людям. Ему нужно удалить большую часть, но не все формулы на листе. Он хочет пройти по всем ячейкам в выбранном диапазоне ячеек и, если ячейка содержит формулу, проверить эту формулу. Если формула содержит ссылку (любую ссылку) на другой лист в текущей книге, то формула игнорируется. Если формула не содержит такой ссылки, то макрос должен заменить формулу результатом формулы.

Это относительно простая задача; все, что вам нужно сделать, это пройти макрос по ячейкам и (1) выяснить, содержит ли ячейка формулу. Если да, проверьте, есть ли в формуле восклицательный знак. В ссылках на формулы используются восклицательные знаки, например:

=Sheet2!A1

Итак, если формула содержит восклицательный знак, вы можете игнорировать его. Если он не содержит восклицательного знака, вы можете заменить его его значением.

Sub ConvertFormulas1()

Dim c As Variant     Dim frm As String

On Error Resume Next

For Each c In Selection         If c.HasFormula Then             frm = c.Formula             If InStr(1, frm, "!") = 0 Then                 c.Value = c.Value             End If         End If     Next c End Sub

У этого подхода есть один недостаток: восклицательный знак будет отображаться во всех формулах, внешних по отношению к текущему листу, включая те, которые находятся в других книгах. Если вы действительно хотите заменить формулы только на другие листы в текущей книге, но игнорировать формулы, которые ссылаются на листы в других книгах, вам необходимо добавить некоторую дополнительную логику. Логика становится очевидной, если вы посмотрите, как Excel ссылается на эти другие книги:

=[OtherWorksheet.xls]Sheet1'!$C$9

Обратите внимание, что имя другой книги заключено в квадратные скобки.

Таким образом, после проверки на восклицательный знак (который информирует вас о том, что ссылка ведет на другой рабочий лист, вам необходимо проверить наличие левой скобки. Если она есть, значит, ссылка не на ячейку в текущей книге.

Sub ConvertFormulas2()

Dim c As Variant     Dim OtherSheet As Boolean     Dim frm As String

On Error Resume Next

For Each c In Selection         If c.HasFormula Then             frm = c.Formula             OtherSheet = False             If InStr(1, frm, "!") Then                 OtherSheet = True                 If InStr(1, frm, "[") Then                     OtherSheet = False                 End If             End If             If Not OtherSheet Then                 c.Value = c.Value             End If         End If     Next c End Sub

Следует отметить, что было бы относительно легко изменить формулу, используемую в этом макросе, чтобы она избавлялась от всех внешних ссылок, оставляя ссылки на текущий рабочий лист не повреждены. Фактически, все, что вам нужно сделать, это избавиться от проверки скобки, а затем избавиться от ключевого слова «Not» в структуре, которая проверяет переменную OtherSheet.

_Примечание: _

Если вы хотите узнать, как использовать макросы, описанные на этой странице (или на любой другой странице на сайтах ExcelTips), я подготовил специальную страницу с полезной информацией .

`ссылка: / excelribbon-ExcelTipsMacros [Щелкните здесь, чтобы открыть эту специальную страницу в новая вкладка браузера] `.

ExcelTips — ваш источник экономичного обучения Microsoft Excel.

Этот совет (6959) применим к Microsoft Excel 97, 2000, 2002 и 2003. Вы можете найти версию этого совета для ленточного интерфейса Excel (Excel 2007 и новее) здесь:

link: / excelribbon-Replacing_Some_Formulas_with_the_Formula_Results [Замена некоторых формул результатами формул].