CODEWITHSUNDEEP
vbaexcel
Hris
Hris

Reputation: 153

VBA error when using a dynamic range

I am trying to print an empty cell if its adjacent cell has a value. I am using a range from b3 to the last row in the column but the empty cells there are not checked- the j variable does not get the empty cells. No formulas leaving a space (e.g. " ") are used. Here is the code:

 Dim ra as Range
 Dim i as Integer
 Dim cell as Range
 Dim j as String
   i = 0
   Set ra = Range([b3], Cells(Rows.count, "B").End(xlUp))
For Each cell In ra     
        If IsEmpty(cell) Then
            If (cell.Offset(1, 0).value <> vbNullString) Then
                j = j & cell.Address(0, 0) & vbNewLine
                i = i + 1
            End If
        End If
Next cell
   ' If i = 0 Then Exit Sub
            msg = msg & vbCrLf & "Sorry, you must enter the missing values in BY Variables sheet in these cells  : " & vbNewLine & j
If msg <> "" Then MsgBox msg

Upvotes: 1

Views: 421

Answers (1)

user4039065
user4039065

Reputation:

If you have cells containing formulas that return zero-length strings (e.g. "") then they are not considered 'empty'. Only a truly blank cell is True when tested with IsEmpty(cell).

However, both a zero-length string and vbnullstring return True when compared to either a truly blank cell or a cell containing a formula that has returned "".

For Each cell In ra     
    If cell.value = vbNullString Then
        If cell.Offset(1, 0).value <> vbNullString Then
            j = j & cell.Address(0, 0) & vbNewLine
            i = i + 1
        End If
    End If
Next cell

I don't know who is propagating this 'bad practice' syntax,

Set ra = Range([b3], Cells(Rows.count, "B").End(xlUp))

... but it should be abandoned in favor of,

with worksheets("sheet1")    'you should know what worksheet you are on
    Set ra = .Range(.cells(3, "B"), .Cells(.Rows.count, "B").End(xlUp))
end with

Upvotes: 2

Related Questions