syed_iqbal
04-01-2017, 11:01 PM
Hi,
In the below code, First "For Loop" giving no result. Pls help me
Sub TEAMUDPATE()
Dim lr1, lr2, lr3, lr4, uaplr, utslr As Long
On Error Resume Next
Application.DisplayAlerts = False
Workbooks.Open FileName:=ActiveWorkbook.Path & "\" & "userinformation-AP.xlsx"
On Error Resume Next
Workbooks.Open FileName:=ActiveWorkbook.Path & "\" & "userinformation-TS.xlsx"
lr1 = Workbooks("Team Update.xlsm").Sheets("TS").Cells(Rows.Count, 1).End(xlUp).Row
lr2 = Workbooks("Team Update.xlsm").Sheets("TS1").Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lr1
Workbooks("Team Update.xlsm").Sheets("TS").Cells(i, 10).Value = WorksheetFunction.IfError(Application.WorksheetFunction.VLookup(Workbooks("Team Update.xlsm").Sheets("TS").Cells(i, 2).Value, Workbooks("userinformation-AP.xlsx").Sheets("AP").Range("b:K"), 9, 0), _
Application.WorksheetFunction.VLookup(Workbooks("Team Update.xlsm").Sheets("TS").Cells(i, 2).Value, Workbooks("userinformation-TS.xlsx").Sheets("TS").Range("b:K"), 9, 0))
Workbooks("Team Update.xlsm").Sheets("TS").Cells(i, 11).Value = WorksheetFunction.IfError(Application.WorksheetFunction.VLookup(Workbooks("Team Update.xlsm").Sheets("TS").Cells(i, 2).Value, Workbooks("userinformation-AP.xlsx").Sheets("AP").Range("b:K"), 10, 0), _
Application.WorksheetFunction.VLookup(Workbooks("Team Update.xlsm").Sheets("TS").Cells(i, 2).Value, Workbooks("userinformation-TS.xlsx").Sheets("TS").Range("b:K"), 10, 0))
Next i
For i = 2 To lr2
Workbooks("Team Update.xlsm").Sheets("TS1").Cells(i, 10).Value = Application.WorksheetFunction.VLookup(Workbooks("Team
Update.xlsm").Sheets("TS1").Cells(i, 2).Value, Workbooks("userinformation-TS.xlsx").Sheets("TS").Range("b:K"), 9, 0)
Workbooks("Team Update.xlsm").Sheets("TS1").Cells(i, 11).Value = Application.WorksheetFunction.VLookup(Workbooks("Team Update.xlsm").Sheets("TS1").Cells(i, 2).Value, Workbooks("userinformation-TS.xlsx").Sheets("TS").Range("b:K"), 10, 0)
Next i
End Sub
In the below code, First "For Loop" giving no result. Pls help me
Sub TEAMUDPATE()
Dim lr1, lr2, lr3, lr4, uaplr, utslr As Long
On Error Resume Next
Application.DisplayAlerts = False
Workbooks.Open FileName:=ActiveWorkbook.Path & "\" & "userinformation-AP.xlsx"
On Error Resume Next
Workbooks.Open FileName:=ActiveWorkbook.Path & "\" & "userinformation-TS.xlsx"
lr1 = Workbooks("Team Update.xlsm").Sheets("TS").Cells(Rows.Count, 1).End(xlUp).Row
lr2 = Workbooks("Team Update.xlsm").Sheets("TS1").Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lr1
Workbooks("Team Update.xlsm").Sheets("TS").Cells(i, 10).Value = WorksheetFunction.IfError(Application.WorksheetFunction.VLookup(Workbooks("Team Update.xlsm").Sheets("TS").Cells(i, 2).Value, Workbooks("userinformation-AP.xlsx").Sheets("AP").Range("b:K"), 9, 0), _
Application.WorksheetFunction.VLookup(Workbooks("Team Update.xlsm").Sheets("TS").Cells(i, 2).Value, Workbooks("userinformation-TS.xlsx").Sheets("TS").Range("b:K"), 9, 0))
Workbooks("Team Update.xlsm").Sheets("TS").Cells(i, 11).Value = WorksheetFunction.IfError(Application.WorksheetFunction.VLookup(Workbooks("Team Update.xlsm").Sheets("TS").Cells(i, 2).Value, Workbooks("userinformation-AP.xlsx").Sheets("AP").Range("b:K"), 10, 0), _
Application.WorksheetFunction.VLookup(Workbooks("Team Update.xlsm").Sheets("TS").Cells(i, 2).Value, Workbooks("userinformation-TS.xlsx").Sheets("TS").Range("b:K"), 10, 0))
Next i
For i = 2 To lr2
Workbooks("Team Update.xlsm").Sheets("TS1").Cells(i, 10).Value = Application.WorksheetFunction.VLookup(Workbooks("Team
Update.xlsm").Sheets("TS1").Cells(i, 2).Value, Workbooks("userinformation-TS.xlsx").Sheets("TS").Range("b:K"), 9, 0)
Workbooks("Team Update.xlsm").Sheets("TS1").Cells(i, 11).Value = Application.WorksheetFunction.VLookup(Workbooks("Team Update.xlsm").Sheets("TS1").Cells(i, 2).Value, Workbooks("userinformation-TS.xlsx").Sheets("TS").Range("b:K"), 10, 0)
Next i
End Sub