VBA Formula R1C1 help

[iainmartin100]

Hi,
I have the below formula [in Red] writen in VBA but unfortunatly it doesn't work, if I run the code it pastes the whole line as text within the cell, if I then click on the cell and press ctrl+shift+enter it works perfectly.

One of the Mods on here said that it's because I need to use R1C1.
I have attempted this [In Green below] but no luck.

Can someone lend a helping hand?

My original VB formula was:

Range("H68").FormulaArray = "{=INDEX('[" & wbName & "]MS Groups'!$F$2:$F$6000,MATCH($H$7&$C$68,'[" & wbName & "]MS Groups'!$C$2:$C$6000&'[" & wbName & "]MS Groups'!$D$2:$D$6000))}"

And my failed attempt at changing the above to R1C1 is below:

Range("h68").FormulaArray = "{=INDEX('[" & wbName & "]MS Groups'!r6c2:r6c6000,MATCH(r8c7&r3c68,'[" & wbName & "]MS Groups'!r3c2:r3c6000&'[" & wbName & "]MS Groups'!r4c2:r4c6000))}"

Many thanks in advance for any assistance.
<!-- / message -->

 

[Andrew Poulsom]

Remove the curly braces {}. They are added by Excel automatically.

 

[RoryA]

Note also that you have your row and column numbers the wrong way round. $F$2 for example is R2C6 and not R6C2

 

[iainmartin100]

It works

I owe you all more than a big thank you.
You guys have saved me so much headaches, thank you so much.

 

[iainmartin100]

Hi last question (fingers crossed),
Im running a my Macro many times and it is very slow, I wanted to speed up things by opening the workbook but again have hit a problem.
The error message says can't find source but I know "wbName" is 100% correct am I missing off something?

Sub Formula()
Dim WW As Variant
Dim wbName As String

WW = ThisWorkbook.LinkSources(xlExcelLinks)
wbName = Right(WW(1), Len(WW(1)) - InStrRev(WW(1), "\"))

Workbooks.Open Filename:=wbName

 

[RoryA]

Try:

Workbooks.Open Filename:=WW

 

[iainmartin100]

When I try that I get the answer type miss match,
What I put in the tread was a paste of the code so it is spelt correctly.

Thanks

 

[Andrew Poulsom]

Try:

Workbooks.Open Filename:=WW(1)

 

[RoryA]

Yep - didn't read carefully enough. Thanks, Andrew.

 

[Andrew Poulsom]

Yep - didn't read carefully enough. Thanks, Andrew.

Me too on the R1C1 references. Cheers Rory.