excel - 为什么当我使用下面的excel vba代码时会出现运行时错误1004而没有if？

Question

Sub Result()

barang = ActiveSheet.Columns(5)

If InStr("barang", "*SF*") <> 0 Then _
   ActiveCell.FormulaR1C1 = _
        "=IF(RC[-2]<25,IF(RC[-1]>=VLOOKUP(RC[-3],'Price List SF'!R4C1:R27C7,6,FALSE),1%,0.5%),IF(Sheet1!RC[-2]<55,IF(Sheet1!RC[-1]>=VLOOKUP(Sheet1!RC[-3],'Price List SF'!R4C1:R27C7,4,FALSE),1%,0.5%),IF(Sheet1!RC[-2]>=60,IF(Sheet1!RC[-1]>=VLOOKUP(Sheet1!RC[-3],'Price List SF'!R4C1:R27C7,2,FALSE),1%,0.5%),"""")))"

ElseIf InStr("barang", "BS" & "FS") <> 0 Then _
       ActiveCell.FormulaR1C1 = _
         "=IF(RC[-2]<5,IF(RC[-1]>=VLOOKUP(RC[-3],'Harga Bubble & Foam'!R5C1:R12C7,6,FALSE),1%,0.5%),IF(Sheet1!RC[-2]<10,IF(Sheet1!RC[-1]>=VLOOKUP(Sheet1!RC[-3],'Harga Bubble & Foam'!R5C1:R12C7,4,FALSE),1%,0.5%),IF(Sheet1!RC[-2]>=10,IF(Sheet1!RC[-1]>=VLOOKUP(Sheet1!RC[-3],'Harga Bubble & Foam'!R5C1:R12C7,2,FALSE),1%,0.5%),"""")))"


