I'm trying to fill in a formula for each empty cell in Last_Cell (a range
that i declared); if the cell is not empty, it can skip that cell. Why
doesn't this work when there are definitely empty cells in the range?

Dim First_Cell, Last_Cell as Range
For Each First_Cell In Last_Cell
If Last_Cell Is Nothing Then
First_Cell.Activate
ActiveCell.Formula = "=dsum(H15:M2000,m15,ak5:al6)"
Else
Exit For
End If
Next First_Cell

End Sub

Jayne-
You are checking to see if an entire range (Last_Cell) is Nothing. I'm
guessing you want to check each individual cell, and check them to see if
they are empty. Also, your exit for statement means that you would exit the
loop the first time you hit a cell that wasn't empty, whereas it sounds like
you want to skip empty cells and overwrite filled cells(?). Based on what I
think you said, maybe something like (aircode):

Dim IndividualCell as Excel.Worksheet.Cell
Dim WholeRange as Range

For Each IndividualCell In WholeRange
If IndividualCell.value <"" Then
IndividualCell.Activate
IndividualCell.Formula = "=dsum(H15:M2000,m15,ak5:al6)"
Else
'do nothing
End If
Next First_Cell

If I mixed up which cells get formulas and which get skipped, just change
the < to =

Aircode, so you may have to tweak the syntax.

HTH,
Keith


"Jayne22" wrote in message
...
I'm trying to fill in a formula for each empty cell in Last_Cell (a range
that i declared); if the cell is not empty, it can skip that cell. Why
doesn't this work when there are definitely empty cells in the range?

Dim First_Cell, Last_Cell as Range
For Each First_Cell In Last_Cell
If Last_Cell Is Nothing Then
First_Cell.Activate
ActiveCell.Formula = "=dsum(H15:M2000,m15,ak5:al6)"
Else
Exit For
End If
Next First_Cell

End Sub

Try this modification


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

End Sub
"Jayne22" wrote:

I'm trying to fill in a formula for each empty cell in Last_Cell (a range
that i declared); if the cell is not empty, it can skip that cell. Why
doesn't this work when there are definitely empty cells in the range?

Dim First_Cell, Last_Cell as Range
For Each First_Cell In Last_Cell
If Last_Cell Is Nothing Then
First_Cell.Activate
ActiveCell.Formula = "=dsum(H15:M2000,m15,ak5:al6)"
Else
Exit For
End If
Next First_Cell

End Sub

I'll tell you what I think I see, but I may be mistaken so don't hesitate to
straighten me out if I'm misunderstanding something:

1) You declare Last_Cell as a Range, but I don't see any code that tells
your program what cells it refers to. If you defined that elsewhere in your
program, like this:

Dim First_Cell, Last_Cell as Range
..
Set Last_Cell = <some range
..
For Each First_Cell In Last_Cell
If Not Last_Cell Is Nothing Then Exit For
First_Cell.Formula = "=dsum(H15:M2000,m15,ak5:al6)"
Next First_Cell

....that's fine; but it literally is what you put below then I think what
happens is that there are no cells in Last_Cell and the loop executes with
zero iterations.

2) If, as you say, there are definitely empty cells in Last_Cell, here's the
next problem (maybe): Your code specifies that the first non-empty cell in
the range will cause it to leave the loop. So if you meant it to fill in
this formula for every empty cell in the range, what it'll really do is fill
in the formula only for the empty cells that come first in the range, and
none of the empty cells that come after a non-empty cell. And if the first
cell in the range is not empty, then it won't fill in the formula anywhere at
all. See what I mean, here? If that's the problem, you should modify the
loop to look like this:

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

I don't see any other problems. My modifications are just because I think
the above is a little easier to interpret, not because your If statement had
an error in it. (But it does seem silly to activate First_Cell and then use
ActiveCell.Formula when you could more easily have used First_Cell.Formula in
the first place.)

--- "Jayne22" wrote:
I'm trying to fill in a formula for each empty cell in Last_Cell (a range
that i declared); if the cell is not empty, it can skip that cell. Why
doesn't this work when there are definitely empty cells in the range?

