How can apply "LastRow" in the formula

[motilulla]

Hello,

I am trying to add in the formula select "LastRow" but can't get it work

Formula below works perfect look value in the C9

 Sub FillArrayFormula()
   
      Dim LastRow As Long
      LastRow = Cells(Rows.Count, "DJ").End(xlUp).Row + 1
 
   
      Range("DJ" & LastRow).FormulaArray = "=CHOOSE(MATCH(MATCH([COLOR=#ff0000][B]$C9[/B][/COLOR]&"""",RIGHT($X$7:$Z$7),0),MATCH(SMALL(LEFT($X$3:$Z$3,FIND(""/"",$X$3:$Z$3)-1)*1,{1,2,3}),LEFT($X$3:$Z$3,FIND(""/"",$X$3:$Z$3)-1)*1,0),0),""L"",""M"",""H"")"
      Range("DJ" & LastRow) = Range("DJ" & LastRow).Value
    
End Sub

I want look value in the C & LastRow. i try to modified the formula as shown below but does not result. Is it possible to do it, as I want?

 Sub FillArrayFormula_1()
   
      Dim LastRow As Long
      LastRow = Cells(Rows.Count, "DJ").End(xlUp).Row + 1
 
   
      Range("DJ" & LastRow).FormulaArray = "=CHOOSE(MATCH(MATCH[COLOR=#000000]([/COLOR][COLOR=#ff0000][B]""C"" & LastRow[/B][/COLOR] &"""",RIGHT($X$7:$Z$7),0),MATCH(SMALL(LEFT($X$3:$Z$3,FIND(""/"",$X$3:$Z$3)-1)*1,{1,2,3}),LEFT($X$3:$Z$3,FIND(""/"",$X$3:$Z$3)-1)*1,0),0),""L"",""M"",""H"")"
      Range("DJ" & LastRow) = Range("DJ" & LastRow).Value
    
End Sub

Please need help if it could be modified or other way possible to use the formula, as I need it.

Thank you all
Excel 2000
Regards,
Moti

 

[MARK858]

Untested but maybe...

Range("DJ" & LastRow).FormulaArray = "=CHOOSE(MATCH(MATCH($C" & LastRow & "&"""",RIGHT($X$7:$Z$7),0),MATCH(SMALL(LEFT($X$3:$Z$3,FIND(""/"",$X$3:$Z$3)-1)*1,{1,2,3}),LEFT($X$3:$Z$3,FIND(""/"",$X$3:$Z$3)-1)*1,0),0),""L"",""M"",""H"")"

 

[motilulla]

Untested but maybe...

Range("DJ" & LastRow).FormulaArray = "=CHOOSE(MATCH(MATCH($C" & LastRow & "&"""",RIGHT($X$7:$Z$7),0),MATCH(SMALL(LEFT($X$3:$Z$3,FIND(""/"",$X$3:$Z$3)-1)*1,{1,2,3}),LEFT($X$3:$Z$3,FIND(""/"",$X$3:$Z$3)-1)*1,0),0),""L"",""M"",""H"")"

MARK858 , Your formula is giving a perfect result as request.

Thank you for your help.

Have a nice day.

Kind Regards,
Moti