Vlookup using lastrow its starting from B1 only where heading is available and not moving until last row

[suresh7860]

By defining Range vlookup its working fine but tried using Lastrow but its not working fine please help me with the correction.

1). Sub VlookupusingRange()
Dim Rng As Range
Set Rng = ActiveSheet.Range("B2:B17")


For Each Cell In Rng
If IsEmpty(Cell) Then
Cell.FormulaR1C1 = "=VLOOKUP(RC[-1],'[Emp Details.xlsb]Sheet1'!R1C1:R17C2,2,FALSE)"
End If
Next Cell

End Sub

Here formula is starting but from B1 where heading is available and its continuing until B2 only just one row, Please help me correct the code.

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

'source workbook?
Set sourceBook = Workbooks.Open("C:\My Documents\Book1.xlsx")

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

'Determine last row of source
With sourceSheet
SourceLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
With outputSheet
'Determine last row in col P
OutputLastRow = .Cells(.Rows.Count, "B").End(xlUp).Row

'Apply our formula
.Range("B2:B" & OutputLastRow).Formula = _
"=VLOOKUP(A2,'[" & sourceBook.Name & "]" & sourceSheet.Name & "'!$A$2:$B$" & SourceLastRow & ",2,0)"
End With

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

Source Data
[TABLE="width: 128"]
<colgroup><col style="width:48pt" span="2" width="64"> </colgroup><tbody>[TR]
[TD="class: xl63, width: 64"]Column A- Employee ID
[/TD]
[TD="class: xl63, width: 64"]Column B-Name
[/TD]
[/TR]
[TR]
[TD="align: right"]525
[/TD]
[TD] Suresh
[/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]234[/TD]
[TD="class: xl64"] Ramesh
[/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]245[/TD]
[TD="class: xl64"] Ganesh
[/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]2465[/TD]
[TD="class: xl64"] Venki
[/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]6244[/TD]
[TD="class: xl64"] Venkatesh
[/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]21533[/TD]
[TD="class: xl64"] Sarvana
[/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]452[/TD]
[TD="class: xl64"] Althaf
[/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]524[/TD]
[TD="class: xl64"] vinod
[/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]252[/TD]
[TD="class: xl64"] madhu
[/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]525[/TD]
[TD="class: xl64"] Suresh
[/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]524[/TD]
[TD="class: xl64"] vinod
[/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]234[/TD]
[TD="class: xl64"] Ramesh
[/TD]
[/TR]
[TR]
[TD]abc[/TD]
[TD="class: xl64"]abc
[/TD]
[/TR]
[TR]
[TD]bcd
[/TD]
[TD="class: xl64"]bcd
[/TD]
[/TR]
[TR]
[TD]a_1[/TD]
[TD="class: xl64"]avinash
[/TD]
[/TR]
[TR]
[TD]a_2[/TD]
[TD="class: xl64"]anand
[/TD]
[/TR]
</tbody>[/TABLE]

Outputsheet
Where i need the answer to be extracted its activework.sheet1
[TABLE="width: 84"]
<colgroup><col></colgroup><tbody>[TR]
[TD][TABLE="width: 157"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Column A -Employee Id
[/TD]
[TD]Column B -Name
[/TD]
[/TR]
[TR]
[TD="align: right"]525
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]234[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]245[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2465[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6244[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]21533[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]452[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]524[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]252[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]525[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]524[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]234[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]abc[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]bcd
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]a_1
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]a_2
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thank you experts!

 

[RickXL]

Hi,

To make it work I had to change this line:

 OutputLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

Column B only has the header row in it.

 

[suresh7860]

Hi Rick

Yes, silly mistake but made me work for long time.. thanks a lot for the catch

Have a great weekend!

 

[RickXL]

No problem.

Have a great weekend!

Thanks, and the same to you.