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

--
Rick (MVP - Excel)


"MLT" 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!