Hi Mark,

Am Thu, 10 Aug 2017 05:19:46 -0700 (PDT) schrieb Living the Dream:

Hi Team

I was hoping someone could assist with the best way to evaluate the following:

___AM___AN_____AO____AP____AQ____AR____AS____AT___ ______________________________
_______MCDH__MLDC__MNDC__MRDC__MSDC__MVCS__MSSS___ ______________________________
1__N____N1____N2____N3____N4____N5____N6____N7____ ______________________________
2__S____S1____S2____S3____S4____S5____S6__________ ______________________________
3__E____E1____E2____E3____E4____E5________________ ______________________________
4__W____W1____W2____W3____W4____W5________________ ______________________________

I have it working fine as a sheet function but not sure how to go about converting it to correct syntax for vba.

=INDEX($AN$2:$AT$5,MATCH($Y6,$AM$2:$AM$5,0),MATCH( $AH6,$AN$1:$AT$1,0))

you can insert a formula for the whole range:

With myRng
.Formula = "=INDEX($AN$2:$AT$5,MATCH($Y6,$AM$2:$AM$5,0),MATCH ($AH6,$AN$1:$AT$1,0))"
.Value = .Value
End With


Regards
Claus B.
--
Windows10
Office 2016