Results 1 to 9 of 9
Thread: Macro to automate formula

20140423, 20:54 #1
 Join Date
 Feb 2008
 Posts
 1,412
 Thanks
 122
 Thanked 5 Times in 5 Posts
Macro to automate formula
Where the value in Col F is the same as in Col G and the control (ref #) in Col E is the same, then the formula must return false, otherwise True
I have the following Array formula in Col I, which does exactly this
=ISNA(IF(ISBLANK(F2),MATCH(E2&G2,E2:E400&F2:F400,0 ),MATCH(E2&F2,E2:E400&G2:G400,0)))
I have written a macro to automate the formula as the data is imported each month. However, when running the macro, it returns True for all the items
I have attached sample data containing the Macro.
It would be appreciated if you could assist me

20140424, 09:52 #2
 Join Date
 Jan 2001
 Location
 La Jolla, CA
 Posts
 1,459
 Thanks
 29
 Thanked 61 Times in 57 Posts
I think it'll work if you make the large ranges absolute cell references:
=ISNA(IF(ISBLANK(F2),MATCH(E2&G2,$E$2:$E$400&$F$2: $F$400,0 ),MATCH(E2&F2,$E$2:$E$400&$G$2:$G$400,0)))

20140424, 10:30 #3
 Join Date
 Feb 2008
 Posts
 1,412
 Thanks
 122
 Thanked 5 Times in 5 Posts
Thanks for the input.
I have made large ranges absolute cell references and when running the macro, it returns true for everything
It would be appreciated if you could assist
I have attached an amended file after amending the large ranges to have absolute cell references

20140424, 13:57 #4
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
The problem is that you are only creating 1 array formula that encompassess the entire range, but you only use 1 row, so you are essentially getting the same value (for row2) in all the cells. You can change your line to:
Code:Range("I2:I" & LR).FormulaArray = "=ISNA(IF(ISBLANK(R2C6:R1000C6),MATCH(R2C5:R1000C5&R2C7:R1000C7,R2C5:R1000C5&R2C6:R1000C6,0),MATCH(R2C5:R1000C5&R2C6:R1000C6,R2C5:R1000C5&R2C7:R1000C7,0)))"
Code:Sub Dup_Val_SameRef() Dim LR As Long Dim rCell As Range LR = Cells(Rows.Count, "D").End(xlUp).Row Range("I2:I" & LR).ClearContents For Each rCell In Range("I2:I" & LR) rCell.FormulaArray = "=ISNA(IF(ISBLANK(RC[3]),MATCH(RC[4]&RC[2],R2C5:R1000C5&R2C6:R1000C6,0),MATCH(RC[4]&RC[3],R2C5:R1000C5&R2C7:R1000C7,0)))" Next End Sub

20140424, 14:06 #5
 Join Date
 Jan 2001
 Location
 La Jolla, CA
 Posts
 1,459
 Thanks
 29
 Thanked 61 Times in 57 Posts
One of the VB heavies should be able to answer this. I'm not! I suspect there's a different approach when using RC references (as Steve pointed out!  don't use the brackets).
Why not just keep the formula in the column and fill down?

20140424, 14:36 #6
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
It is not an issue with the RC formulas, it is an issue with the array [That formula requires the "brackets"]. the way originally done:
Range("I2:I" & LR).FormulaArray = "=Formula"
Creates 1 big array formula, but since the array only references 1 row, it has all the same values (if you look at all the formulas in each row of the attached workbooks you see they all refer to row 2!). This akin to selecting I2:I<LR>, adding a formula to I2 and hitting ctrlshiftenter. The formula given is not appropriate (which is why the results are not what is desired) for that, you need an array for the lookup is also part of the array formula. This was the first part of my response: Creating an appropriate array formula that is for all rows
The "for each rcell" that I created is similar to what was done manuall (and you also suggest). creating the formula in I2 and putting the relative array formula in each of the cells in the range.
SteveLast edited by sdckapr; 20140424 at 14:38.

20140424, 15:31 #7
 Join Date
 Jan 2001
 Location
 La Jolla, CA
 Posts
 1,459
 Thanks
 29
 Thanked 61 Times in 57 Posts
Steve,
The original macro had this as part of the formula: RC[4]:R[398]C[4]&RC[3]:R[398]C[3]
When you rewrote it, you wrote: R2C5:R1000C5&R2C6:R1000C6
which led me to think that without the brackets, the VB treated the cells as absolute references and with the brackets, they were relative.
Is that not the case?

20140424, 17:20 #8
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Ah, you were asking about the square brackets, not the squiggly brackets (for the array)....
Yes, the square brackets in the RC are for relative. That is not a VB issue, it is the same whether used in VB or directly in an XL formula. Howard fixed that issue in the 2nd post (in response to your reply) with formula:
Code:Range("I2:I" & LR).FormulaArray = "=ISNA(IF(ISBLANK(RC[3]),MATCH(RC[4]&RC[2],R2C5:R1000C5&R2C6:R1000C6,0),MATCH(RC[4]&RC[3],R2C5:R1000C5&R2C7:R1000C7,0)))"
Code:Range("I2:I" & LR).FormulaArray = "=ISNA(IF(ISBLANK(RC[3]:R[998]C[3]),MATCH(RC[4]:R[998]C[4]&RC[2]:R[998]C[2],RC[4]:R[998]C[4]&RC[3]:R[998]C[3],0),MATCH(RC[4]:R[998]C[4]&RC[3]:R[998]C[3],RC[4]:R[998]C[4]&RC[2]:R[998]C[2],0)))"
Code:Range("I2:I" & LR).FormulaArray = "=ISNA(IF(ISBLANK(R2C6:R1000C6),MATCH(R2C5:R1000C5&R2C7:R1000C7,R2C5:R1000C5&R2C6:R1000C6,0),MATCH(R2C5:R1000C5&R2C6:R1000C6,R2C5:R1000C5&R2C7:R1000C7,0)))"
What was missing was not the absolute vs relative What was missing for the VB was the changes in the lookup item to be a range rather than a single cell, when one is creating the array to occupy multiple cells.
Steve
PS, more "std" nomenclature can also be used if desired. This is acceptable for creating what Howard was doing:
Code:Range("I2:I" & LR).FormulaArray = "=ISNA(IF(ISBLANK(F2:F1000),MATCH(E2:E1000&G2:G1000,E2:E1000&F2:F1000,0),MATCH(E2:E1000&F2:F1000,E2:E1000&G2:G1000,0)))"
Last edited by sdckapr; 20140424 at 17:28.

The Following User Says Thank You to sdckapr For This Useful Post:
HowardC (20140424)

20140424, 20:32 #9
 Join Date
 Feb 2008
 Posts
 1,412
 Thanks
 122
 Thanked 5 Times in 5 Posts
Hi Steve
Thanks for help, the formula for VBA works perfectly
Howard