时间戳

首页 > 解决方案 > 如何解决此“编译错误：无效或不合格的参考”

excel - 如何解决此“编译错误：无效或不合格的参考”

问题描述

我对 vba 宏完全陌生，似乎无法找到解决我的错误的方法。我有以下代码：

Sub BuilderUpdate()

Range("B2").Copy Worksheets("Pack").Range("B2")
.PasteSpecial xlPasteValues
Range("D2").Copy Worksheets("Pack").Range("C2")
.PasteSpecial xlPasteValues
Range("F2").Copy Worksheets("Pack").Range("D2")
.PasteSpecial xlPasteValues
Range("H2").Copy Worksheets("Pack").Range("E2")
.PasteSpecial xlPasteValues
Range("B3").Copy Worksheets("Pack").Range("F2")
.PasteSpecial xlPasteValues
Range("D3").Copy Worksheets("Pack").Range("G2")
.PasteSpecial xlPasteValues
Range("F3").Copy Worksheets("Pack").Range("H2")
.PasteSpecial xlPasteValues
Range("H3").Copy Worksheets("Pack").Range("I2")
.PasteSpecial xlPasteValues
Range("B4").Copy Worksheets("Pack").Range("J2")
.PasteSpecial xlPasteValues
Range("D4").Copy Worksheets("Pack").Range("K2")
.PasteSpecial xlPasteValues
Range("F4").Copy Worksheets("Pack").Range("L2")
.PasteSpecial xlPasteValues
Range("H4").Copy Worksheets("Pack").Range("M2")
.PasteSpecial xlPasteValues
Range("B5").Copy Worksheets("Pack").Range("N2")
.PasteSpecial xlPasteValues
Range("D5").Copy Worksheets("Pack").Range("O2")
.PasteSpecial xlPasteValues
Range("F5").Copy Worksheets("Pack").Range("P2")
.PasteSpecial xlPasteValues
Range("H5").Copy Worksheets("Pack").Range("Q2")
.PasteSpecial xlPasteValues
Range("B6").Copy Worksheets("Pack").Range("R2")
.PasteSpecial xlPasteValues
Range("D6").Copy Worksheets("Pack").Range("S2")
.PasteSpecial xlPasteValues
Range("F6").Copy Worksheets("Pack").Range("T2")
.PasteSpecial xlPasteValues
Range("H6").Copy Worksheets("Pack").Range("U2")
.PasteSpecial xlPasteValues
Range("B7").Copy Worksheets("Pack").Range("V2")
.PasteSpecial xlPasteValues
Range("D7").Copy Worksheets("Pack").Range("W2")
.PasteSpecial xlPasteValues
Range("F7").Copy Worksheets("Pack").Range("X2")
.PasteSpecial xlPasteValues
Range("H7").Copy Worksheets("Pack").Range("Y2")
.PasteSpecial xlPasteValues
Range("B8").Copy Worksheets("Pack").Range("Z2")
.PasteSpecial xlPasteValues
Range("D8").Copy Worksheets("Pack").Range("AA2")
.PasteSpecial xlPasteValues
Range("F8").Copy Worksheets("Pack").Range("AB2")
.PasteSpecial xlPasteValues
Range("H8").Copy Worksheets("Pack").Range("AC2")
.PasteSpecial xlPasteValues
Range("B9").Copy Worksheets("Pack").Range("AD2")
.PasteSpecial xlPasteValues
Range("D9").Copy Worksheets("Pack").Range("AE2")
.PasteSpecial xlPasteValues
Range("F9").Copy Worksheets("Pack").Range("AF2")
.PasteSpecial xlPasteValues
Range("H9").Copy Worksheets("Pack").Range("AG2")
.PasteSpecial xlPasteValues
Range("B10").Copy Worksheets("Pack").Range("AH2")
.PasteSpecial xlPasteValues
Range("D10").Copy Worksheets("Pack").Range("AI2")
.PasteSpecial xlPasteValues
Range("F10").Copy Worksheets("Pack").Range("AJ2")
.PasteSpecial xlPasteValues
Range("H10").Copy Worksheets("Pack").Range("AK2")
.PasteSpecial xlPasteValues
Range("B11").Copy Worksheets("Pack").Range("AL2")
.PasteSpecial xlPasteValues
Range("D11").Copy Worksheets("Pack").Range("AM2")
.PasteSpecial xlPasteValues
Range("F11").Copy Worksheets("Pack").Range("AN2")
.PasteSpecial xlPasteValues
Range("H11").Copy Worksheets("Pack").Range("AO2")
.PasteSpecial xlPasteValues
Range("B12").Copy Worksheets("Pack").Range("AP2")
.PasteSpecial xlPasteValues
Range("D12").Copy Worksheets("Pack").Range("AQ2")
.PasteSpecial xlPasteValues
Range("F12").Copy Worksheets("Pack").Range("AR2")
.PasteSpecial xlPasteValues
Range("H12").Copy Worksheets("Pack").Range("AS2")
.PasteSpecial xlPasteValues
Range("B13").Copy Worksheets("Pack").Range("AT2")
.PasteSpecial xlPasteValues
Range("D13").Copy Worksheets("Pack").Range("AU2")
.PasteSpecial xlPasteValues
Range("F13").Copy Worksheets("Pack").Range("AV2")
.PasteSpecial xlPasteValues
Range("H13").Copy Worksheets("Pack").Range("AW2")
.PasteSpecial xlPasteValues
Range("B14").Copy Worksheets("Pack").Range("AX2")
.PasteSpecial xlPasteValues
Range("D14").Copy Worksheets("Pack").Range("AY2")
.PasteSpecial xlPasteValues
Range("F14").Copy Worksheets("Pack").Range("AZ2")
.PasteSpecial xlPasteValues
Range("H14").Copy Worksheets("Pack").Range("BA2")
.PasteSpecial xlPasteValues
Range("B15").Copy Worksheets("Pack").Range("BB2")
.PasteSpecial xlPasteValues
Range("D15").Copy Worksheets("Pack").Range("BC2")
.PasteSpecial xlPasteValues
Range("F15").Copy Worksheets("Pack").Range("BD2")
.PasteSpecial xlPasteValues
Range("H15").Copy Worksheets("Pack").Range("BE2")
.PasteSpecial xlPasteValues
Range("B16").Copy Worksheets("Pack").Range("BF2")
.PasteSpecial xlPasteValues
Range("D16").Copy Worksheets("Pack").Range("BG2")
.PasteSpecial xlPasteValues
Range("F16").Copy Worksheets("Pack").Range("BH2")
.PasteSpecial xlPasteValues
Range("H16").Copy Worksheets("Pack").Range("BI2")
.PasteSpecial xlPasteValues