ElseIf InStr("barang", "SB") <> 0 Then _
 ActiveCell.FormulaR1C1 = _
        "=IF(RC[-2]<30,IF(RC[-1]>=VLOOKUP(RC[-3],'Harga Strapping Band RajaPack'!R4C1:R27C7,6,FALSE),1%,0.5%),IF(Sheet1!RC[-2]<100,IF(Sheet1!RC[-1]>=VLOOKUP(Sheet1!RC[-3],'Harga Strapping Band RajaPack'!R4C1:R27C7,4,FALSE),1%,0.5%),IF(Sheet1!RC[-2]>=100,IF(Sheet1!RC[-1]>=VLOOKUP(Sheet1!RC[-3],'Harga Strapping Band RajaPack'!R4C1:R27C7,2,FALSE),1%,0.5%),"""")))"


ElseIf InStr("barang", "MT" & "DT" & "KT" & "CT") <> 0 Then ActiveCell.FormulaR1C1 = _
        "=IF(VLOOKUP(RC[-3],'Price List MT, DT, KT, CT'!R4C1:R28C8,2,FALSE)=72,IF(Sheet1!RC[-2]<360,IF(Sheet1!RC[-1]>=VLOOKUP(Sheet1!RC[-3],'Price List MT, DT, KT, CT'!R4C1:R28C8,7,FALSE),1%,0.5%),IF(Sheet1!RC[-2]<720,IF(Sheet1!RC[-1]>=VLOOKUP(Sheet1!RC[-3],'Price List MT, DT, KT, CT'!R4C1:R28C8,5,FALSE),1%,0.5%),IF(Sheet1!RC[-2]>=720,IF(Sheet1!RC[-1]>=VLOOKUP(Sheet1!RC[-3],'Price L" & _
        "ist MT, DT, KT, CT'!R4C1:R28C8,3,FALSE),1%,0.5%),""""))),IF(VLOOKUP(Sheet1!RC[-3],'Price List MT, DT, KT, CT'!R4C1:R28C8,2,FALSE)=144,IF(Sheet1!RC[-2]<720,IF(Sheet1!RC[-1]>=VLOOKUP(Sheet1!RC[-3],'Price List MT, DT, KT, CT'!R4C1:R28C8,7,FALSE),1%,0.5%),IF(Sheet1!RC[-2]<1440,IF(Sheet1!RC[-1]>=VLOOKUP(Sheet1!RC[-3],'Price List MT, DT, KT, CT'!R4C1:R28C8,5,FALSE),1%,0.5%);IF(She" & _
        "et1!RC[-2]>=1440,IF(Sheet1!RC[-1]>=VLOOKUP(Sheet1!RC[-3],'Price List MT, DT, KT, CT'!R4C1:R28C8,3,FALSE),1%,0.5%)))),IF(VLOOKUP(Sheet1!RC[-3],'Price List MT, DT, KT, CT'!R4C1:R28C8,2,FALSE)=288,IF(Sheet1!RC[-2]<1440,IF(Sheet1!RC[-1]>=VLOOKUP(Sheet1!RC[-3],'Price List MT, DT, KT, CT'!R4C1:R28C8,7,FALSE),1%,0.5%),IF(Sheet1!RC[-2]<2880,IF(Sheet1!RC[-1]>=VLOOKUP(Sheet1!RC[-3],'P" & _
        "rice List MT, DT, KT, CT'!R4C1:R28C8,5,FALSE),1%,0.5%),IF(Sheet1!RC[-2]>=2880,IF(Sheet1!RC[-1]>=VLOOKUP(Sheet1!RC[-3],'Price List MT, DT, KT, CT'!R4C1:R28C8,3,FALSE),1%,0.5%)))),"""")))"


Else


       ActiveCell.FormulaR1C1 = _
        "=IF(VLOOKUP(RC[-3],'Price List OPP Tapes'!R4C1:R42C8,2,FALSE)=12,IF(Sheet1!RC[-2]<60,IF(Sheet1!RC[-1]>=VLOOKUP(Sheet1!RC[-3],'Price List OPP Tapes'!R4C1:R42C8,7,FALSE),1%,0.5%),IF(Sheet1!RC[-2]<120,IF(Sheet1!RC[-1]>=VLOOKUP(Sheet1!RC[-3],'Price List OPP Tapes'!R4C1:R42C8,5,FALSE),1%,0.5%),IF(Sheet1!RC[-2]>=120,IF(Sheet1!RC[-1]>=VLOOKUP(Sheet1!RC[-3],'Price List OPP Tapes'! " & _
        "R4C1:R42C8,3,FALSE),1%,0.5%),""""))),IF(VLOOKUP(Sheet1!RC[-3],'Price List OPP Tapes'!R4C1:R42C8,2,FALSE)=48,IF(Sheet1!RC[-2]<240,IF(Sheet1!RC[-1]>=VLOOKUP(Sheet1!RC[-3],'Price List OPP Tapes'!R4C1:R42C8,7,FALSE),1%,0.5%),IF(Sheet1!RC[-2]<480,IF(Sheet1!RC[-1]>=VLOOKUP(Sheet1!RC[-3],'Price List OPP Tapes'!R4C1:R42C8,5,FALSE),1%,0.5%),IF(Sheet1!RC[-2]>=480,IF(Sheet1!RC[-1]>=" & _
        "VLOOKUP(Sheet1!RC[-3],'Price List OPP Tapes'!R4C1:R42C8,3,FALSE),1%,0.5%),""""))),IF(VLOOKUP(Sheet1!RC[-3],'Price List OPP Tapes'!R4C1:R42C8,2,FALSE)=72,IF(Sheet1!RC[-2]<360,IF(Sheet1!RC[-1]>=VLOOKUP(Sheet1!RC[-3],'Price List OPP Tapes'!R4C1:R42C8,7,FALSE),1%,0.5%),IF(Sheet1!RC[-2]<720,IF(Sheet1!RC[-1]>=VLOOKUP(Sheet1!RC[-3],'Price List OPP Tapes'!R4C1:R42C8,5,FALSE),1%" & _
        "(Sheet1!RC[-2]>=720,IF(Sheet1!RC[-1]>=VLOOKUP(Sheet1!RC[-3],'Price List OPP Tapes'!R4C1:R42C8,3,FALSE),1%,0.5%),""""))),IF(VLOOKUP(Sheet1!RC[-3],'Price List OPP Tapes'!R4C1:R42C8,2,FALSE)=144,IF(Sheet1!RC[-2]<720,IF(Sheet1!RC[-1]>=VLOOKUP(Sheet1!RC[-3],'Price List OPP Tapes'!R4C1:R42C8,7,FALSE),1%,0.5%),IF(Sheet1!RC[-2]<1440,IF(Sheet1!RC[-1]>=VLOOKUP(Sheet1!RC[-3]," & _
        "'Price List OPP Tapes'!R4C1:R42C8,5,FALSE),1%,0.5%),IF(Sheet1!RC[-2]>=1440,IF(Sheet1!RC[-1]>=VLOOKUP(Sheet1!RC[-3],'Price List OPP Tapes'!R4C1:R42C8,3,FALSE),1%,0.5%),""""))),IF(VLOOKUP(Sheet1!RC[-3],'Price List OPP Tapes'!R4C1:R42C8,2,FALSE)=288,IF(Sheet1!RC[-2]<1440,IF(Sheet1!RC[-1]>=VLOOKUP(Sheet1!RC[-3],'Price List OPP Tapes'!R4C1:R42C8,7,FALSE),1%,0.5%),IF(Sheet1!RC[-2]<" & _
        "2880;IF(Sheet1!RC[-1]>=VLOOKUP(Sheet1!RC[-3],'Price List OPP Tapes'!R4C1:R42C8,5,FALSE),1%,0.5%),IF(Sheet1!RC[-2]>=2880,IF(Sheet1!RC[-1]>=VLOOKUP(Sheet1!RC[-3],'Price List OPP Tapes'!R4C1:R42C8,3,FALSE),1%,0.5%),""""))))))))"
