Index/Match in VBA
 

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.

Index/Match in VBA
 

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


wrote in message
ups.com...
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.

Index/Match in VBA
 

Thank you!


Bernie Deitrick wrote:
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


wrote in message
ups.com...
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.

Index/Match in VBA
 

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

Bernie
MS Excel MVP


"Nimish" wrote in message
oups.com...
Thank you!


Bernie Deitrick wrote:
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


wrote in message
ups.com...
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.