Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Empty Cell
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Empty Cell
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" 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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Empty Cell
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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Empty Cell
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
find empty cells in a column then append row that empty cell is in | Excel Programming | |||
Find 1st empty cell within each row | Excel Programming | |||
find last none empty cell | Excel Discussion (Misc queries) | |||
Find Empty Cell in Row | Excel Programming | |||
Find 1st Empty Cell: How to? | Excel Programming |