Results 1 to 6 of 6

Thread: Why can't you add 2 arrays using range?

  1. #1

    Why can't you add 2 arrays using range?



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I have this code:

    Code:
    Sub AddArray()
        Dim A1 As Variant
        Dim A2 As Variant
        
        A1 = Range("A2:A6").Value
        A2 = Range("B2:B6").Value
      
        Range("C2:C6").Value = A1 + A2
            
    End Sub
    Is this really not possible?Or am I missing something?
    Or else I really have to do it using loop.

  2. #2
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,479
    Articles
    0
    Excel Version
    365
    a one liner?:
    Code:
    Range("C2:C6").Value = [A2:A6+B2:B6]

  3. #3
    Nice! Is it faster compared to using a loop?
    I'm actually trying to add the absolute value of the 2 arrays.

  4. #4
    I manage to add the absolute values of 2 arrays using your idea below:
    Code:
    Sub AddArray()
     
       A = [(if(isnumber(A1:A6),abs(A1:A6),A1:A6)) + (if(isnumber(B1:B6),abs(B1:B6),B1:B6))]
      
       [F1:F6] = A
       
            
    End Sub
    But the problem is line in "A" will be too long if I add more arrays.
    I tried to use this one but won't work:
    Code:
    Sub AddArray()
     
       A = [(if(isnumber(A1:A6),abs(A1:A6),A1:A6)) 
       B =[(if(isnumber(B1:B6),abs(B1:B6),B1:B6))]
      
       [F1:F6] = [A+B]
       
            
    End Sub
    any suggestions?

  5. #5
    Super Moderator JoePublic's Avatar
    Join Date
    Sep 2011
    Location
    Askew
    Posts
    183
    Articles
    0
    Excel Version
    2010
    Either loop, or put formulas into the target range and then replace them with values.
    Circumference of a circle = 2πr²



    ²the circle's radius

  6. #6
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,479
    Articles
    0
    Excel Version
    365
    perhaps:
    Range("C2:C6").Value = [INDEX(ABS(A2:A6),)+INDEX(ABS(B2:B6),)]

    ?

Posting Permissions

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