Why won't my loop recognize empty cells?

[Jayne22]

I'm trying to have a loop that finds empty cells in a range. For those empty
cells, I want an equation to be filled in. However, with the following
program, it won't recognize that any of the cells are blank...even if they
are in fact empty. What am I doing wrong?

Dim Last_Cell, First_Cell as Range
--
'code to declare what Last_Cell is
--
For Each First_Cell In Last_Cell
If Last_Cell Is Nothing Then
First_Cell.Formula = "=dsum(H15:M2000,m15,ak5:al6)"
End If
Next First_Cell

Any suggestions?

 

[Keith74]

Try adding a .value at the end, or use something like len(last_cell) =
0, or isnull

 

[Rick Rothstein \(MVP - VB\)]

I think in order for us to be able to tell you why Last_Cell is never
Nothing, you will have to show us the code you use to "declare what
Last_Cell is".

By the way, this statement of yours....

Dim Last_Cell, First_Cell as Range

is not doing what you think it is. First_Cell is declared as a Range, but
Last_Cell is not... it gets declared as a Variant. In VB/VBA, all variables
must be explicitly declared as to their data type or they default to
Variant. You should do either this...

Dim Last_Cell As Range, First_Cell As Range

or

Dim Last_Cell As Range
Dim First_Cell As Range

Rick

 

[Jayne22]

Okay, here is the code where I declare what Last_Cell is:

Dim Last_Cell, First_Cell as Range
Range("b3").Select
Selection.End(xlDown).Select

With ActiveCell
Set Last_Cell = Range("b2", .Offset(0, 4))
End With

For Each First_Cell In Last_Cell
If Last_Cell Is Nothing Then
First_Cell.Formula = "=dsum(H15:M2000,m15,ak5:al6)"
End If
Next First_Cell

B2 does not have a value, but B3 does...if that makes a difference.

 

[Gary''s Student]

Consider replacing:

If Last_Cell Is Nothing Then

with:

If IsEmpty(Last_Cell.Value) Then

 

[Jayne22]

That still does not work

Consider replacing:

If Last_Cell Is Nothing Then

with:

If IsEmpty(Last_Cell.Value) Then

 

[paulgu]

Here's another approach altogether - use the SpecialCells method:

Dim Blanks As Range, MyRange As Range, c As Range
Set Blanks = MyRange.SpecialCells(xlCellTypeBlanks)
For each c in MyRange.Cells
…
Next

The range Blanks will contain all empty cells and the For loop will then only process those.

 

[Rick Rothstein \(MVP - VB\)]

I think Gary''s Student was on the right track, he just used the same wrong
variable that you did. I don't think you want to check Last_Cell; rather I
think you want the loop variable First_Cell. Try this instead...

If IsEmpty(First_Cell) Then

Rick

 

[Jayne22]

Thank you! It works great now!

I think Gary''s Student was on the right track, he just used the same wrong
variable that you did. I don't think you want to check Last_Cell; rather I
think you want the loop variable First_Cell. Try this instead...

If IsEmpty(First_Cell) Then

Rick