End If


End Sub

标签： excelvba

Answer 1

对，正如评论中提到的，如果你Debug的代码你会看到同样的错误Else Without If。这是因为您使用_不正确。同样，使用下划线实际上允许您编写多行代码，实际上是一行。这用于“拆分”非常长的单行。我在下面更新了您的代码，并进行了一些小的调整。我还没有测试它，但它正在编译。

Option Explicit

Sub Result()

    Dim barang As Range

    Set barang = ActiveSheet.Columns(5)

    If InStr("barang", "*SF*") <> 0 Then
       ActiveCell.FormulaR1C1 = _
            "=IF(RC[-2]<25,IF(RC[-1]>=VLOOKUP(RC[-3],'Price List SF'!R4C1:R27C7,6,FALSE),1%,0.5%),IF(Sheet1!RC[-2]<55,IF(Sheet1!RC[-1]>=VLOOKUP(Sheet1!RC[-3],'Price List SF'!R4C1:R27C7,4,FALSE),1%,0.5%),IF(Sheet1!RC[-2]>=60,IF(Sheet1!RC[-1]>=VLOOKUP(Sheet1!RC[-3],'Price List SF'!R4C1:R27C7,2,FALSE),1%,0.5%),"""")))"

    ElseIf InStr("barang", "BS" & "FS") <> 0 Then
           ActiveCell.FormulaR1C1 = _
             "=IF(RC[-2]<5,IF(RC[-1]>=VLOOKUP(RC[-3],'Harga Bubble & Foam'!R5C1:R12C7,6,FALSE),1%,0.5%),IF(Sheet1!RC[-2]<10,IF(Sheet1!RC[-1]>=VLOOKUP(Sheet1!RC[-3],'Harga Bubble & Foam'!R5C1:R12C7,4,FALSE),1%,0.5%),IF(Sheet1!RC[-2]>=10,IF(Sheet1!RC[-1]>=VLOOKUP(Sheet1!RC[-3],'Harga Bubble & Foam'!R5C1:R12C7,2,FALSE),1%,0.5%),"""")))"


    ElseIf InStr("barang", "SB") <> 0 Then
     ActiveCell.FormulaR1C1 = _
            "=IF(RC[-2]<30,IF(RC[-1]>=VLOOKUP(RC[-3],'Harga Strapping Band RajaPack'!R4C1:R27C7,6,FALSE),1%,0.5%),IF(Sheet1!RC[-2]<100,IF(Sheet1!RC[-1]>=VLOOKUP(Sheet1!RC[-3],'Harga Strapping Band RajaPack'!R4C1:R27C7,4,FALSE),1%,0.5%),IF(Sheet1!RC[-2]>=100,IF(Sheet1!RC[-1]>=VLOOKUP(Sheet1!RC[-3],'Harga Strapping Band RajaPack'!R4C1:R27C7,2,FALSE),1%,0.5%),"""")))"


    ElseIf InStr("barang", "MT" & "DT" & "KT" & "CT") <> 0 Then
        ActiveCell.FormulaR1C1 = _
            "=IF(VLOOKUP(RC[-3],'Price List MT, DT, KT, CT'!R4C1:R28C8,2,FALSE)=72,IF(Sheet1!RC[-2]<360,IF(Sheet1!RC[-1]>=VLOOKUP(Sheet1!RC[-3],'Price List MT, DT, KT, CT'!R4C1:R28C8,7,FALSE),1%,0.5%),IF(Sheet1!RC[-2]<720,IF(Sheet1!RC[-1]>=VLOOKUP(Sheet1!RC[-3],'Price List MT, DT, KT, CT'!R4C1:R28C8,5,FALSE),1%,0.5%),IF(Sheet1!RC[-2]>=720,IF(Sheet1!RC[-1]>=VLOOKUP(Sheet1!RC[-3],'Price L" & _
            "ist MT, DT, KT, CT'!R4C1:R28C8,3,FALSE),1%,0.5%),""""))),IF(VLOOKUP(Sheet1!RC[-3],'Price List MT, DT, KT, CT'!R4C1:R28C8,2,FALSE)=144,IF(Sheet1!RC[-2]<720,IF(Sheet1!RC[-1]>=VLOOKUP(Sheet1!RC[-3],'Price List MT, DT, KT, CT'!R4C1:R28C8,7,FALSE),1%,0.5%),IF(Sheet1!RC[-2]<1440,IF(Sheet1!RC[-1]>=VLOOKUP(Sheet1!RC[-3],'Price List MT, DT, KT, CT'!R4C1:R28C8,5,FALSE),1%,0.5%);IF(She" & _
            "et1!RC[-2]>=1440,IF(Sheet1!RC[-1]>=VLOOKUP(Sheet1!RC[-3],'Price List MT, DT, KT, CT'!R4C1:R28C8,3,FALSE),1%,0.5%)))),IF(VLOOKUP(Sheet1!RC[-3],'Price List MT, DT, KT, CT'!R4C1:R28C8,2,FALSE)=288,IF(Sheet1!RC[-2]<1440,IF(Sheet1!RC[-1]>=VLOOKUP(Sheet1!RC[-3],'Price List MT, DT, KT, CT'!R4C1:R28C8,7,FALSE),1%,0.5%),IF(Sheet1!RC[-2]<2880,IF(Sheet1!RC[-1]>=VLOOKUP(Sheet1!RC[-3],'P" & _
            "rice List MT, DT, KT, CT'!R4C1:R28C8,5,FALSE),1%,0.5%),IF(Sheet1!RC[-2]>=2880,IF(Sheet1!RC[-1]>=VLOOKUP(Sheet1!RC[-3],'Price List MT, DT, KT, CT'!R4C1:R28C8,3,FALSE),1%,0.5%)))),"""")))"


    Else
           ActiveCell.FormulaR1C1 = _
            "=IF(VLOOKUP(RC[-3],'Price List OPP Tapes'!R4C1:R42C8,2,FALSE)=12,IF(Sheet1!RC[-2]<60,IF(Sheet1!RC[-1]>=VLOOKUP(Sheet1!RC[-3],'Price List OPP Tapes'!R4C1:R42C8,7,FALSE),1%,0.5%),IF(Sheet1!RC[-2]<120,IF(Sheet1!RC[-1]>=VLOOKUP(Sheet1!RC[-3],'Price List OPP Tapes'!R4C1:R42C8,5,FALSE),1%,0.5%),IF(Sheet1!RC[-2]>=120,IF(Sheet1!RC[-1]>=VLOOKUP(Sheet1!RC[-3],'Price List OPP Tapes'! " & _
            "R4C1:R42C8,3,FALSE),1%,0.5%),""""))),IF(VLOOKUP(Sheet1!RC[-3],'Price List OPP Tapes'!R4C1:R42C8,2,FALSE)=48,IF(Sheet1!RC[-2]<240,IF(Sheet1!RC[-1]>=VLOOKUP(Sheet1!RC[-3],'Price List OPP Tapes'!R4C1:R42C8,7,FALSE),1%,0.5%),IF(Sheet1!RC[-2]<480,IF(Sheet1!RC[-1]>=VLOOKUP(Sheet1!RC[-3],'Price List OPP Tapes'!R4C1:R42C8,5,FALSE),1%,0.5%),IF(Sheet1!RC[-2]>=480,IF(Sheet1!RC[-1]>=" & _
            "VLOOKUP(Sheet1!RC[-3],'Price List OPP Tapes'!R4C1:R42C8,3,FALSE),1%,0.5%),""""))),IF(VLOOKUP(Sheet1!RC[-3],'Price List OPP Tapes'!R4C1:R42C8,2,FALSE)=72,IF(Sheet1!RC[-2]<360,IF(Sheet1!RC[-1]>=VLOOKUP(Sheet1!RC[-3],'Price List OPP Tapes'!R4C1:R42C8,7,FALSE),1%,0.5%),IF(Sheet1!RC[-2]<720,IF(Sheet1!RC[-1]>=VLOOKUP(Sheet1!RC[-3],'Price List OPP Tapes'!R4C1:R42C8,5,FALSE),1%" & _
            "(Sheet1!RC[-2]>=720,IF(Sheet1!RC[-1]>=VLOOKUP(Sheet1!RC[-3],'Price List OPP Tapes'!R4C1:R42C8,3,FALSE),1%,0.5%),""""))),IF(VLOOKUP(Sheet1!RC[-3],'Price List OPP Tapes'!R4C1:R42C8,2,FALSE)=144,IF(Sheet1!RC[-2]<720,IF(Sheet1!RC[-1]>=VLOOKUP(Sheet1!RC[-3],'Price List OPP Tapes'!R4C1:R42C8,7,FALSE),1%,0.5%),IF(Sheet1!RC[-2]<1440,IF(Sheet1!RC[-1]>=VLOOKUP(Sheet1!RC[-3]," & _
            "'Price List OPP Tapes'!R4C1:R42C8,5,FALSE),1%,0.5%),IF(Sheet1!RC[-2]>=1440,IF(Sheet1!RC[-1]>=VLOOKUP(Sheet1!RC[-3],'Price List OPP Tapes'!R4C1:R42C8,3,FALSE),1%,0.5%),""""))),IF(VLOOKUP(Sheet1!RC[-3],'Price List OPP Tapes'!R4C1:R42C8,2,FALSE)=288,IF(Sheet1!RC[-2]<1440,IF(Sheet1!RC[-1]>=VLOOKUP(Sheet1!RC[-3],'Price List OPP Tapes'!R4C1:R42C8,7,FALSE),1%,0.5%),IF(Sheet1!RC[-2]<" & _
            "2880;IF(Sheet1!RC[-1]>=VLOOKUP(Sheet1!RC[-3],'Price List OPP Tapes'!R4C1:R42C8,5,FALSE),1%,0.5%),IF(Sheet1!RC[-2]>=2880,IF(Sheet1!RC[-1]>=VLOOKUP(Sheet1!RC[-3],'Price List OPP Tapes'!R4C1:R42C8,3,FALSE),1%,0.5%),""""))))))))"
    End If


