Results 1 to 9 of 9
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    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
    Attached Files Attached Files

  2. #2
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,469
    Thanks
    30
    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)))

  3. #3
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    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
    Attached Files Attached Files

  4. #4
    WS Lounge VIP sdckapr's Avatar
    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)))"
    to do it, or create a separate formula in each row with a macro like:

    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
    Steve

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,469
    Thanks
    30
    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?

  6. #6
    WS Lounge VIP sdckapr's Avatar
    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 ctrl-shift-enter. 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.

    Steve
    Last edited by sdckapr; 2014-04-24 at 15:38.

  7. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,469
    Thanks
    30
    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?

  8. #8
    WS Lounge VIP sdckapr's Avatar
    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)))"
    The range was absolute and the lookup was relative, which would work for single cell array, but doesn't solve the issue with multi-cell arrays. in fact the all-relative formula:
    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)))"
    Could be used instead of the all absolute one I chose:
    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)))"
    I used the absolute since it was shorter, but it doesn't matter, since there is only ONE formula that occupies a group of cells, the way it is being created...

    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; 2014-04-24 at 18:28.

  9. The Following User Says Thank You to sdckapr For This Useful Post:

    HowardC (2014-04-24)

  10. #9
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Steve

    Thanks for help, the formula for VBA works perfectly

    Howard

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •