application.vlookup

[ImboJones]

I have a user form with a combo box of fund names (cmbFnd) and a text box assigned to the ISIN of that fund (txtISIN). the table holding fund names and ISINs is in a different workbook (S:\IAM Institutions\Institutional Master\Client - Product Matrix IB.xlsm) on the worksheet 'PerTrac Report', Range A6:B71 with A holding the Fund Name and B holding the ISIN and the column headings were in Row 3. When I select an entry from the combo box I would like it to lookup the respective ISIN from that other workbook and enter it into the text box. And I want it to do this without the other workbook being open. So far I get that i should probably be using application.vlookup but I've no idea about how it works.
Any help would be so greatly appreciated
Imi
Oh, if it helps, this used to be a nice nonVBA form in the client-product matrix spreadsheet and the respective formula was:
=VLOOKUP($D$17,'PerTrac Report'!$A$6:$K$71, MATCH("ISIN",'PerTrac Report'!$A$3:$K$3,0),FALSE)
where D17 held the fund name

[teylyn]

Hello,

You're probably after

Please Login or Register  to view this content.

The syntax is the same as in the worksheet function, but you can pass the arguments as variables.

cheers

[ImboJones]

thanks.
though i'm still not entirely sure where I'm putting this in my code, or how to reference a different workbook.

[romperstomper]

And I want it to do this without the other workbook being open.

that won't work with worksheetfunction.vlookup

You could use ADO to extract the data from the table, or you could enter the formula into a blank cell in a worksheet and then retrieve the value and delete the formula.

[ImboJones]

Does it make it easier if I then link the Pertrac Report to a hidden sheet in the workbook that holds my user form, effectively moving the table into the workbook (but so that it gets updated when the other workbook does.
So now I want to look up the Fund name chosen in cmbFnd from the sheet PertracReport in the same workbook and enter the relevant ISIN into txtISIN.
Is that possible?
TIA

[snb]

You can read the table in

S:\IAM Institutions\Institutional Master\Client - Product Matrix IB.xlsm on the worksheet 'PerTrac Report', Range A6:B71 in one move into your combobox

Please Login or Register  to view this content.

If you choose the fund's name in the combobox you can see the ISIN-code in textbox1 using

Please Login or Register  to view this content.

[ImboJones]

It looks promising but I'm getting
Run-time error '9':
Subscript out of range.

Any suggestions?
Thanks

[romperstomper]

Change:

Please Login or Register  to view this content.

to:

Please Login or Register  to view this content.

[ImboJones]

Ok I had a fiddle, but I'm still getting issues.
currently it's;

Please Login or Register  to view this content.

and then

Please Login or Register  to view this content.

and it comes up with
Run-time error '-2147024809 (80070057)';
Could not get the list property. Invalid argument.
I'm pretty certain it's something to do with the second bit because that happens after I've chosen from the combobox but I have no idea what's going on.
thanks

[snb]

Private Sub cmbFnd_Change()
If cmbFnd.ListIndex > -1 Then txtISIN.Text = cmbFnd.List(cmbFnd.ListIndex, 1)
End Sub

[ImboJones]

uh! that's amazing!
I have one last thing that might cause problems though. there are other textboxes that are dependent on the combo box and the information is stored in two different tables on two different worksheets in the client product matrix workbook. And one of the tables holds additional funds that aren't in the combo box list. is this a problem?
It's not too much of an issue if it is because i think the idea is to eventually get rid of the client product matrix and put all the information into pertrac which can then generate one report with all the required information in it. but we're not at that stage yet.
Thanks

[snb]

You can

- copy the data in the other sheet to .Sheets("PerTrac Report").Range("A72"), assuming also an 2-column array
and use
cmbFnd.List = .Sheets("PerTrac Report").Range("A6").currentregion.Value

- make another combobox in the userform that contains the data in that other sheet
cmbFnd.List = .Sheets("Othersheet").Range("A6").currentregion.Value

- combine both areas into using VBA
something like

Please Login or Register  to view this content.