公式应用程序定义或对象定义的错误 [英] Formula Application Defined or Object Defined Error

问题描述

这是对先前为我回答的一个问题的跟进.如果我似乎对此提出了很多问题，我感到很抱歉，我对VBA还是陌生的.

this is a follow up to a question that was answered earlier for me. I apologize if it seems like I am asking a lot of questions on this, I am fairly new to VBA.

我有一行代码应该在一个单元格中放入一个公式.它给了我

I have line of code that is supposed to put a formula in a cell. It is giving me the

Application Defined or Object Defined Error message:

这是整个宏的代码，最后一行给出了错误.

Here is the code for the entire Macro with the last line giving the error.

Sub DBFtoAVI()
'
' DBFtoAVI1 Macro
' Updated by Scott Schmeling on 2/10/2014 based on a Macro recorded by Rebecca Davis
'

' Format As Adjusted

    Sheets("As Adjusted").Select
    Columns("K:K").Copy
    Columns("K:K").PasteSpecial Paste:=xlPasteValues
    Columns("M:S").EntireColumn.AutoFit
    Columns("Q:R").Delete Shift:=xlToLeft

' Create Names for Item, Treat, and Price

' Item

    Sheets("ITEM").Select
    Cells.Select
    With Selection
        .EntireColumn.AutoFit
        .Sort Key1:=Range("C2"), Order1:=xlAscending, Key2:=Range("B2") _
        , Order2:=xlAscending
    End With
    Columns("B:W").Select
    ActiveWorkbook.Names.Add Name:="itemlookup", RefersToR1C1:="=ITEM!C2:C23"

' Treat

    Sheets("TREAT").Select
    Cells.Select
     With Selection
        .EntireColumn.AutoFit
        .Sort Key1:=Range("C2"), Order1:=xlAscending, Key2:=Range("B2") _
        , Order2:=xlAscending
    End With
    Columns("B:L").Select
    ActiveWorkbook.Names.Add Name:="treatlookup", RefersToR1C1:="=TREAT!C2:C12"

' Price

    Sheets("PRICE").Select
    Cells.EntireColumn.AutoFit
    Columns("D:J").Select
    ActiveWorkbook.Names.Add Name:="pricechangelookup", RefersToR1C1:= _
        "=PRICE!C4:C10"


'    Sheets("ITEM").Select
'    Columns("C:C").Select
'    Selection.Find(What:="d", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
'        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
'        False, SearchFormat:=False).Activate


' Insert DBF to AVI Worksheet

    Sheets.Add(Before:=Sheets(5)).Name = "DBFtoAVI"
    Sheets("DBFtoAVI").Select
    ActiveCell.FormulaR1C1 = "='As Adjusted'!RC"
    Range("A1").Select
    Selection.AutoFill Destination:=Range("A1:M1"), Type:=xlFillDefault
    Range("A1:M1").Select
    Selection.AutoFill Destination:=Range("A1:M2000"), Type:=xlFillDefault
    Range("A1:M2000").Select

' Create Header and Columns A - E

    Columns("A:E").Select
    Selection.Insert Shift:=xlToRight
    Range("A1").FormulaR1C1 = "Type"
    Range("B1").FormulaR1C1 = "Action"
    Range("C1").FormulaR1C1 = "Left"
    Range("D1").FormulaR1C1 = "Right"
    Range("E1").FormulaR1C1 = "PriceDiff"
    Range("A4").FormulaR1C1 = "T"
    Range("B4").FormulaR1C1 = "=IF(RC[-1]='T',VLOOKUP(RC[7],treatlookup,11,FALSE),VLOOKUP(RC[7],itemlookup,22,FALSE))"

再次获得帮助，我们将不胜感激.

Again help is greatly appreciated.

推荐答案

更改

Range("B4").FormulaR1C1 = "=IF(RC[-1]='T',VLOOKUP(RC[7],treatlookup,11,FALSE),VLOOKUP(RC[7],itemlookup,22,FALSE))"

到

Range("B4").FormulaR1C1 = "=IF(RC[-1]=""T"",VLOOKUP(RC[7],treatlookup,11,FALSE),VLOOKUP(RC[7],itemlookup,22,FALSE))"

这篇关于公式应用程序定义或对象定义的错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！