PDA

View Full Version : [SOLVED] VBA VLOOKUP ON Historic trades



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

SamT
08-20-2017, 10:14 AM
Not sure, but I think this will work

Formula = "=IF ISERROR(VLOOKUP(A2,'[" & wbk2.Name & "]sheet1'!$A$1:$A$10000,1,false),"New","Historic"")
Watch those Quotes, I am easily confused

Bob Phillips
08-20-2017, 10:40 AM
Not sure, but I think this will work

Formula = "=IF ISERROR(VLOOKUP(A2,'[" & wbk2.Name & "]sheet1'!$A$1:$A$10000,1,false),"New","Historic"")
Watch those Quotes, I am easily confused

You mean


.Formula = "=IF(ISERROR(VLOOKUP(A2,'[" & wbk2.Name & "]Sheet1'!$A$1:$A$10000,1,False)),""New"",""Historic"")"

malleshg24
08-20-2017, 10:46 AM
Hello Sir,

Your code worked successfully, I am too glad now , Thanks both of you for your precious time on my question !!!

Regards,
Mallesh

SamT
08-20-2017, 11:45 AM
xld, That was for you to know and OP to figger out. :D

malleshg24
08-21-2017, 11:16 AM
Hi Team,

ws.Range("B2:B" & lr).Formula = .Formula = "=IF(ISERROR(VLOOKUP(A2,'[" & wbk2.Name & "]Sheet1'!$A$1:$A$10000,1,False)),""New"",""Historic"")"

The above formula works for me, however when I checked the result I see the formula also, what line I need to add in looping so that only values gest pasted.

Thanks in advance !!!


Regards,
Mallesh

SamT
08-21-2017, 12:41 PM
If you only want Values, why are you using a Formula?


ws.Range("B2:B" & lr).Formula= ...........
ws.Calculate
ws.Range("B2:B" & lr).Copy
ws.Range("B2:B" & lr).PasteSpecial xlPasteValues