VBA Macro Issue: Replacing Simple Formula with Complex Formula Causes Error

[flashgordie]

I am trying to create a VBA macro that inserts a new column into an Excel table (item_ledger_entry) and applies a formula. The macro works perfectly when I use a simple formula (=1+2+3) in the new column. Additionally, the more complex Excel formula shown below works when manually entered directly into a cell. However, when I attempt to substitute the simple formula in the macro with the complex formula, I encounter an error. I have tried several approaches to resolve this, but I haven't been able to make it work. Below is the code and formula for reference, along with the issue description. Any assistance would be greatly appreciated! (PS - could not get xl2BB to install, sorry)

The following code works...

Sub Chess_B_Insert_ColumnTESTINGGGGGGGGGGGGGGGGGGGGGGGGG()

Dim headerRange As Range
Dim insertRange As Range
Dim tableName As String
Dim newColumnTitle As String
Dim tbl As ListObject
Dim programTbl As ListObject

' Part 1: Define table names
tableName = "item_ledger_entry" ' Change this to your table name
Dim programTableName As String
programTableName = "ProgramInfoTable"
newColumnTitle = "Qualifications"

' Part 2: Ensure the active worksheet contains the "ProgramInfoTable"
On Error Resume Next
Set programTbl = ActiveSheet.ListObjects(programTableName)
On Error GoTo 0

' Part 3: Exit if "ProgramInfoTable" does not exist
If programTbl Is Nothing Then
MsgBox "The table 'ProgramInfoTable' does not exist on the active worksheet. The macro will exit.", vbCritical, "Table Not Found"
Exit Sub
End If

' Part 4: Find the "item_ledger_entry" table
On Error Resume Next
Set tbl = ActiveSheet.ListObjects(tableName)
On Error GoTo 0

' Part 5: Check if the "item_ledger_entry" table exists
If Not tbl Is Nothing Then
' Part 6: Find the "Posting Date" column within the table
On Error Resume Next
Set headerRange = tbl.HeaderRowRange.Find("Posting Date")
On Error GoTo 0

' Part 7: Check if the "Posting Date" header was found
If Not headerRange Is Nothing Then

' Part 8: Set the insert range to one column to the right of the "Posting Date" header
Set insertRange = headerRange.Offset(0, 1)

' Part 9: Insert one new column to the right
insertRange.EntireColumn.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

' Part 10: Set the new column title
headerRange.Offset(0, 1).Value = newColumnTitle

' Part 11: Format the header cell
With headerRange.Offset(0, 1)
.Interior.Color = RGB(255, 255, 0) ' Set fill color to yellow
.EntireColumn.NumberFormat = "General" ' Format the column as General
End With

' Part 12: Insert the formula in all rows of the new column within the table
Dim dataRange As Range
Set dataRange = tbl.DataBodyRange.Columns(headerRange.Offset(0, 1).Column - tbl.Range.Column + 1)

' Part 13: Apply formula to all rows in the column
dataRange.Formula = "=1+2+3"

' Part 14: Auto-fit the new column for better visibility
headerRange.Offset(0, 1).EntireColumn.AutoFit
Else

' Part 15: Display a message if the "Posting Date" header was not found
MsgBox "Header column 'Posting Date' not found within the table '" & tableName & "'!", vbExclamation, "Column Not Found"
End If
Else

' Part 16: Display a message if the "item_ledger_entry" table does not exist
MsgBox "Table '" & tableName & "' not found!", vbExclamation, "Table Not Found"
End If
End Sub


as does the following excel formula when entered manually...

