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

niksnay

New Member
Joined
May 15, 2014
Messages
5
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)
2
391.21.216.8 (14*1.2)
491.32.518.2
591.43.919.6
691.55.4
21
791.67.022.4
8200
9
1030206
11
12
139116
1492312
1550

<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()
Dim FirstRegisterRead As Integer
Dim SecondRegisterRead As Integer
Dim RegisterReadDifference As Integer
Range("G2").Select
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
               Loop
                
            
        End If
       ActiveCell.Offset(1, 0).Select
     Loop


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

Threads
1,126,901
Messages
5,621,564
Members
415,846
Latest member
Jrider1

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.
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 "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
Top