asked on Difficulty Using FormulaArray with R1C1 Notation
Dear experts
I want to compare two columns of text using the "Exact" command in an array formula
When I type the following array formula directly into Cell Address $A$2, using R1C1 reference style it works perfectly .
{=OR(EXACT(TRIM(RC1),RC5:R
However, using VBA as follows:
The formula ends up calculating this:
=OR(EXACT(TRIM(R[-1]C[469]
I have been unable to find a way of combining
ActiveCell.FormulaArray and ActiveCell.FormulaR1C1
Can you shed any light, please?
Please see accompanying file for clarity.
Thanks very much
ExpExch-FormulaArray-RelativeRef.xlsm
I want to compare two columns of text using the "Exact" command in an array formula
When I type the following array formula directly into Cell Address $A$2, using R1C1 reference style it works perfectly .
{=OR(EXACT(TRIM(RC1),RC5:R
However, using VBA as follows:
Option Explicit
Sub CompareData()
'Decalre Variable
Dim Adr As String 'Address of Range containing Comparison data
' Define Column for Comparison
Adr = Range(Cells(2, 5), Cells(1, 5).End(xlDown)).Address
Debug.Print "=OR(EXACT(TRIM(RC1)," & Adr & "))" 'analyse if formula is correct
'Select Cell for formula
Range("B2").Select
'Array Formula for Comparison
ActiveCell.FormulaArray = "=OR(EXACT(TRIM(RC1)," & Adr & "))"
End Sub
The formula ends up calculating this:
=OR(EXACT(TRIM(R[-1]C[469]
I have been unable to find a way of combining
ActiveCell.FormulaArray and ActiveCell.FormulaR1C1
Can you shed any light, please?
Please see accompanying file for clarity.
Thanks very much
ExpExch-FormulaArray-RelativeRef.xlsm
Microsoft Excel
Last Comment
David Phelops
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Barry and Rgonzo
I am delighted with both your contributions.
rgonzo - you have made what I did work! - exactly what I was looking for.
Barry, you have simplified and improved the results considerably by thinking beyond the obvious.
You have now enabled me to look past exact matches and take into account manually (poorly) entered and duplicate data. The words,"Can't see the wood for the trees" enters my mind.
I hope you will be content with barry 270 points for improving and rgonzo for 230 for ansering what i asked!
Cheers!
David