Index/Match in VBA

[nimish.dixit]

I havea fairly Complex index match that is used to prepopulate a
schedule. I am pulling names into a column from another sheet based on
a job classification.

The index/match formula looks something like this:

IF(INDEX(zSchedule!$P$1:$P$600, MATCH($D4,
zSchedule!$M$1:$M$600,0),1)=J$3, INDEX(zSchedule!$C$1:$C$600,
MATCH($D4, zSchedule!$M$1:$M$600,0),1), "")

J$3 verifies the job classification (it is the heading of a column in
the sheet - so a cell reference here).

Is there anyone that help me indentify how to write his in VBA? I want
to use it in a macro so my employees can paste in the formula to
visible cells and then hard value it.

 

[Bernie Deitrick]

Selection.Formula = "=IF(INDEX(zSchedule!$P$1:$P$600, " & _
"MATCH($D" & Selection.Row & ",zSchedule!$M$1:$M$600,0),1)=" & _
Cells(3, Selection.Column).Address(True, False) & _
", INDEX(zSchedule!$C$1:$C$600, MATCH($D" & _
Selection.Row & ", zSchedule!$M$1:$M$600,0),1), """")"


This assumes that formula in the upper left of the selection should refer to column D of the same
row (the $D4), and to the same column, row 3 (The J$3), so the formula as given in your example,
will be produced if the upper left of the selection is cell J4.... so the formula in J4 (produced by
the code above) is

=IF(INDEX(zSchedule!$P$1:$P$600, MATCH($D4,zSchedule!$M$1:$M$600,0),1)=J$3,
INDEX(zSchedule!$C$1:$C$600, MATCH($D4, zSchedule!$M$1:$M$600,0),1), "")

HTH,
Bernie
MS Excel MVP

 

[Nimish]

Thank you!

Selection.Formula = "=IF(INDEX(zSchedule!$P$1:$P$600, " & _
"MATCH($D" & Selection.Row & ",zSchedule!$M$1:$M$600,0),1)=" & _
Cells(3, Selection.Column).Address(True, False) & _
", INDEX(zSchedule!$C$1:$C$600, MATCH($D" & _
Selection.Row & ", zSchedule!$M$1:$M$600,0),1), """")"


This assumes that formula in the upper left of the selection should refer to column D of the same
row (the $D4), and to the same column, row 3 (The J$3), so the formula as given in your example,
will be produced if the upper left of the selection is cell J4.... so the formula in J4 (produced by
the code above) is

=IF(INDEX(zSchedule!$P$1:$P$600, MATCH($D4,zSchedule!$M$1:$M$600,0),1)=J$3,
INDEX(zSchedule!$C$1:$C$600, MATCH($D4, zSchedule!$M$1:$M$600,0),1), "")

HTH,
Bernie
MS Excel MVP

 

[Bernie Deitrick]

You're welcome... I'm guessing it worked OK ;-)

Bernie
MS Excel MVP