CODEWITHSUNDEEP
excel
Fooz
Fooz

Reputation: 21

automatic filling series in cell when a different cell is edited

I want to add a sequential number in column 1 (so A1, B1, c1, would be 1,2,3) WHEN I enter any data in Column 2. So if a cell doesn't have a value, nothing is entered in the other cell.

The end result should look like this.

A- 1    32
B- 2    18
C- 3    16

E- 5     20

It's for a box count and config.

I've tried a few things and I am missing a value. Can any one help please?

Upvotes: 1

Views: 288

Answers (2)

Alex P
Alex P

Reputation: 12487

One way to tackle this problem would be to leverage the Worksheet_Change event. This way you do not need to have any formulas in your spreadsheet:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Application.Intersect(Target, Range("B:B")) Is Nothing Then
        End
    End If

    Target.Offset(0, -1) = WorksheetFunction.Max(Range("A:A")) + 1

End Sub

To implement this code:

  1. In your workbook press ALT + F11 (this opens VBA editor)
  2. Open the Project Explorer (CTRL + R)
  3. Double-clik the sheet that you want the code to apply to e.g. Sheet1
  4. In the left-hand dropdown menu select Worksheet and in the right hand menu select Change
  5. Cut and paste the code provided

Hope this helps.

Upvotes: 0

oli206
oli206

Reputation: 453

Maybe this can help, just in the A1 cell use this formula:

Excel 2007

=IF(LEN(B1)>0;ROW(A1);"")

Excel 2010

=IF(LEN(B1)>0,ROW(A1),"")

And then fill down the cells with the formula. This will write the cell row when it finds anything and a zero when it doesnt:

1   test
2   test
3   test
0   
5   test
0   
7   test

Upvotes: 1

Related Questions