| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Do Until empty loop | Excel Programming | |||
| Loop through worksheets & empty cells based on color | Excel Programming | |||
| Loop until cell is empty | Excel Programming | |||
| macro to colour empty cells (cells not recognized as empty) | Excel Programming | |||
| Empty cells in For Loop | Excel Programming | |||
Hybrid Mode
