Why can't you add 2 arrays using range?

nepai

New member
Joined
Feb 19, 2013
Messages
13
Reaction score
0
Points
0
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.
 

p45cal

Super Moderator
Staff member
Joined
Dec 16, 2012
Messages
2,168
Reaction score
10
Points
38
Excel Version(s)
365
a one liner?:
Code:
Range("C2:C6").Value = [A2:A6+B2:B6]
 

nepai

New member
Joined
Feb 19, 2013
Messages
13
Reaction score
0
Points
0
Nice! Is it faster compared to using a loop?
I'm actually trying to add the absolute value of the 2 arrays.
 

nepai

New member
Joined
Feb 19, 2013
Messages
13
Reaction score
0
Points
0
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?
 

JoePublic

Super Moderator
Staff member
Joined
Sep 16, 2011
Messages
220
Reaction score
0
Points
16
Location
UK
Excel Version(s)
2016
Either loop, or put formulas into the target range and then replace them with values.
 

p45cal

Super Moderator
Staff member
Joined
Dec 16, 2012
Messages
2,168
Reaction score
10
Points
38
Excel Version(s)
365
perhaps:
Range("C2:C6").Value = [INDEX(ABS(A2:A6),)+INDEX(ABS(B2:B6),)]

?
 
Top