Testing For A Blank Cell

**mattnorman** · 03-14-2010, 07:09 AM

I have the following code so far. Im not confident that the syntax is correct as Its been some time since i've used VB I can only remember C# these days.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Intersect(Target, Range("I11:I110")) Is Nothing Then
    
    If Target.Text = "PC" Then
        If Range("L" & Target.Row).Text = "" Then
            Range("D3") = Range("D3").Value + 1
        End If
    End If
    
End If
    
End Sub

What I am looking to do is test if the changed cell is within the range I11:I110 and if so check the contents in column L of the corresponding row to see if it is blank. The reason for this is that I need to have an error log on my spreadsheet to show that something has not been completed correctly. This is stored in cell D3 which will be a value showing how many errors there are.

**rwgrietveld** · 03-14-2010, 08:39 AM

I changed your code into

Private Sub Worksheet_Change(ByVal Target As Range)

Dim iSect As Range, Ccell As Range

Set iSect = Intersect(Target, Range("I11:I110")) 'Target.cells.Count can be > 1 !!

If Not iSect Is Nothing Then
  For Each Ccell In iSect
    If Ccell = "PC" Then
      If Range("L" & Ccell.Row) = "" Then
        Range("D3") = Range("D3") + 1
      End If
    End If
  Next
End If
    
End Sub

Note that this code does not check the previous values in I11:I110
e.g. Select I11:I20. In the formula bar type PC and press CTRL+Enter

D3 will increase by 10

Do the same again and D3 is increased by 10 again.

You need to store the previous value of I11:I110 in an array and check for changes.

Testing For A Blank Cell

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Testing For A Blank Cell

Re: Testing For A Blank Cell

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions