# ShlunaBeginner

• Member since May 5th 2021
• Last Activity:
Posts
1
Points
15
Profile Hits
30
• 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