Find Empty Cell

[MLT]

Here's what I'm trying to do. First find the cell in column one with
the value "VALUE". Then find the first empty cell that appears above
that "VALUE" cell. I'm using the following code but am getting an
Object Required error at the while loop. Any ideas about what I'm
doing wrong.

Sub findEmpty()
For Each cell In Range("A:A")
If cell = "VALUE" Then
myCell = Cells(cell.Row, 1)
MsgBox myCell.Value
While IsEmpty(myCell)
myCell = myCell.Offset(-1, 0)
Wend
End If
Next cell
End Sub

Also, does VBA have different properties available to distinguish
between totally empty cells vs cells that have a formula that results
in a blank cell?

Thanks for any help!

 

[Jim Cone]

An object reference requires a Set statement.
Also, you have not declared your variables and that can cause confusion.
Your inside loop should continue while the cell is NOT empty.
'--
Sub findEmpty()
Dim cell As Range
Dim myCell As Range

For Each cell In Range("A:A")
If cell = "VALUE" Then
Set myCell = cell
MsgBox myCell.Value
While Not IsEmpty(myCell)
Set myCell = myCell.Offset(-1, 0)
Wend
MsgBox myCell.Address
Exit For ' you are done, so get out
End If
Next cell
End Sub
'--
IsEmpty is probably the best bet to test for empty/blank cells.
However, Len(myCell.Formula) also works for any cell, with or without a formula,
but I have seen it throw an error for no apparent reason.
--
Jim Cone
Portland, Oregon USA



"MLT" <[email protected]>
wrote in message
Here's what I'm trying to do. First find the cell in column one with
the value "VALUE". Then find the first empty cell that appears above
that "VALUE" cell. I'm using the following code but am getting an
Object Required error at the while loop. Any ideas about what I'm
doing wrong.

Sub findEmpty()
For Each cell In Range("A:A")
If cell = "VALUE" Then
myCell = Cells(cell.Row, 1)
MsgBox myCell.Value
While IsEmpty(myCell)
myCell = myCell.Offset(-1, 0)
Wend
End If
Next cell
End Sub

Also, does VBA have different properties available to distinguish
between totally empty cells vs cells that have a formula that results
in a blank cell?
Thanks for any help!

 

[Rick Rothstein]

Here is a non-looping solution...

Sub FindFirstEmptyCellAboveValueCell()
Dim V As Range, E As Range
If Worksheets("Sheet3").Range("A1").Value <> "Value" Then
Set V = Worksheets("Sheet3").Range("A:A").Find("Value")
Set E = V.End(xlUp)
If E.Row > 1 Then
Set E = E.Offset(-1)
ElseIf E.Row = 1 And E.Value <> "" Then
Set E = Nothing
End If
End If
If Not E Is Nothing Then MsgBox E.Address
End Sub

 

[MLT]

That works! Couple questions...

1. What does "Set" do? i.e. What is the difference between "x = 3"
and "Set x = 3" ?
2. I'm trying to understand how the non-looping solution works (is
non-looping preferred?).
- if the 1st value in the A column is not the "value" we're looking
for then it finds the value and sets E to "V.End(xlUp)"?
- And what is "E.offset(-1)" for?

Thanks!