Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
A Macro to autofind a value in a range
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
A Macro to autofind a value in a range
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 "?B?RGF2ZQ==?=" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
A Macro to autofind a value in a range
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 ------------------------------------------- "Dave" wrote in 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. Help please. Thanks |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
A Macro to autofind a value in a range
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. Help please. Thanks . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
A Macro to autofind a value in a range
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. "Dave" wrote in 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. Help please. Thanks |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
A Macro to autofind a value in a range
Thanks all, some very useful advise here, gives me a few more option to play with :)
"Dave" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macro range | Excel Discussion (Misc queries) | |||
Macro deletes row in range, macro then skips the row moved up | Excel Discussion (Misc queries) | |||
Set Range in one macro to range in another | Excel Programming | |||
Macro to input formula in range based on another range | Excel Programming | |||
Macro to name a range | Excel Programming |