VBA Vlookup with Dynamic Data Table Range

[BYUIStudent]

Hello all. First off thank you so much for all your help. Mr. Excel and your forums has helped me out so much. I am still in the very beginnings of understanding VBA so please forgive my ignorance on certain topics.


This is what I am trying to do.

I am trying to set a cell to =Vlookup($C20,Dynamic table aray,2)

The table array will always be different

This is what i am trying to use

Function VLookUP()
Dim StockTableRange As Range


Sheets("Stock Names").Select
Range("Home").Offset(1, -1).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Set StockTableRange = Selection


Sheets("Raw Data").Select


Range("RawDate").Offset(1, 1).Select
activecell = "=vlookup($c20,StockTableRange,2)"




End Function


Thank you for your time and your efforts

BYUIstudent.

 

[MUKESHY12390]

Note ---always try to avoid select thing

Sub D_vlookup()
Dim lr, cl2, Cl As Long
Dim myrnage As Range


lr = Range("Home").Row + 1
cl2 = Range("Home").Column
Cl = Range("Home").End(xlToRight).Column - 1




 Set myrange = Range(Cells(lr, cl2 - 1), Cells(Rows.Count, Cl))


lookup_result = WorksheetFunction.VLookup("search type here", myrange, 2, 0)


End Sub

 

[BYUIStudent]

Thank you so much for your help.

Regarding your Note. Why do you suggest avoiding "Select Thing"

 

[MUKESHY12390]

Follow below link there is good discussion about what to avoid in VBA!

--http://www.excelforum.com/excel-programming-vba-macros/718395-what-to-avoid-in-vba.html
--http://blogs.msdn.com/excel/archive/...practices.aspx


Straight way always better than via.