lookup macro

[Gazlar]

Is there a way that I can create a macro that will put colum A and B in ascending order, And lookup E2 with the answer in E3.

Im aware that for the lookup function to work correctly colum A needs to be numerical order.

I have set up colum A and colum b in Auto filter which enables me to sort colum A descending but I would like a button (macro) to do this for me so all I need to do is click the button with assigned macro to do a lookup search for me.

A l B l C l D l E l
1 l Number l Regarding l l Search l
2l0123456 l burt l l # l 01234565
3l0124567 l stan l l Regarding l Burt

Not sure if thats good enough but thats the general idea of the work sheet.
I would also like to be able to add name and to the list without having to scroll down the page each time.

Thanks

 

[riddenstudios]

Hello Gazler,

Set this to your button. You may want to try it in a new worksheet to get it to your specs. Luckily I had this lying around.

Private Sub CommandButton1_Click()
Range("A1:B5").Select
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("A1").Select
End Sub

This is for column A and B. It's only for the first 5 rows but you can set it to the end of the worksheet or max rows you need. Hopefully this gets you started!

 

[Teeroy]

Use Vlookup instead of Lookup. If you specify finding an exact match for Vlookup then the sort order of the data isn't important. It is only required for the function to determine an approximate match.

To go to the bottom of the data to add a new entry simply call the following macro from a button or shape.

Sub go_to_bottom()
    Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Select
End Sub