Index and Match in Visual Basic

[daniroy]

Hello everybody, and first of all, thank you for the help I receive
here.

I have to come back on the question I did raise yesterday.

A few days ago, I did receive very good help from Max and Dave to
achieve the following formula that I do array-enter:

{=INDEX(AUTOS!$K$10:$K$500,MATCH(1,(Autos!$F$10:$F$500='ID'!D5)
*(Autos!$G$10:$G$400='ID'!$R$2),0))}

This formula is working just fine. I enter it in sheet("ID"), lets
say cells(I6).

I now want and need not to use Formulas anymore, but to code them in
VB.

______________________________________

Sub GetPrice()

Dim Price1 as Single

Price1 = unknow code

Cell("I6").Value = Price 1
______________________________________


To make myself clearer, my spreadsheet("Autos") is built has follow


Column F Column G Column K
Dates Reference Price
16/09/2005 52 48
16/09/2005 53 46
16/09/2005 54 44
16/09/2005 55 38
16/09/2005 56 36
21/10/2005 52 72
21/10/2005 53 64
21/10/2005 54 59
15/11/2005 55 55
15/11/2005 56 51

And basically I need to retrieve a Price meeting both Dates and
Reference criteria. And to setup this price as a Variable I store in VB
and display in my Sheet.

Any help will be appreciated, as I do not have the VB knowledge yet to
create it on my own. Thanks in advance.

Best regards
Daniel ROY

 

[Bob Phillips]

My suggestion no good then?

 

[daniroy]

Hi Bob,

sorry to have been late to answer: unfortunatelly, I do not want to
calculate the data in the spreadsheet anymore but really in VBA then
input the results are datas, not formulas.
Thank you for the suggestion, I did believe it would be fine but it was
not!
Best regards
Daniel

 

[Dave Peterson]

You could loop through the rows looking for a match or you could plop the
formula into a cell and convert to values (like Bob suggested).

But you could just evaluate your formula inside your code, too:

Option Explicit
Sub testme()

Dim myFormula As String
myFormula = "INDEX(AUTOS!$K$10:$K$500," & _
"MATCH(1,(Autos!$F$10:$F$500='ID'!D5)" & _
"*(Autos!$G$10:$G$400='ID'!$R$2),0))"
MsgBox Application.Evaluate(myFormula)

End Sub

Since you qualified each range with its worksheet, I used application.evaluate.

But if you had used D5 instead of 'ID'!D5, you'd want to make sure that you used
the worksheet that held D5 in the .evaluate statement.

If D5 were on Sheet99:

Option Explicit
Sub testme()

Dim myFormula As String
myFormula = "INDEX(AUTOS!$K$10:$K$500," & _
"MATCH(1,(Autos!$F$10:$F$500=D5)" & _
"*(Autos!$G$10:$G$400='ID'!$R$2),0))"
MsgBox worksheets("sheet99").Evaluate(myFormula)

End Sub

 

[daniroy]

thanks a lot, I am gonna have a look asap!
thanks Bob, thanks Dave for your inputs!
regards
DR

 

[daniroy]

looks like brillant ! thanks again! you are absolutely helpfull!

DR