searching for vlookup equivalent in VBA
I didn't look through all you code, but you can use vlookup directly in VBA
.. . . dim res as Variant res = Application.Vlookup(target, Range("A1:C30"),2,False) if not iserror(res) then msgbox res & " was returned" else msgbox Target & " was not found" End if -- Regards, Tom Ogilvy "benb" wrote in message ... I am trying to right a macro that will find what new trades that have been added to an existing table, and then plug the ID for any new trades into a separate table. To do this I have four tables to work with. The first contains all of the trade ID's as of today. The second is the same table as of yesterday. The third contains more information related to each trade (e.g. counterparty and portfolio). Finally, the fourth table is where I want to plug in the new trade ID's belonging to a certain counterparty and portfolio. In Excel to do this manually I would use a series of Vlookups. I've tried to accomplish the same thing with If/Then statements and Do Until Loops, but for some reason it isn't working. I've spent two days already trying different variations on the theme with no success. Can anybody suggest some script to help? To try and rephrase: I need to look up a value in one table. If I find it then I move on to the next value. If I don't find then I need to lookup in another table the corresponding counterparty and portfolio. If it's from one portfolio, I need to plug the ID into one table. If it's from another portfolio, I need to plug it into a different one. If it's from neither then i just need to move on to the next value. Thanks. Here is some of the code that I have tried unsuccessfully: v1 = Sheets("Reconciliation").Cells(71, 10).Value v2 = Sheets("Reconciliation").Cells(71, 23).Value r = 4 x = 4 y = 4 For r = 4 To n If Sheets("CDS Reports").Rows(r).Cells(1).Value < "" Then Do Until _ Sheets("Testing").Rows(y).Cells(17).Value = Sheets("CDS Reports").Rows(r).Cells(1).Value _ Or Sheets("Testing").Rows(y).Cells(17).Value = "" y = y + 1 Loop If Sheets("Testing").Rows(y).Cells(17).Value = "" Then Do Until _ Sheets("Oasys Report").Rows(x).Cells(6).Value = Sheets("CDS Reports").Rows(r).Cells(1).Value x = x + 1 Loop If Sheets("Oasys Report").Rows(x).Cells(9) = "BOLSA" Then If Sheets("Oasys Report").Rows(x).Cells(4) = "FBISNL" Or _ Sheets("Oasys Report").Rows(x).Cells(4) = "FBISML" Then Sheets("Oasys Report").Rows(x).Cells(6).copy Sheets("Reconciliation").Cells(73 + v1, 11).PasteSpecial Paste:=xlPasteValues v1 = v1 + 1 End If If Sheets("Oasys Report").Rows(x).Cells(4) = "FBXSNL" Or _ Sheets("Oasys Report").Rows(x).Cells(4) = "FBXSML" Then Sheets("Oasys Report").Rows(x).Cells(6).copy Sheets("Reconciliation").Cells(73 + v2, 22).PasteSpecial Paste:=xlPasteValues v2 = v2 + 1 End If End If End If End If nxt: Next r But I started with something like this . . . For r = 4 To n If Cells(r, 1) < "" Then With rng Set fnd = .Find(Cells(r, 1), LookIn:=xlValues) End With If fnd Is Nothing Then 'With rngB 'Set fndB = .Find(Cells(r, 1), LookIn:=xlValues) 'End With 'If fndB Is Not Nothing Then Do Until Sheets("Testing").Rows(i).Cells(6) = Cells(r, 1) i = i + 1 If i m Then GoTo mssng Loop If Sheets("testing").Rows(i).Cells(9) = "BOLSA" Then If Sheets("testing").Rows(i).Cells(9) = "FBISNL" Or _ Sheets("testing").Rows(i).Cells(9) = "FBISML" Then Cells(r, 1).copy (Sheets("Reconciliation").Cells(72 + i, 11)) End If If Sheets("testing").Rows(i).Cells(9) = "FBXSNL" Or _ Sheets("testing").Rows(i).Cells(9) = "FBXSML" Then Cells(r, 1).copy (Sheets("Reconciliation").Cells(72 + i, 22)) End If End If mssng: MsgBox "Trade " & Cells(r, 1) & " is appearing in CDS but not Oasys", Title:="CHECK THIS TRADE" End If End If Next r "benb" wrote: Manage Your Profile ©2004 Microsoft Corporation. All rights reserved. Terms of Use |Trademarks |Privacy Statement |
All times are GMT +1. The time now is 04:29 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com