Solved: Index/Match Formular to VBA conversion

[RECrerar]

Hey I'm having trouble converting a formular to VBA.

Currently I have:




Dim FormularE, Bus, WhatToFind as string
Dim E as single

FormularE = "INDEX(E$2:E$100,MATCH(1,(A$2:A$100=" & Bus & ")*(D$2:D$100=" & WhatToFind & "),0))"

With PanelSheet
E = .Evaluate(formularE)
End With

Which is supposed to look up the value in column E in the same row a that Bus and WhatToFind are in.

When I first tried to evaluate it, I was getting E = Error 2015.

I thought this might be because Bus can sometimes be a string containing a space, so I changed the value of Bus so that there were no spaces and re-ran the formular. I am now getting E = Error 2042.

Any idea what I have done wrong?

Additional information that may or may not be useful:

1. There is data validatation in column D, allowing only certain strings to be entered.

2. The table containing the data does not extend to row 100, but may expand, hence the larger ranges

3. There is definately a match

4. I would like ideally to be able to define Bus as strings with gaps in them, but am happy to lose this ability if it will make things simpler.

PS I know I've spelt Formula wrong - may get around to changing it at some point

[Bob Phillips]

If Bus is a string



Dim FormularE, Bus, WhatToFind as string
Dim E as single

FormularE = "INDEX(E$2:E$100,MATCH(1,(A$2:A$100=""" & Bus & """)*(D$2:D$100=" & WhatToFind & "),0))"

With PanelSheet
E = .Evaluate(formularE)
End With

[RECrerar]

You're a star xld, I know it was something like that, but couldn't work outt the right location of the extra quotes. thanks