End Sub

但是，当尝试运行代码时，我收到一条错误消息“编译错误：无效或不合格的引用”，它指向宏的第一行，例如https://puu.sh/GHfuN/45afb283fd.png

我觉得有一个简单的解决方案，但我完全没有看到它。

标签： excelvba

解决方案

重构

编码

Option Explicit

Sub BuilderUpdate()

    Dim Target As Variant
    Target = Array("B2", "C2", "D2", "E2", "F2", "G2", "H2", "I2", "J2", "K2", _
                   "L2", "M2", "N2", "O2", "P2", "Q2", "R2", "S2", "T2", "U2", _
                   "V2", "W2", "X2", "Y2", "Z2", "AA2", "AB2", "AC2", "AD2", _
                   "AE2", "AF2", "AG2", "AH2", "AI2", "AJ2", "AK2", "AL2", _
                   "AM2", "AN2", "AO2", "AP2", "AQ2", "AR2", "AS2", "AT2", _
                   "AU2", "AV2", "AW2", "AX2", "AY2", "AZ2", "BA2", "BB2", _
                   "BC2", "BD2", "BE2", "BF2", "BG2", "BH2", "BI2")
    Dim Source As Variant
    Source = Array("B2", "D2", "F2", "H2", "B3", "D3", "F3", "H3", "B4", "D4", _
                   "F4", "H4", "B5", "D5", "F5", "H5", "B6", "D6", "F6", "H6", _
                   "B7", "D7", "F7", "H7", "B8", "D8", "F8", "H8", "B9", "D9", _
                   "F9", "H9", "B10", "D10", "F10", "H10", "B11", "D11", _
                   "F11", "H11", "B12", "D12", "F12", "H12", "B13", "D13", _
                   "F13", "H13", "B14", "D14", "F14", "H14", "B15", "D15", _
                   "F15", "H15", "B16", "D16", "F16", "H16")

    Dim ws As Worksheet
    Set ws = Worksheets("Pack")
    
    Dim n As Long
    For n = LBound(Source) To UBound(Source)
        ws.Range(Target(n)).Value = Range(Source(n)).Value
    Next n
    
    MsgBox "Data copied.", vbInformation, "Success"
    
