CODEWITHSUNDEEP
vbaexcel
Niva
Niva

Reputation: 298

Defining variable for VLOOKUP code

Have been trying to run a sample code but unable to do so. most proabably because I did not define the variables properly( Table 1&2 and Cl) .Could anyone help me rectify it so i have a better idea on how i should do it in the future?Thanks alot. Here is the code

Option Explicit


Sub ADDCLM()
On Error Resume Next
Dim Dept_Row As Long
Dim Dept_Clm As Long
Dim Table1 As Range
Dim Table2 As Range
Dim cl As Variant

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

Upvotes: 0

Views: 154

Answers (3)

TsTeaTime
TsTeaTime

Reputation: 921

The following code should work for you. The only change that were needed was to update the Table1 and Table2 to Variant types or use Set before Table1 and Table2. This is defined in the syntax for the the following Application.WorksheetFunction.VLookup expression. 

'Option 1 Declare Tables as vaiants
Option Explicit


Sub ADDCLM()
On Error Resume Next
Dim Dept_Row As Long
Dim Dept_Clm As Long
Dim Table1 As Variant
Dim Table2 As Variant
Dim cl As Variant

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

Or: '

'Option 2 Set Ranges using "Set"
Option Explicit


Sub ADDCLM()
On Error Resume Next
Dim Dept_Row As Long
Dim Dept_Clm As Long
Dim Table1 As Range
Dim Table2 As Range
Dim cl As Variant


Set Table1 = Sheet1.Range("A3:A13") ' Employee_ID Column from Employee table
Set 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

Upvotes: 0

user3598756
user3598756

Reputation: 29421

you not only aren't properly setting ranges (as KS Sheon already pointed out) but you are also using invalid reference: Sheet1 should be substituted with Worksheets("Sheet1")

furthermore the code can be as simplified as follows

Option Explicit

Sub ADDCLM()

Worksheets("Sheet1").Range("E3:E13").FormulaR1C1 = "=Vlookup(RC1,R3C8:R13C9,2,False)"
MsgBox "Done"

End Sub

or, if you want to keep values only in E3:E13

Option Explicit

Sub ADDCLM2()

With Worksheets("Sheet1").Range("E3:E13")
    .FormulaR1C1 = "=Vlookup(RC1,R3C8:R13C9,2,False)"
    .Value = .Value
End With
MsgBox "Done"

End Sub

Upvotes: 2

Rosetta
Rosetta

Reputation: 2725

table1 and table2 are declared as range "object". to assign a value to an "object" you have to use set, i.e.

set Table1 = Sheet1.Range("A3:A13")`
set Table2 = Sheet1.Range("H3:I13")

you don't need to use set for Dept_Row and Dept_clm because they are merely assigned a "value".

cl should be declared as range, because of you are calling For Each cl In Table1.

HTH.

Upvotes: 1

Related Questions