CODEWITHSUNDEEP
excelvbaexcel-formula
CACinAZ
CACinAZ

Reputation: 13

Formula into Excel Table using VBA

I want to insert the following formula into the third column of my Excel Table "Transactions". If I enter it manually, it works, but if I try to enter it using a subroutine I get the Error 1004. Not sure how to solve this. Here is a code snippet:

    Sub test3()
    Dim ws As Worksheet
    Dim lo As ListObject
    Dim lCol As ListColumn

    Set ws = ThisWorkbook.Worksheets("Transactions")
    Set lo = ws.ListObjects(1)
    Set lColName = lo.ListColumns(3)

    lColName.DataBodyRange.Formula = "=IFERROR(INDEX(Staff[CREDENTIALS],MATCH([@[Staff, Last Name]],LastName,0)),"")"
    End Sub

All I really need to do is to put the formula into the first row of the table (row 2) in the third column "Staff, Credentials". It is pulling from another Excel Table "Staff".

Upvotes: 0

Views: 3353

Answers (1)

adb
adb

Reputation: 258

I believe the issue is coming from your formula (I know you mentioned it works when you manually enter it however when I try I'm getting an error), try changing [@[Staff, Last Name]] to Staff[Last Name]

Also, you'll need to escape the "" in the IFERROR formula, the below code should work:

Sub test3()
    Dim ws As Worksheet
    Dim lo As ListObject
    Dim lCol As ListColumn

    Set ws = ThisWorkbook.Worksheets("Transactions")
    Set lo = ws.ListObjects(1)
    Set lColName = lo.ListColumns(3)

    lColName.DataBodyRange.Formula = "=IFERROR(INDEX(Staff[CREDENTIALS],MATCH(Staff[Last Name],LastName,0)),"""")"
End Sub

Upvotes: 3

Related Questions