Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find Exact Match using INDEX, MATCH | Excel Worksheet Functions | |||
index(match) Wind Uplift Calculations (match four conditions) | Excel Worksheet Functions | |||
index match array function-returning only first match, need last. | Excel Worksheet Functions | |||
How do I display more than one match in a Index/Match formula? | Excel Worksheet Functions | |||
index,match,match on un-sorted data | Excel Worksheet Functions |