Help With the VLOOKUP

[athbarbara]

Hello!
I am really new to the use of VBA in EXCel and I am trying two days to Figure something out. I have several worksheets with data about stocks and I have created in Sheet1 a table with their names and Dates. I want to put under a date the return of each stock using VlookUp in a macro, but everything I have tried is pointless.... How can I make the VLookUP function to move each time one cell down and get the information for the stock from its worksheet..?
Can anyone help me please??

[Bob Phillips]

Try

=VLOOKUP($A2,lookup_Table,2,False)

or give us more detail to work on.

[athbarbara]

I modified your type but I cannot define the lookup_Table right.That's my problem.

My first Sheet is something like:

_____A_____ _ ______ B ____________________C____D E
1 ________ ________31/1/94 ______________28/2/94 etc
2 Stock 1 __ (return of stock 1 from sheet 2)
3 Stock 2 ___(return of stock 2 from sheet 3)
4 Stock 3
5 Stock 4
6 Etc
7

I want to modify the VLookup in a macro so that the B column is autofilled

[Bob Phillips]

What does Sheet2 look like?

[athbarbara]

Sheet 2 and the next 291 sheets are like:

A______________B____C____D____E____F__________G
1 stock's name____

2 Date__________(other details about the stocks)______ Returns
3 31/1/1994_______________________________________0,086 etc
4 28/2/1994
5 etc.

So far I've tried sth like this:
Sub Macro1
For i = 2 To 293
Range (i,2).Select
ActiveCell.FormulaR1C1="=VLOOKUP($B$1,'Sheets(i).Name'!C1:C7,7,FALSE)"
Next i
End Sub

I believe I do something wrong with the name of the worksheets.Every worksheet is named after the stock's name..

[mdmackillop]

Something like
[VBA]Cells(i, 2).FormulaR1C1 = "=VLOOKUP(R1C2,Data!C3:C9,7,FALSE)"[/VBA]
Please post a sample workbook if you still have issues. (Manage Attachments in Go Advanced reply section)