Index/Match in VBA

  • Thread starter Thread starter nimish.dixit
  • Start date Start date
N

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.
 
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
 
Thank you!


Bernie said:
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
Click to expand...
 
Back
Top