Help me correct formula of vlookup of my Excel template I am getting an Application defined error on Formula4

    Formula4 = "=IFERROR(INDEX(Reference!D:D,MATCH([@Location],Reference!C:C,0)),""))"

    Formula5 = "=IF(AND(LEN([@LPN])=16,LEFT([@LPN],2)=""IP"",COUNTIF(Reference!A:A,RIGHT([@LPN],3))),""OK"",""Wrong LPN"")"

    Formula6 = "=IF(COUNTIF(Ref!A:A,[@[SKU]])=0,""For Update"",INDEX(Ref!C:C,MATCH([@[SKU]],Ref!A:A,0)))"

    Formula7 = "=IF([@[Expire Date]]="""",INDEX('Expiry Compliance'!O:O,MATCH(SOH!AC3,'Expiry Compliance'!E:E,0)),[@[Expire Date]])"

    Formula8 = "=IF(LEN([@SKU])>6,""Pallet"",""Item"")"

    Formula9 = "=IF([@LOCATION]="""",""INP"",IF(LEN([@LOCATION])=8,MID([@LOCATION],3,2)&""-""&MID([@LOCATION],5,2)&""-""&LEFT([@LOCATION],2),IF(LEN([@LOCATION])=6,LEFT([@LOCATION],2)&""-""&MID([@LOCATION],3,2)&""-""&RIGHT([@LOCATION],2),MID([@LOCATION],2,2)&""-""&MID([@LOCATION],4,2)&""-""&RIGHT([@LOCATION],2))))"


    'Remove time from Manufacturing_DTTM Column

    Sheets("SOH").ListObjects("Table1").ListColumns(76).DataBodyRange.TextToColumns Destination:=Range("BX3"), DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, xlDMYFormat), Array(10, xlSkipColumn))

    Sheets("SOH").ListObjects("Table1").ListColumns(76).DataBodyRange.TextToColumns Destination:=Range("BX3"), DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, xlDMYFormat), Array(10, xlSkipColumn))


    'Assign formulas to SOH sheet

    Sheets("SOH").Range("A3").Value = Formula1

    Sheets("SOH").Range("B3").Value = Formula2

    Sheets("SOH").Range("C3").Value = Formula3

    Sheets("SOH").Range("D3").Value = Formula4

    Sheets("SOH").Range("E3").Value = Formula5

    Sheets("SOH").Range("F3").Value = Formula6

    Sheets("SOH").Range("G3").Value = Formula7

    Sheets("SOH").Range("H3").Value = Formula8

    Welcome to the Forum. Please read the Forum Rules to understand how the Forum works and why I have added Code Tags to your post


    All VBA code posted in the forum must be wrapped in code tags, which you omitted, including single-line code snippets.Be sure to use them in future posts.


    How to use code tags


    Just highlight all of the code and press the <> in the post menu above button to add the code tags.


    Thanks.

    What's the point of having a template sheet that requires VBA to add the formulas?


    Create the template with the formulas added manually.


    Also, if you want help read the Forum Rules and attach an example workbook.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login