Dim First_Cell, Last_Cell as Range
For Each First_Cell In Last_Cell
If Last_Cell Is Nothing Then
First_Cell.Activate
ActiveCell.Formula = "=dsum(H15:M2000,m15,ak5:al6)"
Else
Exit For
End If
Next First_Cell

End Sub

Jayne
As written, here is the problem. You have the "If Last_Cell Is Nothing
Then" line inside of the For loop that references the Last_Cell. This is
nonsensical. If Last_Cell is, in fact, nothing, you will get an error in
the "For Each..." line and never reach the IF statement. Also, because
"First_Cell" is a member of Last_Cell (you say so in the "For each..."),
then First_Cell is nothing whenever Last_Cell is nothing. Also (this will
not cause an error) you declared First_Cell as Variant when you didn't
specify a data type. Maybe you want something like the following. HTH
Otto
Sub Whatever()
Dim First_Cell As Range, Last_Cell As Range
If Not Last_Cell Is Nothing Then
For Each First_Cell In Last_Cell
First_Cell.Activate
ActiveCell.Formula = "=dsum(H15:M2000,m15,ak5:al6)"
Next First_Cell
End If
End Sub

"Jayne22" wrote in message
...
I'm trying to fill in a formula for each empty cell in Last_Cell (a range
that i declared); if the cell is not empty, it can skip that cell. Why
doesn't this work when there are definitely empty cells in the range?

Dim First_Cell, Last_Cell as Range
For Each First_Cell In Last_Cell
If Last_Cell Is Nothing Then
First_Cell.Activate
ActiveCell.Formula = "=dsum(H15:M2000,m15,ak5:al6)"
Else
Exit For
End If
Next First_Cell

End Sub

Hi Bob,
When I use the loop you suggest in part 2), even if a cell is empty, the
program does not seem to recognize that there is indeed nothing in that
Last_Cell. I added a step to activate each cell to watch it run, and it skips
over all the cells--including the blank ones (and I did declare the range for
Last_Cell in an earlier statement):

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

Any suggestions?

"Bob Bridges" wrote:

I'll tell you what I think I see, but I may be mistaken so don't hesitate to
straighten me out if I'm misunderstanding something:

1) You declare Last_Cell as a Range, but I don't see any code that tells
your program what cells it refers to. If you defined that elsewhere in your
program, like this:

Dim First_Cell, Last_Cell as Range
.
Set Last_Cell = <some range
.
For Each First_Cell In Last_Cell
If Not Last_Cell Is Nothing Then Exit For
First_Cell.Formula = "=dsum(H15:M2000,m15,ak5:al6)"
Next First_Cell

...that's fine; but it literally is what you put below then I think what
happens is that there are no cells in Last_Cell and the loop executes with
zero iterations.

2) If, as you say, there are definitely empty cells in Last_Cell, here's the
next problem (maybe): Your code specifies that the first non-empty cell in
the range will cause it to leave the loop. So if you meant it to fill in
this formula for every empty cell in the range, what it'll really do is fill
in the formula only for the empty cells that come first in the range, and
none of the empty cells that come after a non-empty cell. And if the first
cell in the range is not empty, then it won't fill in the formula anywhere at
all. See what I mean, here? If that's the problem, you should modify the
loop to look like this:

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

I don't see any other problems. My modifications are just because I think
the above is a little easier to interpret, not because your If statement had
an error in it. (But it does seem silly to activate First_Cell and then use
ActiveCell.Formula when you could more easily have used First_Cell.Formula in
the first place.)

--- "Jayne22" wrote:
I'm trying to fill in a formula for each empty cell in Last_Cell (a range
that i declared); if the cell is not empty, it can skip that cell. Why
doesn't this work when there are definitely empty cells in the range?

Dim First_Cell, Last_Cell as Range
For Each First_Cell In Last_Cell
If Last_Cell Is Nothing Then
First_Cell.Activate
ActiveCell.Formula = "=dsum(H15:M2000,m15,ak5:al6)"
Else
Exit For
End If
Next First_Cell

End Sub