Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Difficulty Using FormulaArray with R1C1 Notation

Posted on 2013-11-21
3
Medium Priority
?
1,009 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 52

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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

721 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