=IF(SUMPRODUCT((ProgramInfoTable[Type]="Purchase")*(ProgramInfoTable[Supplier]=[@[Item - Supplier Code]]))>0,
IF([@[Entry Type]]="Purchase",
IF(AND(
[@[Posting Date]]>=IFERROR(DATEVALUE(LEFT(FILTER(ProgramInfoTable[Date Range], (ProgramInfoTable[Type]="Purchase")*(ProgramInfoTable[Supplier]=[@[Item - Supplier Code]])), FIND(";", FILTER(ProgramInfoTable[Date Range], (ProgramInfoTable[Type]="Purchase")*(ProgramInfoTable[Supplier]=[@[Item - Supplier Code]]))) - 1)), 0),

[@[Posting Date]]<=IFERROR(DATEVALUE(MID(FILTER(ProgramInfoTable[Date Range], (ProgramInfoTable[Type]="Purchase")*(ProgramInfoTable[Supplier]=[@[Item - Supplier Code]])), FIND(";", FILTER(ProgramInfoTable[Date Range], (ProgramInfoTable[Type]="Purchase")*(ProgramInfoTable[Supplier]=[@[Item - Supplier Code]]))) + 1, LEN(FILTER(ProgramInfoTable[Date Range], (ProgramInfoTable[Type]="Purchase")*(ProgramInfoTable[Supplier]=[@[Item - Supplier Code]]))) - FIND(";", FILTER(ProgramInfoTable[Date Range], (ProgramInfoTable[Type]="Purchase")*(ProgramInfoTable[Supplier]=[@[Item - Supplier Code]]))))), 0)
),
"Qualifies|Purchase|" & [@[Item - Supplier Code]],
"DNQ|Purchase|" & [@[Item - Supplier Code]]
),
IF(OR([@[Entry Type]]="Sale", [@[Entry Type]]="Assembly Consumption"),
IF(AND(
[@[Posting Date]]>=IFERROR(DATEVALUE(LEFT(FILTER(ProgramInfoTable[Date Range], (ProgramInfoTable[Type]="OGS")*(ProgramInfoTable[Supplier]=[@[Item - Supplier Code]])), FIND(";", FILTER(ProgramInfoTable[Date Range], (ProgramInfoTable[Type]="OGS")*(ProgramInfoTable[Supplier]=[@[Item - Supplier Code]]))) - 1)), 0),
[@[Posting Date]]<=IFERROR(DATEVALUE(MID(FILTER(ProgramInfoTable[Date Range], (ProgramInfoTable[Type]="OGS")*(ProgramInfoTable[Supplier]=[@[Item - Supplier Code]])), FIND(";", FILTER(ProgramInfoTable[Date Range], (ProgramInfoTable[Type]="OGS")*(ProgramInfoTable[Supplier]=[@[Item - Supplier Code]]))) + 1, LEN(FILTER(ProgramInfoTable[Date Range], (ProgramInfoTable[Type]="OGS")*(ProgramInfoTable[Supplier]=[@[Item - Supplier Code]]))) - FIND(";", FILTER(ProgramInfoTable[Date Range], (ProgramInfoTable[Type]="OGS")*(ProgramInfoTable[Supplier]=[@[Item - Supplier Code]]))))), 0)
),
"Qualifies|OGS|" & [@[Item - Supplier Code]],
"DNQ|OGS|" & [@[Item - Supplier Code]]
),
"DNQ|OGS|" & [@[Item - Supplier Code]]
)
),
"Supplier not found."
)

however, when I attempt to edit PART 13 of the macro, and replace the formula "=1+2+3" with... the much more complicated one shown above, I get an error. I have tried a few different approaches, but simply can not get it to work. Any help would be appreciated.

