Macro help with MAX and VLOOKUP functions of different sizes

[MKordi]

[FONT=&quot]Hi am a novice on VBA and have tried everything so I would be appreciative of any help. [/FONT]
[FONT=&quot]I am looking to write a macro that will select a max value in a column of a select area. These areas (or column heights) are varying in height and therefore I need the "Selection(x1up)" selection rather than specific co-ordinate. I.e. some columns are 100 lines long others are 300. I also want it to [/FONT]
[FONT=&quot]So this is what I get when I record a macro: [/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot] ActiveCell.FormulaR1C1 = "=MAX(R[-197]C:R[-1]C)"
ActiveCell.Offset(1, 0).Range("A1").Select[/FONT]
[FONT=&quot]I need something that simply uses the "max" function for the cell above the active cell and then the range should be up using "shift" + "ctrl" + "arrow up". [/FONT]
[FONT=&quot]My second issue is that I am wanting to write a lookup in the cell below the max cell with the forumula where it will look for the max value in the column above it (two above the vlookup cell) and then connect it to another column that is X across. Like above I need it to have the "selection end up and right" functions like below [/FONT]
[FONT=&quot] Range(Selection, Selection.End(xlUp)).Select
Range(Selection, Selection.End(xlToRight)).Select[/FONT]
[FONT=&quot]rather than having the set coordinates. [/FONT]
[FONT=&quot] ActiveCell.FormulaR1C1 = "=VLOOKUP(R[-1]C,R[-198]C:R[-2]C[15],15,FALSE)"
ActiveCell.Offset(1, 0).Range("A1").Select[/FONT]
[FONT=&quot]I have put an example of the columns below. I tried to screen shot but it didnt let me. To the right of where it says "max" is the first formula line. What I want to change on that line is that is goes one up from the selected cell and get the max value by selecting all the cells via the "shift" + "control" + "up" key.
Below it is the VLOOKUP function where I want to search for the max value (in the cell above) and look for it in the selection from where the max value was found and then find the value in the furthest possible column across.

I hope that makes sense.

Any help would be appreciated.

Thanks, [/FONT]
[FONT=&quot]
[/FONT]
[TABLE="width: 192"]
<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64, align: right"]1.135[/TD]
[TD="width: 64, align: right"]0.04348[/TD]
[TD="width: 64, align: right"]0.05665[/TD]
[/TR]
[TR]
[TD="align: right"]1.14[/TD]
[TD="align: right"]0.04389[/TD]
[TD="align: right"]0.05672[/TD]
[/TR]
[TR]
[TD="align: right"]1.145[/TD]
[TD="align: right"]0.04695[/TD]
[TD="align: right"]0.05676[/TD]
[/TR]
[TR]
[TD="align: right"]1.15[/TD]
[TD="align: right"]0.05132[/TD]
[TD="align: right"]0.05665[/TD]
[/TR]
[TR]
[TD="align: right"]1.155[/TD]
[TD="align: right"]0.07827[/TD]
[TD="align: right"]0.05683[/TD]
[/TR]
[TR]
[TD="align: right"]1.16[/TD]
[TD="align: right"]0.08613[/TD]
[TD="align: right"]0.0569[/TD]
[/TR]
[TR]
[TD]Max[/TD]
[TD="align: right"]0.41969[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Position[/TD]
[TD="align: right"]59.67[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[FONT=&quot]=MAX(B3:B199)
=VLOOKUP(B200,B3:Q199,16,FALSE)[/FONT]