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

F

flashgordie

Board Regular
Joined
Jan 9, 2008
Messages
97
Office Version
  1. 365
Platform
  1. Windows
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
xProgramInfoTableItem_Ledger_Entries Table
xTypeSupplierDate RangexxxPosting DateEntry TypeItem - Supplier Code
OGSArgentinaOct 1, 2023;Sept 30, 20242024-05-31TransferArgentina
OGSAustraliaNov 1, 2023;Oct 31, 20242024-05-31TransferAustralia
OGSAustriaSept 1, 2023;Aug 31, 20242024-05-31TransferAustria
OGSBelgiumNov 1, 2023;Oct 31, 20242024-05-31TransferBelgium
OGSBrazilOct 1, 2023;Sept 30, 20242024-06-03SaleBrazil
OGSCanadaOct 1, 2023;Sept 30, 20242024-06-25Positive Adjmt.Canada
OGSChileNov 1, 2023;Oct 31, 20242024-04-09SaleChile
OGSChinaNov 1, 2023;Oct 31, 20242024-05-02SaleChina
OGSDenmarkSept 1, 2023;Sept 30, 20242024-06-03SaleDenmark
OGSEgyptNov 1, 2023;Oct 31, 20242024-06-13PurchaseEgypt
OGSFinlandNov 1, 2023;Oct 31, 20242024-06-13SaleFinland
OGSFranceNov 1, 2023;Oct 31, 20242024-06-21PurchaseFrance
OGSGermanyNov 1, 2023;Oct 31, 20242024-06-24SaleGermany
OGSGreeceSept 1, 2023;Aug 31, 20242024-06-24SaleGreece
OGSHungaryNov 1, 2023;Oct 31, 20242024-06-24SaleHungary
OGSIcelandOct 1, 2023;Sept 30, 20242024-06-24SaleIceland
OGSIndiaNov 1, 2023;Oct 31, 20242024-06-25TransferIndia
OGSItalyDec 1, 2023;Nov 30, 20242024-06-25TransferItaly
OGSJapanOct 1, 2023;Sept 30, 20242024-06-25TransferJapan
OGSMexicoNov 1, 2023;Oct 31, 20242024-06-25TransferMexico
OGSNorwaySept 1, 2023;Aug 31, 20242024-06-25SaleNorway
OGSPolandOct 1, 2023;Sept 30, 20242024-06-25SalePoland
PurchaseArgentinaOct 1, 2023;Sept 30, 20242024-06-25Negative Adjmt.Argentina
PurchaseAustraliaNov 1, 2023;Oct 31, 20242024-06-26SaleAustralia
PurchaseAustriaSept 1, 2023;Aug 31, 20242024-06-28PurchaseAustria
PurchaseBelgiumNov 1, 2023;Oct 31, 20242024-06-26SaleBelgium
PurchaseBrazilOct 1, 2023;Sept 30, 20242024-06-26SaleBrazil
PurchaseCanadaOct 1, 2023;Sept 30, 20242024-06-26SaleCanada
PurchaseChileNov 1, 2023;Oct 31, 20242024-06-28SaleChile
PurchaseChinaNov 1, 2023;Oct 31, 20242024-06-28SaleChina
PurchaseDenmarkSept 1, 2023;Sept 30, 20242024-07-02TransferDenmark
PurchaseEgyptNov 1, 2023;Oct 31, 20242024-07-02TransferEgypt
PurchaseFinlandNov 1, 2023;Oct 31, 20242024-07-02TransferFinland
PurchaseFranceNov 1, 2023;Oct 31, 20242024-07-02TransferFrance
PurchaseGermanyNov 1, 2023;Oct 31, 20242024-07-02SaleGermany
PurchaseGreeceSept 1, 2023;Aug 31, 20242024-07-04SaleGreece
PurchaseHungaryNov 1, 2023;Oct 31, 20242024-07-04SaleHungary
PurchaseIcelandOct 1, 2023;Sept 30, 20242024-07-09TransferIceland
PurchaseIndiaNov 1, 2023;Oct 31, 20242024-07-09TransferIndia
PurchaseItalyDec 1, 2023;Nov 3, 20242024-07-09TransferItaly
PurchaseJapanOct 1, 2023;Sept 30, 20242024-07-09TransferJapan
PurchaseMexicoNov 1, 2023;Oct 31, 20242024-07-09SaleMexico
PurchaseNorwaySept 1, 2023;Aug 31, 20242024-07-10SaleNorway
PurchasePolandOct 1, 2023;Sept 30, 20242024-07-01SalePoland
2024-07-01SaleArgentina
2024-07-04SaleAustralia
2024-07-04SaleAustria
2024-07-04SaleBelgium
2024-07-05SaleBrazil
2024-07-12SaleCanada
2024-07-15SaleChile
2024-09-09SaleChina
2024-05-03SaleDenmark
2024-05-27SaleEgypt
2024-05-28PurchaseFinland
 

Attachments

  • Macro Spreadsheet image.png
    Macro Spreadsheet image.png
    160.9 KB · Views: 4

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Sort by date Sort by votes
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.

Rich (BB code): 
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

O
  • Solved
Put amount in DEBIT,CREDIT columns based on matching part of item
Replies
9
Views
207
Omran Y
O
O
  • Solved
Merge data in listbox based on another listbox after search for name and dates
Replies
6
Views
187
Omran Y
O
A
  • Solved
lookupifs?? lookup value so long as date value is greater than specified date
Replies
5
Views
121
Peter_SSs
Peter_SSs
sharky12345
  • Solved
Issue trying to identify earliest date & time
2
Replies
15
Views
551
sharky12345
sharky12345
M
  • Question Question
Index Match function List based on date
Replies
7
Views
326
Fluff
Fluff

Forum statistics

Threads
1,224,862
Messages
6,181,464
Members
453,044
Latest member
rgbenson1

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top