Find function In a MACRO help.

bastardino · Mar 18th, 2002, 07:58 AM

I have a question there is simple in theory but hard in explanation. I shall try to do my best. I have an output cell that displays a result say in B5. Then I have a chart that has a number of values assigned, exactly parallell to that I have another chart of values assigned. What I need is to search for B5 in on the columns, once found then take the value from the adjasent column and place it in a designated cell. However, the further difficulty in this lays within a fact that B5 isn't always exactly the same as the chart, hence in that case it needs to locate the closest value to it. Any questions?

Any help would be greatly appreciated. My email is ashterental@hotmail.com if someone has any suggestions, i shall be checking here as well.

Thanks

Mark O'Brien · Mar 18th, 2002, 08:22 AM

Wow, if it doesn't find that exact value, you want it to find the closest match. It sounds like you need some kind of fuzzy match system. That's a hell of a project.

I would start by looking here:

http://www.mrexcel.com/fuzzy/fuzzy.shtml

bastardino · Mar 18th, 2002, 12:04 PM

Thanks, Actually I don't think it's quiet that complicated. My problem de4als with number and numbers only, there is no character involved. For example, a vlook up table is able to finjd numbers closest to , so why Can't i have something simular to that.

Thanks,

bastardino · Mar 18th, 2002, 12:04 PM

Thanks, Actually I don't think it's quiet that complicated. My problem de4als with number and numbers only, there is no character involved. For example, a vlook up table is able to finjd numbers closest to , so why Can't i have something simular to that.

Thanks,

Cosmos75 · Mar 18th, 2002, 03:50 PM

You mean something like this?

=VLOOKUP(B5,C1:D15,2,TRUE)

Change the range C1:D15 to your chart data table range. They are side by side, corect?
This works assuming that the column containing the value you are trying to lookup is in Column C, and the value you are trying to return in in D (the second column to the right of Column C, hence the 2 in thr foumla after the range)

I am not sure this is what you want or if I understood the question correctly..

[ This Message was edited by: Cosmos75 on 2002-03-18 14:54 ]

Juan Pablo González · Mar 18th, 2002, 07:54 PM

Quote:

On 2002-03-18 14:50, Cosmos75 wrote:
You mean something like this?

=VLOOKUP(B5,C1:D15,2,TRUE)

Change the range C1:D15 to your chart data table range. They are side by side, corect?
This works assuming that the column containing the value you are trying to lookup is in Column C, and the value you are trying to return in in D (the second column to the right of Column C, hence the 2 in thr foumla after the range)

I am not sure this is what you want or if I understood the question correctly..

[ This Message was edited by: Cosmos75 on 2002-03-18 14:54 ]

You could just use

=VLOOKUP(B5,C1:D15,2)

since VLOOKUP defaults to TRUE in the 4th argument.

Dave Hawley · Mar 18th, 2002, 08:00 PM

You can just use the VLOOKUP in your macro

MyResult=WorksheetFunction.VLOOKUP(22,Sheet2.Range("A1:B5000",2)

To do this efficiently and get the closest match your data MUST be sorted.

_________________
Kind Regards
Dave Hawley
OzGrid Business Applications
Microsoft Excel/VBA Training

[ This Message was edited by: Dave Hawley on 2002-03-18 19:01 ]

Mar 18th, 2002, 08:22 AM	#2
Mark O'Brien MrExcel MVP Join Date: Feb 2002 Location: Columbus, OH, USA Posts: 3,519	Wow, if it doesn't find that exact value, you want it to find the closest match. It sounds like you need some kind of fuzzy match system. That's a hell of a project. I would start by looking here: http://www.mrexcel.com/fuzzy/fuzzy.shtml __________________ Mark O'Brien Columbus Ohio Celtic Supporters Club

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 18th, 2002, 07:58 AM	#1
bastardino New Member Join Date: Mar 2002 Posts: 3	I have a question there is simple in theory but hard in explanation. I shall try to do my best. I have an output cell that displays a result say in B5. Then I have a chart that has a number of values assigned, exactly parallell to that I have another chart of values assigned. What I need is to search for B5 in on the columns, once found then take the value from the adjasent column and place it in a designated cell. However, the further difficulty in this lays within a fact that B5 isn't always exactly the same as the chart, hence in that case it needs to locate the closest value to it. Any questions? Any help would be greatly appreciated. My email is ashterental@hotmail.com if someone has any suggestions, i shall be checking here as well. Thanks

Mar 18th, 2002, 12:04 PM	#3
bastardino New Member Join Date: Mar 2002 Posts: 3	Thanks, Actually I don't think it's quiet that complicated. My problem de4als with number and numbers only, there is no character involved. For example, a vlook up table is able to finjd numbers closest to , so why Can't i have something simular to that. Thanks,

Mar 18th, 2002, 03:50 PM	#5
Cosmos75 Board Regular Join Date: Feb 2002 Location: Tulsa, OK Posts: 354	You mean something like this? =VLOOKUP(B5,C1:D15,2,TRUE) Change the range C1:D15 to your chart data table range. They are side by side, corect? This works assuming that the column containing the value you are trying to lookup is in Column C, and the value you are trying to return in in D (the second column to the right of Column C, hence the 2 in thr foumla after the range) I am not sure this is what you want or if I understood the question correctly.. [ This Message was edited by: Cosmos75 on 2002-03-18 14:54 ]

Mar 18th, 2002, 08:00 PM	#7
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	You can just use the VLOOKUP in your macro MyResult=WorksheetFunction.VLOOKUP(22,Sheet2.Range("A1:B5000",2) To do this efficiently and get the closest match your data MUST be sorted. _________________ Kind Regards Dave Hawley OzGrid Business Applications Microsoft Excel/VBA Training [ This Message was edited by: Dave Hawley on 2002-03-18 19:01 ]