Using R1C1 in .WorksheetFunction

[brans1982]

Hi,

I'm using an index/match formula as below...

(wf = WorksheetFucntion)

Cells(2, 3).Resize(FinalRow - 1, 1) = _
wf.Index(Range("Routing"), wf.Match(Range("B2"), Range("Flight"), 0))

How do I turn Range("B2") into RC[-1] with in a WorksheetFunction? I can't get the syntax right. I know I can do this formula another way but would like to figure this out for future reference.

Thanks!

 

[GlennUK]

You are not entering a formula into a cell, and are not using cell references. You are putting a VBA object ( Range Value ) into a worksheet function call. You can't use R1C1 referencing as a parameter into a Range property. If you need to access a range by row and column numbers you already know how to do that ... Cells(row,col ).

 

[brans1982]

You are not entering a formula into a cell, and are not using cell references. You are putting a VBA object ( Range Value ) into a worksheet function call. You can't use R1C1 referencing as a parameter into a Range property. If you need to access a range by row and column numbers you already know how to do that ... Cells(row,col ).

Thanks Glenn.

I was just trying to avoid looping a formula as there is over 10,000 rows and also, I want to get out of the habbit of entering a cell forumla and then copying and pasting the values.

What would you use?

 

[jasonb75]

Would somethign on the lines of

Cells(2, 3).Resize(FinalRow - 1, 1) _
= Evaluate("INDEX(Routing,MATCH(INDIRECT(""RC[-1]"",0),FLIGHT,0))")

work?

I'm not sure how the use of Evaluate would affect the outcome.

 

[p45cal]

while you could:

With Cells(2, 3).Resize(FinalRow - 1, 1)
.FormulaR1C1 = "=INDEX(Routing,MATCH(RC[-1], Flight, 0))"
.Value = .Value
End With

I have learnt from experience that sometimes the .value = .value is executed before the cells have completely recalculated - especially if there are sheet change events going on too. There is something you can use to ensure that calculation is complete before continuing, but I forget what it is.
I found in the end, that looping was a cleaner answer, 10,000 not really being a great deal:

For Each cll In Cells(2, 3).Resize(FinalRow - 1, 1).Cells
cll.Value = wf.Index(Range("Routing"), wf.Match(cll.Offset(, -1), Range("Flight"), 0))
Next cll

jasonb75, I tried yours:

Cells(2, 3).Resize(FinalRow - 1, 1) = Application.Evaluate("INDEX(routing,MATCH(INDIRECT(""RC[-1]"",0),flight,0))")

but got #REF! throughout the range.

 

[brans1982]

Thanks for the help folks, much appreciated.