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 ,
mzehr 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 ,
mzehr 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