Index-Match in vba

[pjkaphlen]

Dear VBA wizards

I have a problem that I need some help from one of you.

I have a table that contains prices and names (dynamic). The names are in columns and the prices are given in rows. Their range changes in every excel workbook. i.e. In WB1, I have 2 names and under each name i have 2 prices. While in WB2 i have 4 Names and under each name I have one price each. Let's try the table below

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Min[/TD]
[TD]John[/TD]
[TD]Olga[/TD]
[TD]Peter[/TD]
[TD]Name[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]$1.5[/TD]
[TD]$2.00[/TD]
[TD]$1.5[/TD]
[TD]$3.0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]$0.5[/TD]
[TD]$0.5[/TD]
[TD]$1.0[/TD]
[TD]$0.75[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Here's the trick: on E2 is use this formula "=index($B$1:$D$1,match(A2,B2:D2,0)) to return the name of the person with min. dollar value.

But now I want to create a vba code that can automatically input this formula.

Kindly note that since the number of name changes thus changing the position of the column with "Name" header.

Can anyone enlighten me on this?

Thank you so much in advance!

 

[ww4612]

you can use variable in formula. for example, instead of r2c2, you can use
r2c" & t & "
t is the column index of r2c2
you can use vba to define the column index before apply it in the vba formula.

 

[pjkaphlen]

you can use variable in formula. for example, instead of r2c2, you can use
r2c" & t & "
t is the column index of r2c2
you can use vba to define the column index before apply it in the vba formula.

Thanks for the idea ww4612!