Solved

Difficulty Using FormulaArray with R1C1 Notation

Posted on 2013-11-21
3
792 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 50

Assisted Solution

by:Rgonzo1971
Rgonzo1971 earned 230 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 270 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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
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 in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

831 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