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
• 33,140 points
62 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
• 58,640 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
949 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,979 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
519 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
12,599 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
251 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
• 58,640 points
480 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
• 58,640 points
304 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
• 58,640 points
394 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
• 58,640 points
174 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