Why won't this code works

[ksnapp]

here is the code

Sub Total_adder()

Dim LR As Long

LR = Cells(Rows.Count, 2).End(xlUp).Row

Range("B2", Cells(LR, 2)).Select

For Each CELL In Selection
With CELL

If CELL.Value = Empty Then
CELL.Value = "TOTALS"
CELL.EntireRow.Font.Bold = True
End If
End With
Next

end sub

it runs and after it just has the range selected with no changes to th
sheet.

I need it to find all the empty cells in that range(that part works
and make the the row bold and put the word "TOTALS" in the empty cell.



Thanks in advanc

 

[Rob van Gelder]

I'm not sure if CELL is a keyword. It's too close for my comfort.
Testing for an empty cell is done differently.

Sub Total_adder()
Dim LR As Long, rng As Range

LR = Cells(Rows.Count, 2).End(xlUp).Row

For Each rng In Range("B2", Cells(LR, 2))
If IsEmpty(rng.Value) Then
rng.Value = "TOTALS"
rng.EntireRow.Font.Bold = True
End If
Next
End Sub

 

[mudraker]

ksnapp


It appears to work ok on my pc

I set up column b with data in several rows then a empty row then mor
data

it put total in the empty cell as expected.

It does not put Total after the last entry

If that is what is wrong then you will need to change

LR = Cells(Rows.Count, 2).End(xlUp).Row

to

LR = Cells(Rows.Count, 2).End(xlUp).Row +

 

[ksnapp]

I tried both answers I was kindly provided with and the one that adds
to the row counter added a total at the bootom

I added a counter to see if it was finding the empty cells and it give
1 as the answer, There are alot more than one.

here is that code. Im using excell 97 if that means anything?

Sub Total_adder()

Dim LR As Long
Dim k As Single

LR = Cells(Rows.Count, "B").End(xlUp).Row + 1


Range("B2", Cells(LR, "B")).Select

For Each CELL In Selection
With CELL

If CELL.Value = "" Then

k = k + 1

End If
End With
Next
MsgBox (k)
End Su

 

[mudraker]

ksnapp


somethings to double check


Is column B the correct column letter


What is the last row number with data in column B

is LR being set with the correct row number

try changing

If CELL.Value = "" Then

to

If trim(CELL.Value) = "" Then

 

[Nigel]

Your code worked for me, it counted the correct number of empty cells, are
you sure your cells are empty and not full of non-printable characters?
Spaces is a favorite one in which cases replace

If CELL.Value = "" Then

with

If Trim(CELL.Value) = "" Then

Cheers
Nigel

 

[Don Guillett]

I tested you code and it worked just fine. Cells is protected but cell isn't
(i use c). Are you counting one too many with the row+1? Maybe you are not
counting the dreaded space bar so add " " to "". Can be modified to:

Sub Total_adder()
Dim LR As Long
Dim k As Single
LR = Cells(Rows.Count, "B").End(xlUp).Row + 1
For Each cell In Range("B2", Cells(LR, "B"))
If cell = "" Or cell = " " Then
k = k + 1
End If
Next
MsgBox k
End Sub

BUT, if you are counting BLANKS then try this
Sub cb()
MsgBox Selection.SpecialCells(xlCellTypeBlanks).Count
End Sub