how to write vlookup in vba code

[monu]

I have a sheet with several column- stocks name,future price, spot price etc.
How to use vlookup function the data stocks and its future price in seperate sheet.
Exp:
Stocks spotprice future price

icici 232 256
idbi 58 62
....
....
...

 

[saltkev]

Hi

There are a thousand out there some of them for free. Just tap in Something like "Excel VBA Free Books" into a search engine. Otherwise I think you could try "Excel VBA Programming for Dummies" it's as good a place as any to start.

regards

Kev

 

[monu]

dear kev,
can be get the top 5 future value along with stock name from the same table.

 

[saltkev]

Hi Mono

Add thisto your existing code after "Next fcomp". I have assumed that you will want to place the information on a third sheet.

'*** Order Table by Future Price
  Sheets("sheet2").Activate
  
  Sheets("sheet2").Range("A1:D200").Select
    ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add Key:=Range("B2:B9"), _
        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet2").Sort
        .SetRange Range("A1:D9")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
'*** Copy & Paste range for top 5 from sheet2tp sheet3
Sheets("sheet2").Range("a1:B6").Copy
Sheets("sheet3").Activate
Sheets("sheet3").Range("a1").Select
ActiveSheet.Paste

regards

Kev

 

[monu]

thanks kev,
result is coming but giving rum time error.
actualy result sould be only 5 top stock and its future value.

 

[saltkev]

OK

Remove the code I asked you to insert. And put it under another command button, and run seperatly. we can see if this is cousing the prob. Make sure you have created a third sheet.

regards

Kev

 

[monu]

dear kev
Is it not possible to get the top 5 value in the same sheet?