Excel VBA复制/粘贴宏:使用公式忽略单元格

本文介绍了Excel VBA复制/粘贴宏:使用公式忽略单元格的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我在excel中创建了一个工具，该工具可以处理两个电子表格，并在对工作表本身进行更新时将内容从一个电子表格复制到另一个电子表格.

I have created a tool in excel which can take two spreadsheets, and copy the content from one to another when we do an update on the sheet itself.

该工具纯粹是设计为复制/粘贴工具，当前代码将在工作表之间复制和粘贴值.

The tool is purely designed to be a copy / paste tool, the current code will copy and paste values from sheet to sheet.

我必须在工具中包含逻辑以跳过带有公式的单元格，如果该工具将当前在源工作表中的公式复制并粘贴到目标工作表中，则它们将不再匹配并抛出#REF错误.关于如何在此处放置for循环的任何建议或类似的允许其使用公式检查和忽略单元格的内容的建议?我只需要复制/粘贴带有数字或值的单元格即可.

I have to include logic into the tool to skip cells with formulas, if the tool copies and pastes the formulas which are currently in the source sheet to the target sheet, they no longer match and throw #REF errors. Any suggestions on how to put a for loop in here or something similar to allow it to check and ignore cells with formulas? I need it only to copy / paste cells with numbers or values.

Sub CopyWorkbook() Dim wb1 As Workbook, wb2 As Workbook wb1.Sheets("Capex").Range("H1124:AT1173").Copy wb2.Sheets("Capex").Range("H529:AT578").PasteSpecial Paste:=xlPasteAll wb1.Sheets("Capex").Range("H1275:AT1284").Copy wb2.Sheets("Capex").Range("H580:AT589").PasteSpecial Paste:=xlPasteAll

推荐答案

如果您确实想跳过包含公式的单元格，则可以将此示例作为开始.该代码假定只有公式以等号开头.使用问题中的范围扩展示例.

If you really want to skip cells containing formulas, you could use this example as a start. The code assumes that only formulas start with an equals sign. expanding the example with the ranges in the question.

Sub example() Dim source As Range Dim target As Range Set source = ActiveSheet.Range("A1:B6") Set target = ActiveSheet.Range("D1:E6") copy_non_formulas source:=source, target:=target 'Extended example using the ranges posted in the question 'For the sake of formatting, I omitted the fully qualified 'range names. copy_non_formulas source:=Range("H1124:AT1173"), target:=Range("H529:AT578") copy_non_formulas source:=Range("H1275:AT1284"), target:=Range("H580:AT589") End Sub Public Sub copy_non_formulas(source As Range, target As Range) 'Assumes that all formulas start with '=' and all non formulas do not Dim i As Long Dim j As Long Dim c As Range For i = 1 To source.Rows.Count For j = 1 To source.Columns.Count Set c = source(RowIndex:=i, ColumnIndex:=j) If Left(c.Formula, 1) <> "=" Then target(RowIndex:=i, ColumnIndex:=j).Value = c.Value End If Next j Next i End Sub