Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old August 31st 04, 06:20 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 27,285
Default 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




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Searching specific record using VLOOKUP function. dev Excel Worksheet Functions 12 July 4th 08 11:51 PM
Value searching in columns - vlookup can not do it? Barney W Excel Discussion (Misc queries) 4 November 18th 07 08:32 PM
VLookup Not Searching the entire cell for Match LAP Excel Discussion (Misc queries) 3 February 23rd 07 06:40 PM
How do I create an equivalent VLOOKUP function using FIND? dan Excel Worksheet Functions 8 August 17th 05 04:43 PM
VB's equivalent to VLOOKUP? Dan Excel Programming 4 June 9th 04 08:47 PM


All times are GMT +1. The time now is 02:25 PM.

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017