CODEWITHSUNDEEP
vbaexcel
user2555565
user2555565

Reputation: 62

.Formula is producing runtime error 1004 - works for all other strings

Everything works except for the last .Formula for kcFormula. It returns a 1004 Runtime Application error. I am thinking maybe I declared the kcFormula as a string and it is too big or I have no idea what I am really doing :D

this is the problem statement:

Sheets("AML").Range(myRNG).Formula = kcFormula

Sub SharePoint_FormatADDcolS()
Dim myRNG As String
Dim lastROW As Integer
Dim matchFormula As String
Dim kcFormula As String
matchFormula = "=IF(ISNA(Table3[[#This Row],[Status]]=Table3[[#This Row],[Last Status]]),"
matchFormula = matchFormula & """ Status Changed"""
matchFormula = matchFormula & "," & "IF(Table3[[#This Row],[Status]]<>Table3[[#This Row],[Last Status]],"
matchFormula = matchFormula & """ Status Changed""" & "," & """Status Unchanged""" & "))"
kcFormula = "=IF(OR(Table3[[#This Row],[Status]]=" & """Rejected""" & "," & "Table3[[#This Row],[Status]] =" & """Completed""" & "," & "Table3[[#This Row],[Status]]=" & """Not Implemented""" & ")," & """ """ & ",INDEX('AMLLAST'!$1:$1048576,MATCH(Table3[[#This Row],[ID]],'AMLLAST'!$E:$E,0),11)"
lastROW = ActiveSheet.UsedRange.Rows.Count

    With Sheets("AML")
        .ListObjects(1).Name = "Table3"
    End With
Sheets("AML (2)").Name = "AMLLAST"

    With Sheets("AML")
                Range("J1").Select
                Range(Selection, Selection.End(xlDown)).Select
                Selection.Copy
                Range("J1").Select
                ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
                IconFileName:=False
            Sheets("AML").Range("C1").EntireColumn.Insert
            Sheets("AML").Range("C1").EntireColumn.Insert
            Sheets("AML").Range("C1") = "Last Status"
            Sheets("AML").Range("D1") = "Match"
            Sheets("AML").Range("K1").EntireColumn.Insert
            Sheets("AML").Range("K1") = "BITOOL - Rating"

        myRNG = "C2:" & "C" & lastROW
                Sheets("AML").Range(myRNG).NumberFormat = "General"
                Sheets("AML").Range(myRNG).Formula = "=INDEX('AMLLAST'!$1:$1048576,MATCH(Table3[[#This Row],[ID]],'AMLLAST'!$E:$E,0),2)"
        myRNG = "D2:" & "D" & lastROW
                Sheets("AML").Range(myRNG).NumberFormat = "General"
                Sheets("AML").Range(myRNG).Formula = matchFormula
        myRNG = "K2:" & "K" & lastROW
                Sheets("AML").Range(myRNG).NumberFormat = "General"
                Sheets("AML").Range(myRNG).Formula = kcFormula

    End With
End Sub

Upvotes: 0

Views: 175

Answers (2)

FreeMan
FreeMan

Reputation: 5687

You can improve the readability of that last statement by getting rid of all the concatenation:

kcFormula = "=IF(
               OR(Table3[[#This Row],[Status]] = ""Rejected"",
                  Table3[[#This Row],[Status]] = ""Completed"",
                  Table3[[#This Row],[Status]] = ""Not Implemented""
                 ),
              "" "",
              INDEX('AMLLAST'!$1:$1048576,
                    MATCH(Table3[[#This Row],[ID]],'AMLLAST'!$E:$E,0
                         )
                   ,11
                   )"
                 )  <---- this guy is missing! (put him inside the closing " mark

Also, when typing up those deeply embedded, complex IF statements, I usually paste them into Notepad and break them across multiple lines. When you do that, you'll notice that you are missing the final closing ), as OpiesDad pointed out.

Give him the credit for the solution, I just wanted to point out couple of tips for resolving the issue that wouldn't have fit in a comment.

Upvotes: 1

OpiesDad
OpiesDad

Reputation: 3435

You're missing a parenthesis at the end.....I haven't tested it in excel, but I'm guessing that's the problem.

Note that for issues like this, the easiest way to resolve them is to debug.print the formula and then paste it into an actual cell. That way, excel will give you clearer error message that you can resolve.

Upvotes: 1

Related Questions