A1
x
x
x
x
x
x
x
x
x	ProgramInfoTable	Item_Ledger_Entries Table
x	Type	Supplier	Date Range	xxx	Posting Date	Entry Type	Item - Supplier Code
OGS	Argentina	Oct 1, 2023;Sept 30, 2024	2024-05-31	Transfer	Argentina
OGS	Australia	Nov 1, 2023;Oct 31, 2024	2024-05-31	Transfer	Australia
OGS	Austria	Sept 1, 2023;Aug 31, 2024	2024-05-31	Transfer	Austria
OGS	Belgium	Nov 1, 2023;Oct 31, 2024	2024-05-31	Transfer	Belgium
OGS	Brazil	Oct 1, 2023;Sept 30, 2024	2024-06-03	Sale	Brazil
OGS	Canada	Oct 1, 2023;Sept 30, 2024	2024-06-25	Positive Adjmt.	Canada
OGS	Chile	Nov 1, 2023;Oct 31, 2024	2024-04-09	Sale	Chile
OGS	China	Nov 1, 2023;Oct 31, 2024	2024-05-02	Sale	China
OGS	Denmark	Sept 1, 2023;Sept 30, 2024	2024-06-03	Sale	Denmark
OGS	Egypt	Nov 1, 2023;Oct 31, 2024	2024-06-13	Purchase	Egypt
OGS	Finland	Nov 1, 2023;Oct 31, 2024	2024-06-13	Sale	Finland
OGS	France	Nov 1, 2023;Oct 31, 2024	2024-06-21	Purchase	France
OGS	Germany	Nov 1, 2023;Oct 31, 2024	2024-06-24	Sale	Germany
OGS	Greece	Sept 1, 2023;Aug 31, 2024	2024-06-24	Sale	Greece
OGS	Hungary	Nov 1, 2023;Oct 31, 2024	2024-06-24	Sale	Hungary
OGS	Iceland	Oct 1, 2023;Sept 30, 2024	2024-06-24	Sale	Iceland
OGS	India	Nov 1, 2023;Oct 31, 2024	2024-06-25	Transfer	India
OGS	Italy	Dec 1, 2023;Nov 30, 2024	2024-06-25	Transfer	Italy
OGS	Japan	Oct 1, 2023;Sept 30, 2024	2024-06-25	Transfer	Japan
OGS	Mexico	Nov 1, 2023;Oct 31, 2024	2024-06-25	Transfer	Mexico
OGS	Norway	Sept 1, 2023;Aug 31, 2024	2024-06-25	Sale	Norway
OGS	Poland	Oct 1, 2023;Sept 30, 2024	2024-06-25	Sale	Poland
Purchase	Argentina	Oct 1, 2023;Sept 30, 2024	2024-06-25	Negative Adjmt.	Argentina
Purchase	Australia	Nov 1, 2023;Oct 31, 2024	2024-06-26	Sale	Australia
Purchase	Austria	Sept 1, 2023;Aug 31, 2024	2024-06-28	Purchase	Austria
Purchase	Belgium	Nov 1, 2023;Oct 31, 2024	2024-06-26	Sale	Belgium
Purchase	Brazil	Oct 1, 2023;Sept 30, 2024	2024-06-26	Sale	Brazil
Purchase	Canada	Oct 1, 2023;Sept 30, 2024	2024-06-26	Sale	Canada
Purchase	Chile	Nov 1, 2023;Oct 31, 2024	2024-06-28	Sale	Chile
Purchase	China	Nov 1, 2023;Oct 31, 2024	2024-06-28	Sale	China
Purchase	Denmark	Sept 1, 2023;Sept 30, 2024	2024-07-02	Transfer	Denmark
Purchase	Egypt	Nov 1, 2023;Oct 31, 2024	2024-07-02	Transfer	Egypt
Purchase	Finland	Nov 1, 2023;Oct 31, 2024	2024-07-02	Transfer	Finland
Purchase	France	Nov 1, 2023;Oct 31, 2024	2024-07-02	Transfer	France
Purchase	Germany	Nov 1, 2023;Oct 31, 2024	2024-07-02	Sale	Germany
Purchase	Greece	Sept 1, 2023;Aug 31, 2024	2024-07-04	Sale	Greece
Purchase	Hungary	Nov 1, 2023;Oct 31, 2024	2024-07-04	Sale	Hungary
Purchase	Iceland	Oct 1, 2023;Sept 30, 2024	2024-07-09	Transfer	Iceland
Purchase	India	Nov 1, 2023;Oct 31, 2024	2024-07-09	Transfer	India
Purchase	Italy	Dec 1, 2023;Nov 3, 2024	2024-07-09	Transfer	Italy
Purchase	Japan	Oct 1, 2023;Sept 30, 2024	2024-07-09	Transfer	Japan
Purchase	Mexico	Nov 1, 2023;Oct 31, 2024	2024-07-09	Sale	Mexico
Purchase	Norway	Sept 1, 2023;Aug 31, 2024	2024-07-10	Sale	Norway
Purchase	Poland	Oct 1, 2023;Sept 30, 2024	2024-07-01	Sale	Poland
2024-07-01	Sale	Argentina
2024-07-04	Sale	Australia
2024-07-04	Sale	Austria
2024-07-04	Sale	Belgium
2024-07-05	Sale	Brazil
2024-07-12	Sale	Canada
2024-07-15	Sale	Chile
2024-09-09	Sale	China
2024-05-03	Sale	Denmark
2024-05-27	Sale	Egypt
2024-05-28	Purchase	Finland

 

[Alex Blakenburg]

There are a number of issues at play here:
• Wherever you have a quote mark within the formula you need to double up on the quotes eg ="Purchase" becomes =""Purchase""
• You have line breaks within the formula
• VBA has some issues with length if you have the formula as one line.
Try this, I have used your initial line breaks to build the formula up part by part.

