Copy a R1C1 formula to a user defined range results #NAME? error

M

mzehr

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
 
J

JE McGimpsey

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
 
M

mzehr

Fantastic! Thanks

JE McGimpsey said:
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


mzehr said:
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
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Incorporating the User Profile location into a document template path 1
Code works but goes into endless loop and crashes 1
Find string from column A in Range("B2:F7") list the header of that column/s 5
Return BLANK is cell value sought is blank 13
Formula Array Help in VBA 3
Visio: copy text of a master shape to User-define Cells (Variable) 0
for each in a range 1
Advice on multiple macros, same code just a different column 9

Top