Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Difficulty Using FormulaArray with R1C1 Notation

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

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
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…

580 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