使用VBA将索引公式与单元格地址匹配时出错

Question

excel一直扔我：

运行时错误1004：应用程序定义或对象定义的错误

每当我尝试运行以下代码时。我不确定我在做什么错（真的很感谢任何建议）。

这是我定义skumaestro范围的方式的问题吗？

谢谢

Sub getskus ()
lastskurow = .Cells(.Rows.count, "M").End(xlUp).Row
lasthandlerow = .Cells(.Rows.count, "B").End(xlUp).Row

With ThisWorkbook.Worksheets(2)
    Dim skumaestro As Range
    Set skumaestro = Range(Cells(lasthandlerow, 13), Cells(lastskurow, 13))
End With
For start = 1 To count
     Range("C" & Rows.count).End(xlUp).Offset(1).Select
     ActiveCell.FormulaR1C1 = "=INDEX(" & barcodes.Address(True, True, 
      xlA1, True) & ",MATCH(" & skumaestro(1).Address(False, True, xlA1, 
      False) & "," & skucodes.Address(True, True, xlA1, True) & ",0))"
     ActiveCell.Value2 = ActiveCell.Value2 
Next
End Sub

Answer 1

您无法正确使用with ... end block。注意允许.range和.cell继承父工作表的.。

With ThisWorkbook.Worksheets(2)
    Dim skumaestro As Range
    Set skumaestro = .Range(.Cells(lasthandlerow, 13), .Cells(lastskurow, 13))
End With

您的公式使用XLA1地址尝试构建一个Formular1c1公式。没有定义的父工作表。条形码和 skucodes 不确定。您没有使用 start 来循环浏览任何东西。

我认为您想要类似的东西

For start = 1 To count
     with Range("C" & Rows.count).End(xlUp).Offset(1)   'what parent worksheet?
         .FormulaR1C1 = "=INDEX(" & barcodes.Address(True, True, 
      xlR1C1, True) & ",MATCH(" & skumaestro(start).Address(False, True, xlR1C1, 
      False) & "," & skucodes.Address(True, True, xlR1C1, True) & ",0))"
         .Value = .Value2 
    end with
Next start

您不使用公式中的 skumaestro 的外部地址，因此可以合理地假设该公式是针对上面使用的相同工作表的。

Dim skumaestro As Range, addr1 as string, addr2 as string
With ThisWorkbook.Worksheets(2)
    Set skumaestro = .Range(.Cells(lasthandlerow, 13), .Cells(lastskurow, 13))
    addr1 = barcodes.Address(True, True, xlR1C1, external:=True)
    addr2 = skucodes.Address(True, True, xlR1C1, external:=True)
     with .Range("C" & Rows.count).End(xlUp).Offset(1).resize(count, 1)
         .FormulaR1C1 = "=INDEX(" & addr1 & ",MATCH(" & skumaestro(1).Address(False, True, xlR1C1, False) & "," & addr2 & ",0))"
         .Value = .Value2 
    end with
End With