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 .
However, using VBA as follows:
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
'Array Formula for Comparison
ActiveCell.FormulaArray = "=OR(EXACT(TRIM(RC1)," & Adr & "))"
The formula ends up calculating this:
REGARDLESS of how the spreadsheet is set: either R1C1 style or A1 style
I have been unable to find a way of combining
Can you shed any light, please?
Please see accompanying file for clarity.
Thanks very much