Dim sFormula As String

sFormula = "=IF(SUMPRODUCT((ProgramInfoTable[Type]=""Purchase"")*(ProgramInfoTable[Supplier]=[@[Item - Supplier Code]]))>0,"
sFormula = sFormula & "IF([@[Entry Type]]=""Purchase"","
sFormula = sFormula & "IF(AND("
sFormula = sFormula & "[@[Posting Date]]>=IFERROR(DATEVALUE(LEFT(FILTER(ProgramInfoTable[Date Range], (ProgramInfoTable[Type]=""Purchase"")*(ProgramInfoTable[Supplier]=[@[Item - Supplier Code]])), FIND("";"", FILTER(ProgramInfoTable[Date Range], (ProgramInfoTable[Type]=""Purchase"")*(ProgramInfoTable[Supplier]=[@[Item - Supplier Code]]))) - 1)), 0),"
sFormula = sFormula & "[@[Posting Date]]<=IFERROR(DATEVALUE(MID(FILTER(ProgramInfoTable[Date Range], (ProgramInfoTable[Type]=""Purchase"")*(ProgramInfoTable[Supplier]=[@[Item - Supplier Code]])), FIND("";"", FILTER(ProgramInfoTable[Date Range], (ProgramInfoTable[Type]=""Purchase"")*(ProgramInfoTable[Supplier]=[@[Item - Supplier Code]]))) + 1, LEN(FILTER(ProgramInfoTable[Date Range], (ProgramInfoTable[Type]=""Purchase"")*(ProgramInfoTable[Supplier]=[@[Item - Supplier Code]]))) - FIND("";"", FILTER(ProgramInfoTable[Date Range], (ProgramInfoTable[Type]=""Purchase"")*(ProgramInfoTable[Supplier]=[@[Item - Supplier Code]]))))), 0)"
sFormula = sFormula & "),"
sFormula = sFormula & """Qualifies|Purchase|"" & [@[Item - Supplier Code]],"
sFormula = sFormula & """DNQ|Purchase|"" & [@[Item - Supplier Code]]"
sFormula = sFormula & "),"
sFormula = sFormula & "IF(OR([@[Entry Type]]=""Sale"", [@[Entry Type]]=""Assembly Consumption""),"
sFormula = sFormula & "IF(AND("
sFormula = sFormula & "[@[Posting Date]]>=IFERROR(DATEVALUE(LEFT(FILTER(ProgramInfoTable[Date Range], (ProgramInfoTable[Type]=""OGS"")*(ProgramInfoTable[Supplier]=[@[Item - Supplier Code]])), FIND("";"", FILTER(ProgramInfoTable[Date Range], (ProgramInfoTable[Type]=""OGS"")*(ProgramInfoTable[Supplier]=[@[Item - Supplier Code]]))) - 1)), 0),"
sFormula = sFormula & "[@[Posting Date]]<=IFERROR(DATEVALUE(MID(FILTER(ProgramInfoTable[Date Range], (ProgramInfoTable[Type]=""OGS"")*(ProgramInfoTable[Supplier]=[@[Item - Supplier Code]])), FIND("";"", FILTER(ProgramInfoTable[Date Range], (ProgramInfoTable[Type]=""OGS"")*(ProgramInfoTable[Supplier]=[@[Item - Supplier Code]]))) + 1, LEN(FILTER(ProgramInfoTable[Date Range], (ProgramInfoTable[Type]=""OGS"")*(ProgramInfoTable[Supplier]=[@[Item - Supplier Code]]))) - FIND("";"", FILTER(ProgramInfoTable[Date Range], (ProgramInfoTable[Type]=""OGS"")*(ProgramInfoTable[Supplier]=[@[Item - Supplier Code]]))))), 0)"
sFormula = sFormula & "),"
sFormula = sFormula & """Qualifies|OGS|"" & [@[Item - Supplier Code]],"
sFormula = sFormula & """DNQ|OGS|"" & [@[Item - Supplier Code]]"
sFormula = sFormula & "),"
sFormula = sFormula & """DNQ|OGS|"" & [@[Item - Supplier Code]]"
sFormula = sFormula & ")"
sFormula = sFormula & "),"
sFormula = sFormula & """Supplier not found."""
sFormula = sFormula & ")"

dataRange.Formula = sFormula