CODEWITHSUNDEEP
vbaexcel
Kamil Gosciminski
Kamil Gosciminski

Reputation: 17157

Fill column if other columns in row are not empty

Using:

  1. Microsoft Office Excel Macro-Enabled Worksheet 2007
  2. Microsoft Visual Basic

I've been searching on ways to resolve my issue and couldn't really find the key to resolve this.

Case

In a worksheet I need to automatically insert a value (static timestamp) into column right after specified range of columns based on condition that they are all not null (have been filled).

Example goal

Given the order of inserting values into rows one after another, when values in range of columns A:C are filled, I'd want the column D (the next one after specified range) to update.

ColumnA ColumnB ColumnC AutoInsert
filled  filled  filled  2016-01-11 20:57
filled  filled      
filled          
filled  filled  filled  2016-01-11 20:58

What have I got?

Right now when I insert value into column A current timestamp appears in column B.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
        If Not Intersect(Target, Range("A:A")) Is Nothing Then
            With Target(1, 2)
                .Value = Now
            End With
        End If
End Sub

Current results:

ColumnA AutoInsert
value   2016-01-11 21:06
value   2016-01-11 21:06
value   2016-01-11 21:07

What do I need?

I need some way to check for specified range of columns within row, not only the first column like shown above (in what have I got? section).

I'm not sure whether I need some other method than Intersect, or some kind of a loop. I've tried experimenting with Target(x, y) and Range("A:C"), but the behavior is different than expected and seems to be applied to each active cell.

Upvotes: 4

Views: 2364

Answers (1)

Scott Craner
Scott Craner

Reputation: 152505

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
            If Not Intersect(Target, Range("A:C")) Is Nothing And WorksheetFunction.CountBlank(Range("A" & Target.Row & ":C" & Target.Row)) = 0 Then
                With Me.Cells(Target.Row, 4)
                    Application.EnableEvents = False
                    .value = Now
                End With
            End If
    Application.EnableEvents = True
End Sub

The Me refers to the sheet. It works because this is a worksheet event. If this was in a module it would need to be ActiveSheet. The .Cells is a Range. It used in this format Cells(Row,Column). The whole thing could have been rewritten as Me.Range("D" & Target.row).

Target is a range and doing Target(row,column) is shorthand for Target.Cells(row,column) You could have put Target(1,4). Target(1,1) would have referred to itself, or the upper left cell in a range of cells. It is not constrained by the range but uses the upper left cell as a starting point.

Upvotes: 4

Related Questions