End Sub

---

编辑

是的，所以您的代码中缺少一个For循环，所以我认为您正在寻找的内容如下。我还没有测试过，但我相信你会明白逻辑。

此外，明确定义工作表对象是一种很好的编码习惯，因为使用ActiveSheet容易出现用户错误。

Option Explicit

Sub Result()

    Dim ws As Worksheet
    Dim i As Long, lRow As Long

    Set ws = ThisWorkbook.Sheets("Sheet1")

    lRow = ws.Columns(5).Rows.Count


    For i = 1 To lRow

        With ws.Cells(i, 5)

            If InStr(1, .Value, "*SF*") <> 0 Then
               .FormulaR1C1 = _
                "=IF(RC[-2]<25,IF(RC[-1]>=VLOOKUP(RC[-3],'Price List SF'!R4C1:R27C7,6,FALSE),1%,0.5%),IF(Sheet1!RC[-2]<55,IF(Sheet1!RC[-1]>=VLOOKUP(Sheet1!RC[-3],'Price List SF'!R4C1:R27C7,4,FALSE),1%,0.5%),IF(Sheet1!RC[-2]>=60,IF(Sheet1!RC[-1]>=VLOOKUP(Sheet1!RC[-3],'Price List SF'!R4C1:R27C7,2,FALSE),1%,0.5%),"""")))"

            ElseIf InStr(1, .Value, "BS") <> 0 Then
                .FormulaR1C1 = _
                "=IF(RC[-2]<5,IF(RC[-1]>=VLOOKUP(RC[-3],'Harga Bubble & Foam'!R5C1:R12C7,6,FALSE),1%,0.5%),IF(Sheet1!RC[-2]<10,IF(Sheet1!RC[-1]>=VLOOKUP(Sheet1!RC[-3],'Harga Bubble & Foam'!R5C1:R12C7,4,FALSE),1%,0.5%),IF(Sheet1!RC[-2]>=10,IF(Sheet1!RC[-1]>=VLOOKUP(Sheet1!RC[-3],'Harga Bubble & Foam'!R5C1:R12C7,2,FALSE),1%,0.5%),"""")))"


            ElseIf InStr(1, .Value, "SB") <> 0 Then
                .FormulaR1C1 = _
                "=IF(RC[-2]<30,IF(RC[-1]>=VLOOKUP(RC[-3],'Harga Strapping Band RajaPack'!R4C1:R27C7,6,FALSE),1%,0.5%),IF(Sheet1!RC[-2]<100,IF(Sheet1!RC[-1]>=VLOOKUP(Sheet1!RC[-3],'Harga Strapping Band RajaPack'!R4C1:R27C7,4,FALSE),1%,0.5%),IF(Sheet1!RC[-2]>=100,IF(Sheet1!RC[-1]>=VLOOKUP(Sheet1!RC[-3],'Harga Strapping Band RajaPack'!R4C1:R27C7,2,FALSE),1%,0.5%),"""")))"


            ElseIf InStr(1, .Value, "MT") <> 0 Then
                .FormulaR1C1 = _
                "=IF(VLOOKUP(RC[-3],'Price List MT, DT, KT, CT'!R4C1:R28C8,2,FALSE)=72,IF(Sheet1!RC[-2]<360,IF(Sheet1!RC[-1]>=VLOOKUP(Sheet1!RC[-3],'Price List MT, DT, KT, CT'!R4C1:R28C8,7,FALSE),1%,0.5%),IF(Sheet1!RC[-2]<720,IF(Sheet1!RC[-1]>=VLOOKUP(Sheet1!RC[-3],'Price List MT, DT, KT, CT'!R4C1:R28C8,5,FALSE),1%,0.5%),IF(Sheet1!RC[-2]>=720,IF(Sheet1!RC[-1]>=VLOOKUP(Sheet1!RC[-3],'Price L" & _
                "ist MT, DT, KT, CT'!R4C1:R28C8,3,FALSE),1%,0.5%),""""))),IF(VLOOKUP(Sheet1!RC[-3],'Price List MT, DT, KT, CT'!R4C1:R28C8,2,FALSE)=144,IF(Sheet1!RC[-2]<720,IF(Sheet1!RC[-1]>=VLOOKUP(Sheet1!RC[-3],'Price List MT, DT, KT, CT'!R4C1:R28C8,7,FALSE),1%,0.5%),IF(Sheet1!RC[-2]<1440,IF(Sheet1!RC[-1]>=VLOOKUP(Sheet1!RC[-3],'Price List MT, DT, KT, CT'!R4C1:R28C8,5,FALSE),1%,0.5%);IF(She" & _
                "et1!RC[-2]>=1440,IF(Sheet1!RC[-1]>=VLOOKUP(Sheet1!RC[-3],'Price List MT, DT, KT, CT'!R4C1:R28C8,3,FALSE),1%,0.5%)))),IF(VLOOKUP(Sheet1!RC[-3],'Price List MT, DT, KT, CT'!R4C1:R28C8,2,FALSE)=288,IF(Sheet1!RC[-2]<1440,IF(Sheet1!RC[-1]>=VLOOKUP(Sheet1!RC[-3],'Price List MT, DT, KT, CT'!R4C1:R28C8,7,FALSE),1%,0.5%),IF(Sheet1!RC[-2]<2880,IF(Sheet1!RC[-1]>=VLOOKUP(Sheet1!RC[-3],'P" & _
                "rice List MT, DT, KT, CT'!R4C1:R28C8,5,FALSE),1%,0.5%),IF(Sheet1!RC[-2]>=2880,IF(Sheet1!RC[-1]>=VLOOKUP(Sheet1!RC[-3],'Price List MT, DT, KT, CT'!R4C1:R28C8,3,FALSE),1%,0.5%)))),"""")))"

            Else
                .FormulaR1C1 = _
                 "=IF(VLOOKUP(RC[-3],'Price List OPP Tapes'!R4C1:R42C8,2,FALSE)=12,IF(Sheet1!RC[-2]<60,IF(Sheet1!RC[-1]>=VLOOKUP(Sheet1!RC[-3],'Price List OPP Tapes'!R4C1:R42C8,7,FALSE),1%,0.5%),IF(Sheet1!RC[-2]<120,IF(Sheet1!RC[-1]>=VLOOKUP(Sheet1!RC[-3],'Price List OPP Tapes'!R4C1:R42C8,5,FALSE),1%,0.5%),IF(Sheet1!RC[-2]>=120,IF(Sheet1!RC[-1]>=VLOOKUP(Sheet1!RC[-3],'Price List OPP Tapes'! " & _
                 "R4C1:R42C8,3,FALSE),1%,0.5%),""""))),IF(VLOOKUP(Sheet1!RC[-3],'Price List OPP Tapes'!R4C1:R42C8,2,FALSE)=48,IF(Sheet1!RC[-2]<240,IF(Sheet1!RC[-1]>=VLOOKUP(Sheet1!RC[-3],'Price List OPP Tapes'!R4C1:R42C8,7,FALSE),1%,0.5%),IF(Sheet1!RC[-2]<480,IF(Sheet1!RC[-1]>=VLOOKUP(Sheet1!RC[-3],'Price List OPP Tapes'!R4C1:R42C8,5,FALSE),1%,0.5%),IF(Sheet1!RC[-2]>=480,IF(Sheet1!RC[-1]>=" & _
                 "VLOOKUP(Sheet1!RC[-3],'Price List OPP Tapes'!R4C1:R42C8,3,FALSE),1%,0.5%),""""))),IF(VLOOKUP(Sheet1!RC[-3],'Price List OPP Tapes'!R4C1:R42C8,2,FALSE)=72,IF(Sheet1!RC[-2]<360,IF(Sheet1!RC[-1]>=VLOOKUP(Sheet1!RC[-3],'Price List OPP Tapes'!R4C1:R42C8,7,FALSE),1%,0.5%),IF(Sheet1!RC[-2]<720,IF(Sheet1!RC[-1]>=VLOOKUP(Sheet1!RC[-3],'Price List OPP Tapes'!R4C1:R42C8,5,FALSE),1%" & _
                 "(Sheet1!RC[-2]>=720,IF(Sheet1!RC[-1]>=VLOOKUP(Sheet1!RC[-3],'Price List OPP Tapes'!R4C1:R42C8,3,FALSE),1%,0.5%),""""))),IF(VLOOKUP(Sheet1!RC[-3],'Price List OPP Tapes'!R4C1:R42C8,2,FALSE)=144,IF(Sheet1!RC[-2]<720,IF(Sheet1!RC[-1]>=VLOOKUP(Sheet1!RC[-3],'Price List OPP Tapes'!R4C1:R42C8,7,FALSE),1%,0.5%),IF(Sheet1!RC[-2]<1440,IF(Sheet1!RC[-1]>=VLOOKUP(Sheet1!RC[-3]," & _
                 "'Price List OPP Tapes'!R4C1:R42C8,5,FALSE),1%,0.5%),IF(Sheet1!RC[-2]>=1440,IF(Sheet1!RC[-1]>=VLOOKUP(Sheet1!RC[-3],'Price List OPP Tapes'!R4C1:R42C8,3,FALSE),1%,0.5%),""""))),IF(VLOOKUP(Sheet1!RC[-3],'Price List OPP Tapes'!R4C1:R42C8,2,FALSE)=288,IF(Sheet1!RC[-2]<1440,IF(Sheet1!RC[-1]>=VLOOKUP(Sheet1!RC[-3],'Price List OPP Tapes'!R4C1:R42C8,7,FALSE),1%,0.5%),IF(Sheet1!RC[-2]<" & _
                 "2880;IF(Sheet1!RC[-1]>=VLOOKUP(Sheet1!RC[-3],'Price List OPP Tapes'!R4C1:R42C8,5,FALSE),1%,0.5%),IF(Sheet1!RC[-2]>=2880,IF(Sheet1!RC[-1]>=VLOOKUP(Sheet1!RC[-3],'Price List OPP Tapes'!R4C1:R42C8,3,FALSE),1%,0.5%),""""))))))))"
            End If

        End With

    Next i

End Sub