vba Index Match Match
 

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

So essentially something along the lines of.

For each c in myRng
If Not c = "" then
c = WorksheetFunction.Evaluate_
("=INDEX($AN$2:$AT$5,MATCH($Y6,$AM$2:$AM$5,0),MATC H($AH6,$AN$1:$AT$1,0))")
With c
.Value = .Value
End with
End If
Next

TIA
Mark

vba Index Match Match
 

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

vba Index Match Match
 

Hi Claus

This worked well for the first row, sadly every row down has the same exact formula instead of incrementing the row number as it goes down the list.

I think maybe RC Formula might be a better option, would would you recommend please?

vba Index Match Match
 

Hi Mark,

Am Fri, 11 Aug 2017 04:34:30 -0700 (PDT) schrieb Living the Dream:

This worked well for the first row, sadly every row down has the same exact formula instead of incrementing the row number as it goes down the list.

all relative references will increment for each row. Only the absolute
references stay.
Download here the demo workbook because macros are disabled in OneDrive:
https://1drv.ms/x/s!AqMiGBK2qniTgaAyDPnqcQyQIjJxpA
There are solution for both your questions.

Regards
Claus B.
--
Windows10
Office 2016

vba Index Match Match
 

Hi Claus

Thank you for your patience.

No idea why the index Match did not work for me, but it is now working thank you.

I see where there is the issue in the transposing.

Only column A has the Unit No's. They are not repeated in G , M, etc.

The array spacing are as follows:

| Week | Unit | Rows |
|______|________|_______
|..1...|Company.|...9..|
|......|........|..22..|
|______|________|______|
|..1...|Subby...|..27..|
|......|........|..46..|
|______|________|______|
|..2...|Company.|..55..|
|......|........|..68..|
|______|________|______|
|..2...|Subby...|..73..|
|......|........|..92..|
|______|________|______|
|..3...|Company.|.101..|
|......|........|.114..|
|______|________|______|
|..3...|Subby...|.119..|
|......|........|.138..|
|______|________|______|
|..4...|Company.|.147..|
|......|........|.160..|
|______|________|______|
|..4...|Subby...|.165..|
|......|........|.184..|
|______|________|______|

Etc......

Appreciate your help.

Cheers
Mark.