Counting the Number of Blank Cells in an undefined range and summing the values in the next column

Question

I have written a VBA Script that allows the user to allow insert a new assembly and its weight into a table. The inserted assembly can be one of two levels (1 or2). If the inserted row is is a sub assembly (i.e. level 2) then its position is directly below the higher level assembly. I would like the level 1 weight to equal the sum of all of the level 2 weights. I believe the easiest way to do this would be by counting the blank cells to define the range and then carrying out the calculation. If the difference between the weight currently in level 1 and the sum of the level 2 weights is not 0 I would like this value to be inserted as a level 2 weight in the same row as the level 1 assembly. I realise this is quite confusing so have attached a picture to try and explain it slightly better. Any help is appreciated :)

Excel Sheet Image

Here is the code I am currently using to insert the values (from a userform)

If TextBox_LevelNo.Value = 1 Then
Cells(NextRow, 6).Value = TextBox_Weight.Value
Cells(NextRow, 7).Value = TextBox_ExtraWeight.Value
ElseIf TextBox_LevelNo.Value = 2 Then
Cells(NextRow, 7).Value = TextBox_Weight.Value
Cells(NextRow, 8).Value = TextBox_ExtraWeight.Value
ElseIf TextBox_LevelNo.Value = 3 Then
Cells(NextRow, 8).Value = TextBox_Weight.Value
End If

Answer 1

OK, here it goes. Use this macro. Execute it once you're finished with the work on the assemblies.

Code explanations:

• col1 and col2 have to be set to the column numbers of the levels, respectively
• other variations shouldn't be necessary. It's just a big iteration to check for column values

Just execute this as a macro once. All Level 2 columns next to level 1 entries will be set.

Sub SumLevelOne()

Dim col1 As Integer
Dim col2 As Integer

col1 = 1    'level 1 column (1 = A)
col2 = 2    'level 2 column (2 = B)

Dim i As Integer
Dim currentLevel1Row As Integer
currentLevel1Row = -1
Dim currentLevel1Total As Integer
currentLevel1Total = 0
Dim currentLevel2Sum As Integer
currentLevel2Sum = 0

Dim LastRow As Integer
LastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, col2).End(xlUp).Row

For i = 1 To LastRow
    If Cells(i, col1) <> "" Then    'level 1 entry
        If currentLevel1Row <> -1 Then  'level 1 entry already set
            'sum up former level 1 entry:
            Cells(currentLevel1Row, col2) = currentLevel1Total - currentLevel2Sum
        End If
        'set new level 1 entry
        currentLevel1Row = i
        currentLevel1Total = Cells(i, col1)
        currentLevel2Sum = 0
    ElseIf i = LastRow Then
        If currentLevel1Row <> -1 Then  'level 1 entry already set
            'sum up former level 1 entry:
            Cells(currentLevel1Row, col2) = currentLevel1Total - currentLevel2Sum
        End If
    Else
        'sum up level 2 entries
        If Cells(i, col2) <> "" Then    'level 2 entry here
            'add this
            currentLevel2Sum = currentLevel2Sum + Cells(i, col2)
        End If
    End If

Next i
End Sub