Count the sum of difference between two cells

0 votes

Hello, I wrote a function that calculates the absolute difference between two cells, subtracting 70,000 from the total each time. I'd like to do this in Visual Basic for Applications to make it look prettier.

70000-(IF(D2>0,ABS(D2-C2))+IF(E2>0,ABS(E2-D2))+IF(F2>0,ABS(F2-E2))+IF(G2>0,ABS(G2-F2))+IF(H2>0,ABS(H2-G2))+IF(I2>0,ABS(I2-H2))+IF(J2>0,ABS(J2-I2))+IF(K2>0,ABS(K2-J2))+IF(L2>0,ABS(L2-K2))+IF(M2>0,ABS(M2-L2))+IF(N2>0,ABS(N2-M2))+IF(O2>0,ABS(O2-N2)) )
Jan 15 in Others by Kithuzzz
• 28,700 points
42 views

1 answer to this question.

0 votes

Use a for loop:

Sub AbsoluteDifference()
    ' Store variables as double to account for large numbers and decimals
    Dim n As Double
    Dim sum As Double

    n = 70000
    sum = 0

    For Each i In Range("D2:O2").Cells
        If i.Value > 0 Then
            sum = sum + Abs(i.Value - i.Offset(0, -1).Value)
        End If
    Next i
    ' Save the value to C3
    Range("C3").Value = n - sum
End Sub

This code produces an identical result to your function.

answered Jan 15 by narikkadan
• 53,160 points

Related Questions In Others

0 votes
1 answer

What is the difference between hadoop and google analytics ?

I will try and answer this as ...READ MORE

answered Aug 22, 2018 in Others by Frankie
• 9,830 points
924 views
0 votes
0 answers

Mention the difference between Data Driven Testing and Retesting?

Retesting:  It is a process of checking ...READ MORE

Feb 1, 2019 in Others by riya

closed Feb 1, 2019 by Omkar 1,885 views
+1 vote
1 answer

What is the difference between Dark Web and Deep Web?

The content that which you cannot find ...READ MORE

answered Feb 6, 2019 in Others by Priyaj
• 58,100 points
474 views
0 votes
1 answer

What is the difference between BASH and DOS?

The major difference between the BASH and ...READ MORE

answered Mar 8, 2019 in Others by Nabarupa
11,926 views
0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

Feb 17, 2022 in Others by Edureka
• 13,630 points
175 views
0 votes
1 answer

Remove formulas from all worksheets in Excel using VBA

Try this : Option Explicit Sub test1() ...READ MORE

answered Oct 3, 2022 in Others by narikkadan
• 53,160 points
284 views
0 votes
1 answer

Calculate monthly average from daily data without PivotTable

Assuming you have the months in column D enter ...READ MORE

answered Oct 3, 2022 in Others by narikkadan
• 53,160 points
195 views
0 votes
1 answer

Automate compound annual growth rate (CAGR) calculation

The following PowerPivot DAX formulas worked for ...READ MORE

answered Oct 7, 2022 in Others by narikkadan
• 53,160 points
274 views
0 votes
1 answer

<meta name="title"> tag and <title></title> tag elaborate and explain the difference between them

<title> This will be displayed in the ...READ MORE

answered Feb 28, 2022 in Others by narikkadan
• 53,160 points
154 views
0 votes
1 answer
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP