I have a worksheet in which a user will select a defined range somewhere in
column A, in which I want to put the following relative formula, (assuming
that the user is starting in cell A12):
=INDEX(Chart!$A:$A,MATCH($C12,Chart!$C:$C,0))
This formula would be copied down (or inserted in) to each cell in column A
within the user defined range.

I have a short subroutine with the following code:

Sub GL_Lookup()
Dim i As Variant
i = ActiveCell
For Each i In Selection
If i.Offset(0, 1).Value = "DR" Then
i.FormulaR1C1 = "=INDEX(Chart!A:A,MATCH(RC[2],Chart!C[2]:C[2],0))"
ElseIf i.Offset(0, 1).Value = "CR" Then
i.FormulaR1C1 = "=INDEX(Chart!A:A,MATCH(RC[2],Chart!C[2]:C[2],0))"
End If

Next
End Sub

The routine works well, and the formula looks correct in the individual
cells within the range, however each cell contains the error message #NAME?
I have to go into each cell, hit F2, then Enter, and the correct result show
up.
I must be missing something really simple in this and appreciate any help
someone can offer.

Thanks,
Mike

One way:

Dim rCell As Range
For Each rCell In Selection
If rCell.Offset(0, 1).Value = "DR" Then
rCell.FormulaR1C1 = "=INDEX(Chart!C1,MATCH(RC[2],Chart!C[2],0))"
ElseIf rCell.Offset(0, 1).Value = "CR" Then
rCell.FormulaR1C1 = "=INDEX(Chart!C1,MATCH(RC[2],Chart!C[2],0))"
End If


In article <59A70D1F-2A56-4BEC-BC9A-(E-Mail Removed)>,
mzehr <(E-Mail Removed)> wrote:

> I have a worksheet in which a user will select a defined range somewhere in
> column A, in which I want to put the following relative formula, (assuming
> that the user is starting in cell A12):
> =INDEX(Chart!$A:$A,MATCH($C12,Chart!$C:$C,0))
> This formula would be copied down (or inserted in) to each cell in column A
> within the user defined range.
>
> I have a short subroutine with the following code:
>
> Sub GL_Lookup()
> Dim i As Variant
> i = ActiveCell
> For Each i In Selection
> If i.Offset(0, 1).Value = "DR" Then
> i.FormulaR1C1 = "=INDEX(Chart!A:A,MATCH(RC[2],Chart!C[2]:C[2],0))"
> ElseIf i.Offset(0, 1).Value = "CR" Then
> i.FormulaR1C1 = "=INDEX(Chart!A:A,MATCH(RC[2],Chart!C[2]:C[2],0))"
> End If
>
> Next
> End Sub
>
> The routine works well, and the formula looks correct in the individual
> cells within the range, however each cell contains the error message #NAME?
> I have to go into each cell, hit F2, then Enter, and the correct result show
> up.
> I must be missing something really simple in this and appreciate any help
> someone can offer.
>
> Thanks,
> Mike

Fantastic! Thanks

"JE McGimpsey" wrote:

> One way:
>
> Dim rCell As Range
> For Each rCell In Selection
> If rCell.Offset(0, 1).Value = "DR" Then
> rCell.FormulaR1C1 = "=INDEX(Chart!C1,MATCH(RC[2],Chart!C[2],0))"
> ElseIf rCell.Offset(0, 1).Value = "CR" Then
> rCell.FormulaR1C1 = "=INDEX(Chart!C1,MATCH(RC[2],Chart!C[2],0))"
> End If
>
>
> In article <59A70D1F-2A56-4BEC-BC9A-(E-Mail Removed)>,
> mzehr <(E-Mail Removed)> wrote:
>
> > I have a worksheet in which a user will select a defined range somewhere in
> > column A, in which I want to put the following relative formula, (assuming
> > that the user is starting in cell A12):
> > =INDEX(Chart!$A:$A,MATCH($C12,Chart!$C:$C,0))
> > This formula would be copied down (or inserted in) to each cell in column A
> > within the user defined range.
> >
> > I have a short subroutine with the following code:
> >
> > Sub GL_Lookup()
> > Dim i As Variant
> > i = ActiveCell
> > For Each i In Selection
> > If i.Offset(0, 1).Value = "DR" Then
> > i.FormulaR1C1 = "=INDEX(Chart!A:A,MATCH(RC[2],Chart!C[2]:C[2],0))"
> > ElseIf i.Offset(0, 1).Value = "CR" Then
> > i.FormulaR1C1 = "=INDEX(Chart!A:A,MATCH(RC[2],Chart!C[2]:C[2],0))"
> > End If
> >
> > Next
> > End Sub
> >
> > The routine works well, and the formula looks correct in the individual
> > cells within the range, however each cell contains the error message #NAME?
> > I have to go into each cell, hit F2, then Enter, and the correct result show
> > up.
> > I must be missing something really simple in this and appreciate any help
> > someone can offer.
> >
> > Thanks,
> > Mike
>