# Calculating the difference between the two values in a Column for a specific block

#### niksnay

##### New Member
I'm learning Excel <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help;">VBA</acronym> programming, and I'm currently trying to get over an issue with how to get the difference between two values for a specific block.
I have one sheet which contains 13 columns in total i.e. from A to M.
Below is the description of Columns which will be useful for performing the desired calculation:

Column A contains random integer value. When there is no value present in it , that means the macro should stopped.
The Column "G" contain value of "9" but occurrence of this value is not fixed; it spreads randomly i.e. some cells contain null value (0) and some contains value 9 etc.

Column J contain a value at places only where there is a "9" present in Column "G", otherwise there is no value present in column J. Thus, Column J can contain a specific value only if there is a 9 present in a column G.

Column "K" contains the sum value for each cell and the value keeps on adding to the previous value until there are 9 present in Column G, once we encountered empty cell, the loop starts again looking for new block of 9

Column "M" contains some integer value at random places.

Now my question is , I want to select the first and last value present in Column M for a specific block of 9, once I can access first and last integer value for that block, I need to calculate the difference between those two values and the process has to repeat till there are no more 9 present in the entire worksheet in Column G. Once I get this difference value , then I have to select Final Sum value for that block that present in Column K, Once I get that , I will perform this operation = Difference value / Sum value . The value that we will get after this, will get multiplied by values present in Column J for that specific block and the new set of values after this operation is my ideal result.

This is an example how my worksheet looks like, so there are cells in Column J which contains any value that can be any integer or decimal value or may be even 0 and there may be cells in column G and J which may contain no data.

 A G J K M Difference Divide Final Result 1 100 100 (200-100) 14 (100/7.0) 2 3 9 1.2 1.2 16.8 (14*1.2) 4 9 1.3 2.5 18.2 5 9 1.4 3.9 19.6 6 9 1.5 5.4 21 7 9 1.6 7.0 22.4 8 200 9 10 30 20 6 11 12 13 9 1 1 6 14 9 2 3 12 15 50

<tbody>
</tbody>

Column G to M is given and Column Difference, Divide and Final result has to be computed by macro for whole worksheet. The macro will run till there is some value present in Column A.

Below is the code that I am trying , till now, this is just able to look for the first value in Column M but not able to locate the second value.

Code:
``````Sub RegisterDifference()
Range("G2").Select
Do While ActiveCell.Offset(0, -6).Value <> ""

If ActiveCell.Value = "9" Then

ActiveCell.Offset(0, 6).Select

Do While ActiveCell.Offset(0, 0).Value = ""

If ActiveCell.Offset(0, 0).Value = "" Then
ActiveCell.Offset(-1, 0).Select
End If

If ActiveCell.Offset(0, 0).Value <> "" Then

End If
Loop

End If
ActiveCell.Offset(1, 0).Select
Loop

End Sub``````

### Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Replies
0
Views
116
Replies
0
Views
155
Replies
2
Views
76
Replies
1
Views
118
Replies
3
Views
180

1,127,352
Messages
5,624,172
Members
416,016
Latest member
DavidXL

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back