A Macro to autofind a value in a range

[Dave]

OK, Ill do my best to describe what I am trying to achieve, all help appreciated, so lets set the scene!:

I have put together a table for viewing various exchange rates (roughly 30 rows by 30 columns to be precise) with a view to being able to automatically extract the correct rate from it in conjuction with a combobox as the means for doing this.

Worksheet 1 contains a 'select the currency from' combobox list and a 'select the currency to' combobox list

Worksheet 2 has the exchange rate table in it.

What I want to achieve is when an exchange rate is selected from the 2 combo boxes on worksheet 1 it triggers a formula to go to worksheet 2, read and publish the appropriate rate but I have run out of ideas to do this, help please!!

The logic I have been trying to apply is to name each of the rows and colums of figures in the table (Lets say for example the row of USD Dollar rates, USD and the column of UK Pound Rates, UKP) there will then be only one instance where those ranges contain the same cell, ie where they cross and that would give me the rate i need

I would use the comboboxes to give me the required currencies in the linked cells and then use the answers in the linked cells in someway to read the correct rate from the currency table.

Help please.

Thanks

 

[keepITcool]

try this (may contain typos)

Function Banker(sFrom$,sTo$)
dim tblRates as range,r,c

on error goto oops
set tblrange=thisworkbook.worksheets(1).Range("a1:z26")
with application.worksheetfunction
r = .match(sFrom,tblRates.columns(1),0)
c = .match(sTo ,tblRates.rows(1),0)
Banker=.index(tblrates,r,c)
end with
exit function
oops:
banker=cverr(xlErrValue)
end function

hth, keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

 

[Earl Kiosterud]

Dave,

The rate table is named RateTable, and includes the currency headings in the
left column and top row. The top row of the table is also named ToHeader,
and the left column is named FromHeader (the currencies are listed). There
are two list boxes from the Control Toolbox linked to cells B2 and B4 (From
currency and To). Their linked cells are B2 and B4 respectively. Their
list range is FromHeader and FromHeader (the list fill range doesn't seem to
work with horizontal ranges, and I presumed to put the same list of
currencies in From and TO).

=VLOOKUP(B2,RateTable,MATCH(B4,ToHeader,0)+1,FALSE)

I used list boxes, because the user does not need to type in any value -- he
can only select from those in the table.
--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

OK, Ill do my best to describe what I am trying to achieve, all help

appreciated, so lets set the scene!:

I have put together a table for viewing various exchange rates (roughly 30

rows by 30 columns to be precise) with a view to being able to automatically
extract the correct rate from it in conjuction with a combobox as the means
for doing this.

Worksheet 1 contains a 'select the currency from' combobox list and a

'select the currency to' combobox list

Worksheet 2 has the exchange rate table in it.

What I want to achieve is when an exchange rate is selected from the 2

combo boxes on worksheet 1 it triggers a formula to go to worksheet 2, read
and publish the appropriate rate but I have run out of ideas to do this,
help please!!

The logic I have been trying to apply is to name each of the rows and

colums of figures in the table (Lets say for example the row of USD Dollar
rates, USD and the column of UK Pound Rates, UKP) there will then be only
one instance where those ranges contain the same cell, ie where they cross
and that would give me the rate i need

I would use the comboboxes to give me the required currencies in the

linked cells and then use the answers in the linked cells in someway to read
the correct rate from the currency table.

 

[DavidC]

Try this I take it that the comboboxes are on sheet1, and
sheet 2 has the exchange rate table. It assumes that the
list of currencies in the first column is the same order
as the currencies in each of the 30 columns. Assign this
routine to the second combobox which is selected.:


Dim rg1 As String, rg2 As String

row1 = ActiveSheet.Range("A40").Value 'row reference
row2 = ActiveSheet.Range("A41").Value 'column reference

Worksheets("Sheet2").Activate
ActiveSheet.Cells(row1 + 1, 1).Select
ActiveCell.Offset(0, row2).Select

exRate = ActiveCell.Value

-----Original Message-----
OK, Ill do my best to describe what I am trying to

achieve, all help appreciated, so lets set the scene!:

I have put together a table for viewing various exchange

rates (roughly 30 rows by 30 columns to be precise) with a
view to being able to automatically extract the correct
rate from it in conjuction with a combobox as the means
for doing this.

Worksheet 1 contains a 'select the currency from'

combobox list and a 'select the currency to' combobox list

Worksheet 2 has the exchange rate table in it.

What I want to achieve is when an exchange rate is

selected from the 2 combo boxes on worksheet 1 it triggers
a formula to go to worksheet 2, read and publish the
appropriate rate but I have run out of ideas to do this,
help please!!

The logic I have been trying to apply is to name each of

the rows and colums of figures in the table (Lets say for
example the row of USD Dollar rates, USD and the column of
UK Pound Rates, UKP) there will then be only one instance
where those ranges contain the same cell, ie where they
cross and that would give me the rate i need

I would use the comboboxes to give me the required

currencies in the linked cells and then use the answers in
the linked cells in someway to read the correct rate from
the currency table.

 

[kcc]

First off, I would dump the big table for a simple list of exchange rates
in terms of one currency.
Since I'm in the US, I would tend to choose USD. The table would need a USD
to USD = 1
entry to work right. The exchange rate between any 2 currencies would be
the ratio of the to and from
USD entries. This way, a simple vlookup function could get the 2 exchange
rates based on the value
returned by combo boxes. The combo box from the control box can be assigned
inputs and outputs through the properties vs. the form controls that require
vba.

Basically you would need:
A list with 2 columns. Currency code in the first and the USD exchange rate
in the second
2 combo boxes that both referring to column 1 for the list of options in the
combo box and 2 different cells for the selection.
Finally, a cell with something like: = vlookup(combo out 1, list of rates,
2)/vlookup(combo out 2, list of rates, 2)
You may need to invert the formula depending on if the table is USD to X or
X to USD.

If you really want a square table, I would use a match function on the
column headings
to get the offset to use in a vlookup on the rows.

OK, Ill do my best to describe what I am trying to achieve, all help

appreciated, so lets set the scene!:

I have put together a table for viewing various exchange rates (roughly 30

rows by 30 columns to be precise) with a view to being able to automatically
extract the correct rate from it in conjuction with a combobox as the means
for doing this.

Worksheet 1 contains a 'select the currency from' combobox list and a

'select the currency to' combobox list

Worksheet 2 has the exchange rate table in it.

What I want to achieve is when an exchange rate is selected from the 2

combo boxes on worksheet 1 it triggers a formula to go to worksheet 2, read
and publish the appropriate rate but I have run out of ideas to do this,
help please!!

The logic I have been trying to apply is to name each of the rows and

colums of figures in the table (Lets say for example the row of USD Dollar
rates, USD and the column of UK Pound Rates, UKP) there will then be only
one instance where those ranges contain the same cell, ie where they cross
and that would give me the rate i need

I would use the comboboxes to give me the required currencies in the

linked cells and then use the answers in the linked cells in someway to read
the correct rate from the currency table.

 

[Dave]

Thanks all, some very useful advise here, gives me a few more option to play with