xlDown and xlToLeft in a vlookup in a macro

Hi
I cant get shift+control+arrow to record an xlDown in a formula that I need a macro for and dont have the programing required to write it myself. I swear it used to work on my old computer, but now when I type shift+control+arrow in a formula it has the same effect of hitting enter and I get an error that the formula is incomplete.

my data looks like (thats 2 columns with some empty spaces, I have added row numbers and column names as they appear on excel for reference and 'blank' indicates a blank cell):
A B
1 'blank' 'blank'
2 'blank' >isogroup00003
3 2 'blank'
4 17771 'blank'
5 1280 isotig00001
6 907 isotig00002
7 1041 isotig00003
8 'blank' 'blank'
9 'blank' >isogroup00004
10 684 'blank'
11 17854 'blank'
12 1313 isotig00031
13 1313 isotig00032
14 1313 isotig00033
15 1313 isotig00034


I need a macro that returns the name in column B for the max value in column A (but only for the entries where there is a name in column B, i.e. not A3 or A4 for the first group and not A10 or A11 for the second group) for 1500 separate groups organised as above.

my macro is:

Please Login or Register  to view this content.

but since the different tables have different numbers of rows I need the selection to use xlDown and xlToLeft instead of the specific number of cells from the current that it has now (relative references is turned on)
I need the formula to essentially do =vlookup(max(shift+control+arrows to select all values that have a name in column B down to the blank space),shift+control+arrows to select values and names from columns A and B down to the blank space,2,0)

Thanks in advance
Jeremy

Edit: title amended
thanks Leith for adding code tags

Hi Jeremy.S
Crl + Shift + Arrow Down should still work
You cant use the arrows on the numbered keys
do you have two sets of arrows keys, one just with arrows?

Hi Jeremy.S
hopefully will beable to get the result with a array formula but not quite there yet

=IF(OR($A2=0,$B2=""),"",IF(INDEX($A$2:$A2,LARGE(($A$2:$A2<>0)*ROW($A$2:$A2),1)-ROW($A$2)+1)<$A3,"",INDEX($A$2:$A2,LARGE(($A$2:$A2<>0)*ROW($A$2:$A2),1)-ROW($A$2)+1)))
I keep at it!!

hey Jeremy.S I've give up on array formula
here is VBA which will do the trick

Please Login or Register  to view this content.