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


New Member
May 15, 2014
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.

AGJKMDifferenceDivideFinal Result
1100100 (200-100)14 (100/7.0)
391.21.216.8 (14*1.2)


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.

Sub RegisterDifference()
Dim FirstRegisterRead As Integer
Dim SecondRegisterRead As Integer
Dim RegisterReadDifference As Integer
FirstRegisterRead = 0
  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
                    FirstRegisterRead = ActiveCell.Offset(0, 0).Value
                    ActiveCell.Offset(0, 3).Value = FirstRegisterRead
                End If
        End If
       ActiveCell.Offset(1, 0).Select

End Sub

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Watch MrExcel Video

Forum statistics

Latest member

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
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
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