Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why won't this code works
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 -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why won't this code works
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 -- Rob van Gelder - http://www.vangelder.co.nz/excel "ksnapp " wrote in message ... 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 the 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 advance --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why won't this code works
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 + -- Message posted from http://www.ExcelForum.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why won't this code works
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 -- Message posted from http://www.ExcelForum.com |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why won't this code works
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 --- Message posted from http://www.ExcelForum.com/ |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why won't this code works
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 "ksnapp " wrote in message ... I tried both answers I was kindly provided with and the one that adds 1 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 gives 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 Sub --- Message posted from http://www.ExcelForum.com/ ----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==---- http://www.newsfeed.com The #1 Newsgroup Service in the World! 100,000 Newsgroups ---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =--- |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why won't this code works
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 -- Don Guillett SalesAid Software "ksnapp " wrote in message ... I tried both answers I was kindly provided with and the one that adds 1 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 gives 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 Sub --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
code works - need to save | Excel Discussion (Misc queries) | |||
Code works 1st time then Type Mismatch when checking for not blank cells | Excel Programming | |||
VBA Code works in 2000 not 97 | Excel Programming | |||
Code works in XL2000 but not in XP!? | Excel Programming | |||
VBA Code Works in 97, Not in 2002 | Excel Programming |