Excel VBA 复制/粘贴宏：忽略带有公式的单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25518173/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel VBA Copy / Paste macro: Ignore cells with Formulas
提问by PootyToot
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.
我在 excel 中创建了一个工具，它可以处理两个电子表格，并在我们对工作表本身进行更新时将内容从一个复制到另一个。
The tool is purely designed to be a copy / paste tool, the current code will copy and paste values from sheet to sheet.
该工具纯粹设计为复制/粘贴工具，当前代码将在工作表之间复制和粘贴值。
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.
我必须在工具中包含逻辑以跳过带有公式的单元格，如果该工具将当前在源工作表中的公式复制并粘贴到目标工作表中，它们将不再匹配并抛出 #REF 错误。关于如何在这里放置 for 循环或类似的东西以允许它检查和忽略带有公式的单元格的任何建议？我只需要它来复制/粘贴带有数字或值的单元格。
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
回答by brettdj
Rather than loop cell by cell you can use SpecialCellsto identify the formulae
您可以SpecialCells用来识别公式，而不是逐个单元格循环
There are two options
有两种选择
- Copy only the
Constantcells to the destination - Remove any
Formulacells from the destination
- 仅将
Constant单元格复制到目标 Formula从目标中删除任何单元格
If your formulae occur in a single contigious block then (1) works easily, else this will result in a number of areas needing to copied over. So (2) is preferable
如果您的公式出现在单个连续块中，那么 (1) 很容易工作，否则这将导致需要复制多个区域。所以（2）是可取的
Your first range can be covered as so.
您的第一个范围可以如此覆盖。
Dim rng1 As Range
Set rng1 = Range("H1124:AT1173")
rng1.Copy [h1275]
On Error Resume Next
[h1275].Resize(rng1.Rows.Count, rng1.Columns.Count).SpecialCells(xlFormulas) = vbNullString
On Error GoTo 0
回答by Frank
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. Edit: 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
回答by Peter
Can't you just use Paste:=xlPasteValuesfor all cells? That way no formulas get copied to the target sheet.
你不能只Paste:=xlPasteValues用于所有单元格吗？这样就不会将公式复制到目标工作表中。