End Sub

  • 在 Excel 中，您可以在两列中记下单元格地址，然后使用公式在工作表引用下方获取以下字符串：

    ="    ws.Range("""&A1&""").Value = Range("""&B1&""").Value"

快速修复

Sub BuilderUpdateQF()
    
    Dim ws As Worksheet
    Set ws = Worksheets("Pack")

    ws.Range("B2").Value = Range("B2").Value
    ws.Range("C2").Value = Range("D2").Value
    ws.Range("D2").Value = Range("F2").Value
    ws.Range("E2").Value = Range("H2").Value
    ws.Range("F2").Value = Range("B3").Value
    ws.Range("G2").Value = Range("D3").Value
    ws.Range("H2").Value = Range("F3").Value
    ws.Range("I2").Value = Range("H3").Value
    ws.Range("J2").Value = Range("B4").Value
    ws.Range("K2").Value = Range("D4").Value
    ws.Range("L2").Value = Range("F4").Value
    ws.Range("M2").Value = Range("H4").Value
    ws.Range("N2").Value = Range("B5").Value
    ws.Range("O2").Value = Range("D5").Value
    ws.Range("P2").Value = Range("F5").Value
    ws.Range("Q2").Value = Range("H5").Value
    ws.Range("R2").Value = Range("B6").Value
    ws.Range("S2").Value = Range("D6").Value
    ws.Range("T2").Value = Range("F6").Value
    ws.Range("U2").Value = Range("H6").Value
    ws.Range("V2").Value = Range("B7").Value
    ws.Range("W2").Value = Range("D7").Value
    ws.Range("X2").Value = Range("F7").Value
    ws.Range("Y2").Value = Range("H7").Value
    ws.Range("Z2").Value = Range("B8").Value
    ws.Range("AA2").Value = Range("D8").Value
    ws.Range("AB2").Value = Range("F8").Value
    ws.Range("AC2").Value = Range("H8").Value
    ws.Range("AD2").Value = Range("B9").Value
    ws.Range("AE2").Value = Range("D9").Value
    ws.Range("AF2").Value = Range("F9").Value
    ws.Range("AG2").Value = Range("H9").Value
    ws.Range("AH2").Value = Range("B10").Value
    ws.Range("AI2").Value = Range("D10").Value
    ws.Range("AJ2").Value = Range("F10").Value
    ws.Range("AK2").Value = Range("H10").Value
    ws.Range("AL2").Value = Range("B11").Value
    ws.Range("AM2").Value = Range("D11").Value
    ws.Range("AN2").Value = Range("F11").Value
    ws.Range("AO2").Value = Range("H11").Value
    ws.Range("AP2").Value = Range("B12").Value
    ws.Range("AQ2").Value = Range("D12").Value
    ws.Range("AR2").Value = Range("F12").Value
    ws.Range("AS2").Value = Range("H12").Value
    ws.Range("AT2").Value = Range("B13").Value
    ws.Range("AU2").Value = Range("D13").Value
    ws.Range("AV2").Value = Range("F13").Value
    ws.Range("AW2").Value = Range("H13").Value
    ws.Range("AX2").Value = Range("B14").Value
    ws.Range("AY2").Value = Range("D14").Value
    ws.Range("AZ2").Value = Range("F14").Value
    ws.Range("BA2").Value = Range("H14").Value
    ws.Range("BB2").Value = Range("B15").Value
    ws.Range("BC2").Value = Range("D15").Value
    ws.Range("BD2").Value = Range("F15").Value
    ws.Range("BE2").Value = Range("H15").Value
    ws.Range("BF2").Value = Range("B16").Value
    ws.Range("BG2").Value = Range("D16").Value
    ws.Range("BH2").Value = Range("F16").Value
    ws.Range("BI2").Value = Range("H16").Value

End Sub

推荐阅读