Solved

Difficulty Using FormulaArray with R1C1 Notation

Posted on 2013-11-21
3
694 Views
Last Modified: 2013-11-21
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:R613C5))} (using Ctrll-Shift+Enter)

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

Open in new window


The formula ends up calculating this:
=OR(EXACT(TRIM(R[-1]C[469]),R2C5:R613C5)) REGARDLESS of how the spreadsheet is set: either R1C1 style or A1 style

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
0
Comment
Question by:David Phelops
3 Comments
 
LVL 48

Assisted Solution

by:Rgonzo1971
Rgonzo1971 earned 230 total points
Comment Utility
Hi,

pls try

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(ReferenceStyle:=xlR1C1)
        'Debug.Print "=OR(exact(trim(R[" & ActiveCell.Row & "]C[1])," & rOneT.Address & "))"
'Select Cell for formula
    Range("B2").Select
'Array Formula for Comparison
    ActiveCell.FormulaArray = "=OR(EXACT(TRIM(RC[-1])," & Adr & "))"

End Sub

Open in new window

Regards
0
 
LVL 50

Accepted Solution

by:
barry houdini earned 270 total points
Comment Utility
Not answering your question, just a query......

Do you need EXACT function here? That function is only useful if you want to make sure that matches are case-sensitive, e.g. that LOWER doesn't equal lower

All letters in your data appear to be upper case so unless there are examples where that isn't the case a COUNTIF formula might be simpler, e.g. something like

=COUNTIF(E:E,TRIM(A2))>0

which doesn't need "array entry"

regards, barry
0
 

Author Closing Comment

by:David Phelops
Comment Utility
Gentlemen, both...
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
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now