Why won't my loop recognize empty cells?
 

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?

Why won't my loop recognize empty cells?
 

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

Why won't my loop recognize empty cells?
 

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" wrote in message
...
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?

Why won't my loop recognize empty cells?
 

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.

"Rick Rothstein (MVP - VB)" wrote:

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" wrote in message
...
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?

Why won't my loop recognize empty cells?
 

Consider replacing:

If Last_Cell Is Nothing Then

with:

If IsEmpty(Last_Cell.Value) Then
--
Gary''s Student - gsnu200800


"Jayne22" wrote:

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.

"Rick Rothstein (MVP - VB)" wrote:

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" wrote in message
...
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?

Why won't my loop recognize empty cells?
 

That still does not work :(

"Gary''s Student" wrote:

Consider replacing:

If Last_Cell Is Nothing Then

with:

If IsEmpty(Last_Cell.Value) Then
--
Gary''s Student - gsnu200800


"Jayne22" wrote:

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.

"Rick Rothstein (MVP - VB)" wrote:

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" wrote in message
...
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?

Why won't my loop recognize empty cells?
 

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" wrote in message
...
That still does not work :(

"Gary''s Student" wrote:

Consider replacing:

If Last_Cell Is Nothing Then

with:

If IsEmpty(Last_Cell.Value) Then
--
Gary''s Student - gsnu200800


"Jayne22" wrote:

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.

"Rick Rothstein (MVP - VB)" wrote:

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" wrote in message
...
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?

Why won't my loop recognize empty cells?
 

Thank you! It works great now!

"Rick Rothstein (MVP - VB)" wrote:

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" wrote in message
...
That still does not work :(

"Gary''s Student" wrote:

Consider replacing:

If Last_Cell Is Nothing Then

with:

If IsEmpty(Last_Cell.Value) Then
--
Gary''s Student - gsnu200800


"Jayne22" wrote:

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.

"Rick Rothstein (MVP - VB)" wrote:

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" wrote in message
...
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?