Excel VBA复制/粘贴宏:使用公式忽略单元格 [英] Excel VBA Copy / Paste macro: Ignore cells with Formulas

问题描述

我在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

这篇关于Excel VBA复制/粘贴宏:使用公式忽略单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！