Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 151
Default 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
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default 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
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 151
Default 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?

  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default 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
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 151
Default 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.
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Find Exact Match using INDEX, MATCH DoubleUU Excel Worksheet Functions 3 August 15th 08 02:42 PM
index(match) Wind Uplift Calculations (match four conditions) JMeier Excel Worksheet Functions 8 August 1st 08 01:45 AM
index match array function-returning only first match, need last. Julie Olsen Excel Worksheet Functions 3 December 29th 06 01:50 AM
How do I display more than one match in a Index/Match formula? Trish Excel Worksheet Functions 0 September 26th 05 10:21 PM
index,match,match on un-sorted data Brisbane Rob Excel Worksheet Functions 3 September 24th 05 10:04 PM


All times are GMT +1. The time now is 12:29 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"