Find and Update Value

[smartguy]

Hello all,

I have excel file in the below .

Excel Workbook
	C	D	E	F	G	H	I
1	Student				Mark1	Mark2	Mark3
2	1
3	2
4	3
5	4
6	5
7	6
8	7
9	8
10	9
11	10
Sheet1

Sheet 2 i Have data.

Excel Workbook
	E	F	G	H
1	Student	Mark1	Mark2	Mark3
2	2	101	20	20
3	3	23	21	22
4	4	34	23	24
5	5	576	25	26
6	6	22	234	345
7	7	2	232	90
Sheet2

I want to updated data in to sheet1.

Answer :

Excel Workbook
	C	D	E	F	G	H	I
1	Student				Mark1	Mark2	Mark3
2	1
3	2				101	20	20
4	3				23	21	22
5	4				34	23	24
6	5				576	25	26
7	6				22	234	345
8	7				2	232	90
9	8
10	9
11	10
Sheet1

I am looking for VBA Code....

 

[Weaver]

Any particular reason to use vba?

Looks like a job for Vlookup

 

[VBACO]

I have to agree with Weaver here. There are much better methods of achieving this goal without using VBA here.

 

[smartguy]

Yes,

I am working on Project. All the part done in VBA. So i looking for VBA Code.

Thanks

 

[Weaver]

Sub vlookupPlus()
    Dim sRange, dRange
    Application.ScreenUpdating = False
    With Sheets("sheet2")
        Set sRange = .Range("A2", .Cells(Rows.Count, 1).End(xlUp)).Resize(, 4)
    End With
    With Sheets("sheet1")
        Set dRange = .Range("G2", .Cells(Rows.Count, 3).End(xlUp).Offset(, 4)).Resize(, 3)
    End With
    With dRange
        .FormulaR1C1 = "=VLOOKUP(RC3,Sheet2!" & sRange.Address(, , 0) & ",COLUMN(Sheet2!R1C[-5]),0)"
        .Value = .Value
        .Replace "#n/a", ""
    End With
End Sub

 

[smartguy]

Hello ,

The Above code is not working....

 

[Weaver]

Hello ,

The Above code is not working....

From the fact that I wouldn't have posted it if it didn't work on my machine, you can assume that it did. Therefore a more descriptive response is required if you expect me to do anything about it.

 

[smartguy]

Macro Run...

But Value shows "Blank".

Regards

 

[Weaver]

Try this slight variation.

Sub vlookupPlus()
    Dim sRange, dRange
    Application.ScreenUpdating = False
    With Sheets("sheet2")
        Set sRange = .Range("E2", .Cells(Rows.Count, 5).End(xlUp)).Resize(, 4)
    End With
    With Sheets("sheet1")
        Set dRange = .Range("G2", .Cells(Rows.Count, 3).End(xlUp).Offset(, 4)).Resize(, 3)
    End With
    With dRange
        .FormulaR1C1 = "=VLOOKUP(RC3,Sheet2!" & sRange.Address(, , 0) & ",COLUMN(Sheet2!R1C[-5]),0)"
        .Value = .Value
        .Replace "#n/a", ""
    End With
End Sub

 

[VBACO]

The error is because Weaver started sheet 2 in Column A and smartguy is starting in Column E