vlook up-vba macro code for more than one column

[Atul2582]

I have 2 sheets, sheet2 have data consist of 5 columns I want to vlook up by vba code so the data from sheet2 with reference here is (ID) search in sheet1 and update the data from sheet 2 to sheet 1 like below. i have found a similar code but it updates only one column that is B cloumn i want to update C, D, E columns and rows by the below code, could anybody help in this. see the below code.

Sheet 2
Id, beginning, principal, interest, endbalance
123, 10000, 1000, 50 , 9000
789, 8000 , 400 , 150, 7600
245, 5000 , 1000, 200 , 4000
456, 4000 , 1000, 1000 , 3000
789, 2500 , 500 , 100 , 2000

Sheet1
id beginning principal interest endbalance
123 10000
789 8000
245 5000
456 4000
786 2500
Ifound below code suitable for the above query but this is only execute column b , I want to execute c, d, e columns how can I do this.
Sub ADDCLM()
On Error Resume Next
Dim Dept_Row As Long
Dim Dept_Clm As Long
ctr = 0
Table1 = Sheet1.Range("A3:A10") ' Employee_ID Column from Employee table
Table2 = Sheet2.Range("a3:e10") ' Range of Employee Table 1
Dept_Row = Sheet1.Range("b3").Row '
Dept_Clm = Sheet1.Range("b3").Column
For Each cl In Table1
Sheet1.Cells(Dept_Row, Dept_Clm).FormulaR1C1 = "=VLOOKUP(RC[-1],sheet2!C[-1]:c,2, False)"
Dept_Row = Dept_Row + 1
ctr = ctr + 1
Next cl
MsgBox "Done"
End Sub

 

[JLeno]

Couldn't you just use Index Match?

Assuming the headers are in A1:E1, and the ID numbers in A2:A6, use the following formula:

=INDEX(Sheet1!$B$2:$E$6,MATCH($A2,Sheet1!$A$2:$A$6,0),MATCH(B$1,Sheet1!$B$1:$E$1,0))

Does that help you?

 

[VoG]

Cross posted vlook up-vba macro code for more than one column

 

[Atul2582]

Couldn't you just use Index Match?

Assuming the headers are in A1:E1, and the ID numbers in A2:A6, use the following formula:

=INDEX(Sheet1!$B$2:$E$6,MATCH($A2,Sheet1!$A$2:$A$6,0),MATCH(B$1,Sheet1!$B$1:$E$1,0))

Does that help you?

Thanks for this solution however in my earlier code where i can fit your given code also I have done the code for B column I only need to repeat the same code for C D E where should i amend my code. could you give any idea
example i have code like this for B column in sheet1======== Sheet1.Cells(Dept_Row, Dept_Clm).FormulaR1C1 = "=VLOOKUP(RC[-1],sheet2!C[-1]:c,2, False)"
if i repeat the same line four times the code does not work its show a "fxd" error.

 

[Atul2582]

Thanks for this solution however in my earlier code where i can fit your given code also I have done the code for B column I only need to repeat the same code for C D E where should i amend my code.

I have code like this for B column in sheet1 ====>>> Sheet1.Cells(Dept_Row, Dept_Clm).FormulaR1C1 = "=VLOOKUP(RC[-1],sheet2!C[-1]:c,2, False)"

I tried below code lines for C D E columns but it does not work it shows "FXD" error

if I repeat the same line for C, D & E columns, code does not work its show a "fxd" error.

Sheet1.Cells(Dept_Row, Dept_Clm).FormulaR1C1 = "=VLOOKUP(RC[-2],sheet2!C[-1]:c,3, False)"

Sheet1.Cells(Dept_Row, Dept_Clm).FormulaR1C1 = "=VLOOKUP(RC[-3],sheet2!C[-1]:c,4, False)"

Sheet1.Cells(Dept_Row, Dept_Clm).FormulaR1C1 = "=VLOOKUP(RC[-4],sheet2!C[-1]:c,5, False)"

<colgroup><col></colgroup><tbody>
</tbody>

 

[Atul2582]

Thanks for this solution however in my earlier code where i can fit your given code also I have done the code for B column I only need to repeat the same code for C D E where should i amend my code.
I have code like this for B column in sheet1 ====>>> Sheet1.Cells(Dept_Row, Dept_Clm).FormulaR1C1 = "=VLOOKUP(RC[-1],sheet2!C[-1]:c,2, False)"
I tried below code lines for C D E columns but it does not work it shows "FXD" error
if I repeat the same line for C, D & E columns, code does not work its show a "fxd" error.
Sheet1.Cells(Dept_Row, Dept_Clm).FormulaR1C1 = "=VLOOKUP(RC[-2],sheet2!C[-1]:c,3, False)"
Sheet1.Cells(Dept_Row, Dept_Clm).FormulaR1C1 = "=VLOOKUP(RC[-3],sheet2!C[-1]:c,4, False)"
Sheet1.Cells(Dept_Row, Dept_Clm).FormulaR1C1 = "=VLOOKUP(RC[-4],sheet2!C[-1]:c,5, False)"

 

[JLeno]

It's not really clear for me why you would want to have this in a VBA code, but i guess that's fine. Furthermore I would suggest Index-Match instead of Vlookup. You could use the following:

Sub ADDCLM()
On Error Resume Next
Dim Table1 As Range
Set Table1 = Sheet1.Range("B3:E10") ' Employee_ID Column from Employee table
Table1.Formula = "=INDEX(Sheet2!R2C2:R6C5,MATCH(RC1,Sheet2!R2C1:R6C1,0),MATCH(R2C,Sheet2!R1C2:R1C5,0))"
MsgBox "Done"
End Sub

Or if you want to use Vlookup:

Sub ADDCLM()
On Error Resume Next
Dim Table1 As Range
Dim cl As Range
Set Table1 = Sheet1.Range("B3:E10") ' Employee_ID Column from Employee table
For Each cl In Table1
    cl.FormulaR1C1 = "=VLOOKUP(RC1,Sheet2!R1C1:R6C5," & cl.Column & ",FALSE)"
Next cl
MsgBox "Done"
End Sub

Cheers!

 

[Atul2582]

It's not really clear for me why you would want to have this in a VBA code, but i guess that's fine. Furthermore I would suggest Index-Match instead of Vlookup. You could use the following:

Sub ADDCLM()
On Error Resume Next
Dim Table1 As Range
Set Table1 = Sheet1.Range("B3:E10") ' Employee_ID Column from Employee table
Table1.Formula = "=INDEX(Sheet2!R2C2:R6C5,MATCH(RC1,Sheet2!R2C1:R6C1,0),MATCH(R2C,Sheet2!R1C2:R1C5,0))"
MsgBox "Done"
End Sub

Or if you want to use Vlookup:

Sub ADDCLM()
On Error Resume Next
Dim Table1 As Range
Dim cl As Range
Set Table1 = Sheet1.Range("B3:E10") ' Employee_ID Column from Employee table
For Each cl In Table1
    cl.FormulaR1C1 = "=VLOOKUP(RC1,Sheet2!R1C1:R6C5," & cl.Column & ",FALSE)"
Next cl
MsgBox "Done"
End Sub

Cheers!

Amazing reply from you you are a master, you done a great work to solve my query. Thanks a lot.i will solve some more queries by using your logic.

 

[Atul2582]

Amazing reply from you you are a master, you done a great work to solve my query. Thanks a lot.i will solve some more queries by using your logic.