Simple macro to find the next blank column in the range

[ferocious12]

Hi All,

I have data for same month in multiple columns in a continuous range.

Each month range is separated by a blank column.

What I want is a macro that find the next blank cell in column starting cell B10 and autosum the columns preceding the blank cell.

I want autosum in highlighted columns in yellow (must be a loop).



I started writing the code but stuck:

Sub sum()
ActiveSheet.Range("b10").Select
Selection.Columns.End(xlToRight).Offset(0, 1).Select
ActiveCell.Value = "=Sum(columns.end (xltoleft)"
End Sub

Many thanks
Sophia

 

[Hui]

Sophia

Why not simplify this to :

=SUMIFS(B10:Z10,B8:Z8,"Jan")

 

[NARAYANK991]

Hi Sophia ,

You cannot have a loop which merely autosums columns preceding a blank cell ; when you start the loop from B10 , execution will find that D10 is blank , and sum B10 and C10 correctly.

When it encounters the next blank cell , it is G10 , but now the summing will be done of B10 , C10 , D10 , E10 and F10 since these are all columns preceding a blank cell.

If you can confirm that every blank cell has to be filled in with the sum of the preceding 2 column cells , then there is no chance of any problems.

You can try the following code :

Public Sub InsertSums()
           With ActiveSheet
                .Range("A8").Offset(, .Columns.Count - 1).End(xlToLeft).Offset(, 1).Select
                Do While ActiveCell.Column > 2
                   With ActiveCell
                        .Offset(2).Value = .Offset(2, -1).Value + .Offset(2, -2).Value
                        .Offset(4).Value = .Offset(4, -1).Value + .Offset(4, -2).Value
                        .Offset(, -3).Select
                   End With
                Loop
           End With
End Sub

Narayan