Question

IN VBA Please

2.Write a function that takes 2 ranges as input and gets the sum of absolute difference of every element if there two ranges have the same numbers of elements.

the function can send error message to user if the size of these two ranges are different.

      the function can take not only 2 ranges but also two arrays as input

Answer 1

Hi,

Please find the sample VB code:

We can use the Abs function to get the absolute value.
Below is a sample function to calculate the absolute difference between two ranges.

------------------------

Private Sub CommandButton1_Click()
Dim Rng1 As Range
Dim Rng2 As Range
Dim CountRng1 As Long
Dim CountRng2 As Long
Dim absSum As Long
Dim cell As Range

Set Rng1 = Range("A1:A3")
Set Rng2 = Range("B1:B3")
CountRng1 = Rng1.Count
CountRng2 = Rng2.Count

If CountRng1 = CountRng2 Then
    For i = 1 To CountRng1
        absSum = absSum + Abs(Rng1(i).Value - Rng2(i).Value)
    Next i
    MsgBox (absSum)
Else
        MsgBox ("Ranges are not the same size")
End If
End Sub

-------------------------

I have added VB Script code to a command button in excel sheet:

Hope this helps
Thanks,