Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Difficulty Using FormulaArray with R1C1 Notation

Posted on 2013-11-21
3
Medium Priority
?
1,066 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 53

Assisted Solution

by:Rgonzo1971
Rgonzo1971 earned 920 total points
ID: 39665736
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 1080 total points
ID: 39665774
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
ID: 39667189
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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

971 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