Help to change VBA Array Index Formula to remove #N/A leave blank

[norman_bunch]

Hello Board,

I am trying to figure out how I can change the following line of code to leave out the #N/A and just leave the cell values blank. I think that this would be an easy task but I can not figure it out.

Range("DS8:DS78").FormulaArray = "=INDEX('Cover Sheet'!$AA$46:$AA$68,MATCH(CV8:CV78&DD8:DD78,'Cover Sheet'!$D$46:$D$69&'Cover Sheet'!$L$46:$L$69,0))"

Please Help!

 

[Jonmo1]

Do you want to end up with the formula in the cell, or just the result of the formula?

 

[Jonmo1]

If you want the formula in the cells, You can try

Range("DS8:DS78").FormulaArray = "=IF(ISNA(MATCH(CV8:CV78&DD8:DD78,'Cover Sheet'!$D$46:$D$69&'Cover Sheet'!$L$46:$L$69,0)),"""",INDEX('Cover Sheet'!$AA$46:$AA$68,MATCH(CV8:CV78&DD8:DD78,'Cover Sheet'!$D$46:$D$69&'Cover Sheet'!$L$46:$L$69,0)))"

 

[norman_bunch]

If you want the formula in the cells, You can try

Range("DS8:DS78").FormulaArray = "=IF(ISNA(MATCH(CV8:CV78&DD8:DD78,'Cover Sheet'!$D$46:$D$69&'Cover Sheet'!$L$46:$L$69,0)),"""",INDEX('Cover Sheet'!$AA$46:$AA$68,MATCH(CV8:CV78&DD8:DD78,'Cover Sheet'!$D$46:$D$69&'Cover Sheet'!$L$46:$L$69,0)))"

I would like to just end up with the result of the formula in the cell. Thanks for the update, I will give that a try once I get home.

 

[Jonmo1]

Try

With Range("DS8:DS78")
    .FormulaArray = "=IF(ISNA(MATCH(CV8:CV78&DD8:DD78,'Cover Sheet'!$D$46:$D$69&'Cover Sheet'!$L$46:$L$69,0)),"""",INDEX('Cover Sheet'!$AA$46:$AA$68,MATCH(CV8:CV78&DD8:DD78,'Cover Sheet'!$D$46:$D$69&'Cover Sheet'!$L$46:$L$69,0)))"
    .Value = .Value
End With