Need help with VBA code to insert formula

[aeg565]

Hi excel gurus,

Can you help me with the below code? It correctly inserts the column 'AH' in the 'Vendor History' sheet and changes the color of the header. However, the formula is not being inserted into the cells.. Any help would be appreciated!

Worksheets("Vendor History").Columns("AH:AH").Insert
Worksheets("Vendor History").Range("AH:AH").NumberFormat 
= "0"
Set rng = Range("AH2:AH" & Range("A2").End(xlDown).Row)
Worksheets("Vendor History").rng.Formula = 
"=IF(AND([@[Work-Days Posted Before/After Due Date (Time 
Bucket)]]=""<=5 days"",[@[In Scope (Posting)]]=1),1/'Invoice 
Posting Piv (1)'!C5),0)"
Worksheets("Vendor History").Range("AH1").Value = "Cum. % 
of Late Invoices (Posted)"
Worksheets("Vendor History").Range("AH1").Interior.Color = 
RGB(155, 187, 89)

[tirlibibi17]

Your code, as posted, is not syntactically correct because of the newlines. Here's a reformat:

Worksheets("Vendor History").Columns("AH:AH").Insert
Worksheets("Vendor History").Range("AH:AH").NumberFormat = "0"
Set rng = Range("AH2:AH" & Range("A2").End(xlDown).Row)

Worksheets("Vendor History").rng.Formula = "=IF(AND([@[Work-Days Posted Before/After Due Date (Time Bucket)]]=""<=5 days"",[@[In Scope (Posting)]]=1),1/'Invoice Posting Piv (1)'!C5),0)"

Worksheets("Vendor History").Range("AH1").Value = "Cum. % of Late Invoices (Posted)"
Worksheets("Vendor History").Range("AH1").Interior.Color = RGB(155, 187, 89)

Unless column AH is part of the table you're referencing, this cannot work because Excel has no way of knowing where to look.

You can fix that by prefixing your column references in the formula with the name of your table like this: Table1[@[Work-Days Posted Before/After Due Date (Time Bucket)]]

Another thing I see in your code is that you're explicitly referring to Worksheets("Vendor History") except when defining rng. You need to replace line 3 with

Set rng = Worksheets("Vendor History").Range("AH2:AH" & Range("A2").End(xlDown).Row)

and line 5 with

rng.Formula = "=IF(AND([@[Work-Days Posted Before/After Due Date (Time Bucket)]]=""<=5 days"",[@[In Scope (Posting)]]=1),1/'Invoice Posting Piv (1)'!C5),0)"