PDA

View Full Version : Help With the VLOOKUP



athbarbara
03-14-2011, 04:23 PM
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....:banghead::banghead: How can I make the VLookUP function to move each time one cell down and get the information for the stock from its worksheet..?:dunno
Can anyone help me please??:help:help:help:help:help:help

Bob Phillips
03-15-2011, 01:33 AM
Try

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

or give us more detail to work on.

athbarbara
03-15-2011, 07:58 AM
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
03-15-2011, 09:55 AM
What does Sheet2 look like?

athbarbara
03-15-2011, 12:17 PM
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
03-15-2011, 01:08 PM
Something like
Cells(i, 2).FormulaR1C1 = "=VLOOKUP(R1C2,Data!C3:C9,7,FALSE)"
Please post a sample workbook if you still have issues. (Manage Attachments in Go Advanced reply section)