malleshg24
08-20-2017, 07:43 AM
Hi Team,
I have workbook (1) which has trade id in different sheets in column A. and
I have set of historic trade id in workbook 2 sheet1 Colulmn (A) .
In office my daily activity is to find out how many new trades processed and historic trades Processed.
So I apply a Vlookup in workbook 1 sheets, in column B, if the vlookup result shows N/A Then those are New trades.
else historic trades. Below code is working for me and its currently giving me the result Either N/A , exact match.
Please suggest me the extra syntax , for N/A Result it should display "New Trades" & For exact match " Historic Trades"
Also suggest any suggestion in my below code. Thanks.:banghead::crying::help
Option Explicit
Sub Deferentiate_Historic_and_NewTrades()
Dim wbk1 As Workbook
Dim wbk2 As Workbook
Dim MyData As Range
Dim lr As Long
Dim ws As Worksheet
Set wbk1 = Workbooks.Open(Sheet1.Range("B5").Value) 'E:\MIS\STVB004\Todays Report\Daily report August 15.xlsx
Set wbk2 = Workbooks.Open("F:\Mallesh\Historic trades data file\Historic trades.xlsx")
wbk2.Sheets("Sheet1").Range("A1:A25000").Name = "MyData"
Set MyData = ThisWorkbook.Sheets("Sheet1").Range("A1:A25000")
Application.ScreenUpdating False
For Each ws In wbk1.Worksheets
Select Case ws.Name
Case "Sheet1", "Sheet2", "Sheet4", "Sheet6"
ws.Range("B1").EntireColumn.Insert
ws.Range("B1").Value = "Historic/New"
lr = ws.Cells(Rows.Count, "A").End(xlUp).Row
ws.Range("B2:B" & lr).Formula = "=VLOOKUP(A2,'[" & wbk2.Name & "]sheet1'!$A$1:$A$10000,1,false)"
End Select
Next ws
Application.ScreenUpdating True
End Sub
I have workbook (1) which has trade id in different sheets in column A. and
I have set of historic trade id in workbook 2 sheet1 Colulmn (A) .
In office my daily activity is to find out how many new trades processed and historic trades Processed.
So I apply a Vlookup in workbook 1 sheets, in column B, if the vlookup result shows N/A Then those are New trades.
else historic trades. Below code is working for me and its currently giving me the result Either N/A , exact match.
Please suggest me the extra syntax , for N/A Result it should display "New Trades" & For exact match " Historic Trades"
Also suggest any suggestion in my below code. Thanks.:banghead::crying::help
Option Explicit
Sub Deferentiate_Historic_and_NewTrades()
Dim wbk1 As Workbook
Dim wbk2 As Workbook
Dim MyData As Range
Dim lr As Long
Dim ws As Worksheet
Set wbk1 = Workbooks.Open(Sheet1.Range("B5").Value) 'E:\MIS\STVB004\Todays Report\Daily report August 15.xlsx
Set wbk2 = Workbooks.Open("F:\Mallesh\Historic trades data file\Historic trades.xlsx")
wbk2.Sheets("Sheet1").Range("A1:A25000").Name = "MyData"
Set MyData = ThisWorkbook.Sheets("Sheet1").Range("A1:A25000")
Application.ScreenUpdating False
For Each ws In wbk1.Worksheets
Select Case ws.Name
Case "Sheet1", "Sheet2", "Sheet4", "Sheet6"
ws.Range("B1").EntireColumn.Insert
ws.Range("B1").Value = "Historic/New"
lr = ws.Cells(Rows.Count, "A").End(xlUp).Row
ws.Range("B2:B" & lr).Formula = "=VLOOKUP(A2,'[" & wbk2.Name & "]sheet1'!$A$1:$A$10000,1,false)"
End Select
Next ws
Application.ScreenUpdating True
End Sub