VBA Formula R1C1 help

iainmartin100

New Member
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 -->

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Remove the curly braces {}. They are added by Excel automatically.

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

It works

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

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

wbName = Right(WW(1), Len(WW(1)) - InStrRev(WW(1), "\"))

Workbooks.Open Filename:=wbName

Try:
Code:
``Workbooks.Open Filename:=WW``

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

Try:

Rich (BB code):
``Workbooks.Open Filename:=WW(1)``

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

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

Me too on the R1C1 references. Cheers Rory.

Replies
1
Views
661
Replies
6
Views
505
Replies
3
Views
203
Replies
3
Views
354
Replies
1
Views
191

1,203,099
Messages
6,053,523
Members
444,669
Latest member
Renarian

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back