Dynamic INDEX lookup?

M

mpjohnston

Basically I am trying to run one statement then use the informatio
pulled from to complete a second statement.

The first statement is:
ActiveCell.FormulaR1C1
"=MATCH(""StricklandT"",qperiodagentperformance!C[-10],0)"

This will result in 206 - which will give me the cells below D206 an
A206

The second statement (in theory):
ActiveCell.FormulaR1C1
"=INDEX(qperiodagentperformance!D206:D13000,MATCH("Agen
Summary",qperiodagentperformance!A206:A13000,0))

See the worksheet has a listing of 20 or so Agents and under each agen
is their Agent Summary. So my theory is find where each agent start
then find their agent summary under it.

Any suggestions.
Thanks, Mik
 
M

mpjohnston

By the way the second statement does not work because in VBA it doesn
allow me to put the D206:D13000 syntax, and I do understand what synta
is require
 
H

Hank Scorpio

By the way the second statement does not work because in VBA it doesnt
allow me to put the D206:D13000 syntax, and I do understand what syntax
is required
Click to expand...

I haven't really examined the question of whether there's a better
approach than the one you have in mind, or even whether that will
really do what you want. However I CAN tell you that the only reason
for the second statement not working is that you're using the
FormulaR1C1 property instead of the Formula property:

Sub ActCellFormulas()

'You can't use THIS:

ActiveCell.FormulaR1C1 = _
"=INDEX(qperiodagentperformance!D206:D13000, " _
& "MATCH(""Agent Summary"", qperiodagentperformance!A206:A13000,0))"

ActiveCell.Offset(1, 0).Select

'But you CAN use THIS:
ActiveCell.Formula = _
"=INDEX(qperiodagentperformance!D206:D13000, " _
& "MATCH(""Agent Summary"", qperiodagentperformance!A206:A13000,0))"

End Sub
 
Top