Vlookup through VBA: Dynamic Range

[wouldbeca]

Hello Everyone,

I am a novice VBA user who gets his work done though knowledge shared by other users. In this case also I had found one good code which suits perfectly to my requirement subject to catch. Here is the code:-



Sub ADDCLM()
On Error Resume Next
Dim Dept_Row As Long
Dim Dept_Clm As Long
Table1 = Sheet1.Range("A3:A13") ' Employee_ID Column from Employee table
Table2 = Sheet1.Range("H3:I13") ' Range of Employee Table 1
Dept_Row = Sheet1.Range("E3").Row ' Change E3 with the cell from where you need to start populating the Department
Dept_Clm = Sheet1.Range("E3").Column
For Each cl In Table1
Sheet1.Cells(Dept_Row, Dept_Clm) = Application.WorksheetFunction.VLookup(cl, Table2, 2, False)
Dept_Row = Dept_Row + 1
Next cl
MsgBox "Done"
End Sub

In the above code, I require excel to use selection of data instead of Sheet1.Range("A3:A13"). For e.g. if select in sheet 2 cells ranging from B5:B15 then excel shoul return desired data in next column to it. I tried few options with my limited knowledge but failed to do so.

Please suggest the code with suitable changes or any other code which i can use to get desired result.

Thanks in advance!!! Cheers!!!

[westconn1]

to set a range object variable to the selected range
try like
set table1 = selection.range

to put the result in the next column on right, same row
cl.offset(,1) = Application.WorksheetFunction.VLookup(cl, Table2, 2, False)

[wouldbeca]

I tried code suggested by you but not able to get the desired results. I guess i am making some error in using the correction as advised by you especially in the second part.. Can u make changes in the code itself and post here.

You would be of great help.


to set a range object variable to the selected range
try like
set table1 = selection.range

to put the result in the next column on right, same row
cl.offset(,1) = Application.WorksheetFunction.VLookup(cl, Table2, 2, False)

[westconn1]

set Table1 = selection
Table2 = Sheet1.Range("H3:I13") ' Range of Employee Table 1
Dept_Row = Sheet1.Range("E3").Row ' Change E3 with the cell from where you need to start populating the Department
Dept_Clm = Sheet1.Range("E3").Column
For Each cl In Table1
cl.offset(, 1).value = Application.WorksheetFunction.VLookup(cl, Table2, 2, False)
Next cl
MsgBox "Done" this is totally untested, but should work without error

[wouldbeca]

set Table1 = selection
Table2 = Sheet1.Range("H3:I13") ' Range of Employee Table 1
Dept_Row = Sheet1.Range("E3").Row ' Change E3 with the cell from where you need to start populating the Department
Dept_Clm = Sheet1.Range("E3").Column
For Each cl In Table1
cl.offset(, 1).value = Application.WorksheetFunction.VLookup(cl, Table2, 2, False)
Next cl
MsgBox "Done" this is totally untested, but should work without error

[wouldbeca]

Thanks. It works. Now I got into something more advanced for same purpose. You formula works there as well but i am stuck on one thing. Will come back if not able to get around.

[wouldbeca]

Thanks. It works. Now I got into something more advanced for same purpose. You formula works there as well but i am stuck on one thing. Will come back if not able to get around.


I had used your suggestion into some other code and here it is.


Sub MakeFormulas()
Dim SourceLastRow As Long
Dim sourceBook As Workbook
Dim sourceSheet As Worksheet
Application.ScreenUpdating = True

'Where is the source workbook?
Set sourceBook = Workbooks.Open("E:\LocationList.xlsx")

'what are the names of our worksheets?
Set sourceSheet = sourceBook.Worksheets("Sheet1")



'Determine last row of source
With sourceSheet
SourceLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With


Set Table1 = Selection

For Each c1 In Table1

c1.Offset(, 1).Formula = _
"=VLOOKUP(c1,'[" & sourceBook.Name & "]" & sourceSheet.Name & "'!$A$2:$C$" & SourceLastRow & ",3,0)"
Next c1


'Close the source workbook, don't save any changes
sourceBook.Close False
Application.ScreenUpdating = True
MsgBox "Done"
End Sub


The problem is when I am using 'Table 1 = Selection' it is selecting from source sheet. Instead I want to use the selection in my output sheet ( It can be any sheet I mean I cant freeze it name and I want to use my code as add in file when all coding is done ) and give result next to it. Please suggest suitable modification.

Your help is highly appreciable.

[westconn1]

as the application can only have one selection currently active, which is always to one on the activesheet, you would need to activate the worksheet, to make the selection relate to that sheet

if you can not activate the worksheet by name, you would have to find some other criteria to match, then loop through all worksheets (maybe in all workbooks) till the correct worksheet is found

alternatively, add a toolbar or ribbon button, that can be activated from the sheet after selecting the cells, so already the correct worksheet is the activesheet

[wouldbeca]

as the application can only have one selection currently active, which is always to one on the activesheet, you would need to activate the worksheet, to make the selection relate to that sheet

if you can not activate the worksheet by name, you would have to find some other criteria to match, then loop through all worksheets (maybe in all workbooks) till the correct worksheet is found

alternatively, add a toolbar or ribbon button, that can be activated from the sheet after selecting the cells, so already the correct worksheet is the activesheet

Ya trying to get into what you said. Ok I got your point. Well Need your suggestion what I thought could be way around. As you can see in my code My code opens the source book first and then in turns make it active workbook. Well can I have Prompt box which requires me to select the data. It can solve my two purpose. One, it gives a chance to select the data after running the code. Secondly, it will make my outputsheet active. Am I right in my understanding or there is a tweak to it. If that solves my purpose, could you suggest me code fot prompt box as i have no idea of that but I seen codes having prompt box.

Thanks once again for being so helpful.

[westconn1]

could you suggest me code fot prompt boxrangeaddress = inputbox("select a range")


My code opens the source book first and then in turns make it active workbook
try like

set selsheet = activesheet
'Where is the source workbook?
Set sourceBook = Workbooks.Open("E:\LocationList.xlsx")

'what are the names of our worksheets?
Set sourceSheet = sourceBook.Worksheets("Sheet1")
selsheet.activateone of the very few times to use select or activate within code

[wouldbeca]

rangeaddress = inputbox("select a range")


try like

set selsheet = activesheet
'Where is the source workbook?
Set sourceBook = Workbooks.Open("E:\LocationList.xlsx")

'what are the names of our worksheets?
Set sourceSheet = sourceBook.Worksheets("Sheet1")
selsheet.activateone of the very few times to use select or activate within code

I got my code. Full and Final to my satisfaction. It couldn't been possible without you. Thanks Mate. Well here is my final code.




Sub Location_Name()
Dim SourceLastRow As Long
Dim sourceBook As Workbook
Dim sourceSheet As Worksheet


Application.ScreenUpdating = True

Set selsheet = ActiveSheet

'Where is the source workbook?
Set sourceBook = Workbooks.Open("E:\LocationList.xlsx")

'what are the names of our worksheets?
Set sourceSheet = sourceBook.Worksheets("Sheet1")
selsheet.Activate

'Determine last row of source
With sourceSheet
SourceLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

Set Table1 = Selection
Cell = ActiveCell.Address(0, 0)

For Each C1 In Table1

C1.Offset(, 1).Formula = _
"=VLOOKUP( " & Cell & " ,'[" & sourceBook.Name & "]" & sourceSheet.Name & "'!$A$2:$C$" & SourceLastRow & ",3,0)"


Next C1

'Close the source workbook, don't save any changes
sourceBook.Close False
Application.ScreenUpdating = True
MsgBox "Done"
End Sub