Posts by Shluna

  • 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