Shluna Beginner

  • Member since May 5th 2021
  • Last Activity:
Posts
1
Points
15
Profile Hits
22
  • Hi anyone could help me solve my codes in inserting formula in my excel file?

    here's the part of my code I'm having trouble. Hope there's someone who could help me. I'm getting Defined object error on formula no. 4


    Formula1 = "=IF(COUNTIF(Reference!B:B,[@[Actual Location]])=1,""Included"","""")"

    Formula2 = "=IFERROR(IF(INDEX('Multiple SKU'!B:B,MATCH([@[Actual Location]],'Multiple SKU'!A:A,0))>1,""Multiple SKU"",""""),"""")"

    Formula3 = "=IF(COUNTIF(Ref!A:A,[@[SKU]])=0,""Prod Life For Update"",IF(INDEX(Ref!$C:$C,MATCH([@[SKU]],Ref!$A:$A,0))="""",""Expiry For Update"",IF([@[CONSUMPTION_PRIORITY_DTTM]]="""",""No Consum Prio"",IF(INT([@[CONSUMPTION_PRIORITY_DTTM]])<>INT([@[Expire Date]]),""Mismatch Expiry & Consum Prio"",IF(BX3="""",""No Mfg Date"",IF([@[Expire Date]]-[@[MANUFACTURED_DTTM]]<>INDEX(Ref!$C:$C,MATCH([@[SKU]],Ref!$A:$A,0)),""Wrong Expiry"",IF([@[Expire Date]]="""",""No Expiry"",IF([@Batch]="""",""No Batch"",IF(OR(LEFT([@Batch],2)<>""20"",LEN([@Batch])<>8),""Wrong Batch"",IF(AND(DATEVALUE(IF(LEN([@Batch])=8,MID([@Batch],5,2)&""/""&RIGHT([@Batch],2)&""/""&LEFT([@Batch],4))),TODAY()-(DATEVALUE(MID([@Batch],5,2)&""/""&RIGHT([@Batch],2)&""/""&LEFT([@Batch],4)))>=0),""OK"",IF(DATEVALUE(MID([@Batch],5,2)&""/""&RIGHT([@Batch],2)&""/""&LEFT([@Batch],4))=BX3,""OK"",""Mismatch Batch & Mfg Date"")))))))))))"

    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))))"


    '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