Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
"If column is empty" not working
In a macro, I insert a formula into a column and copy down to the bottom of
the range. The formula is basically "IF there's an error, THEN insert this, ELSE "" ". I'm trying to detect if the column was empty - ie: the formula returns "" - but it's not working. I guess what I'm using is detecting a formula in the cell, so it's not empty? Any suggestions are welcome. Here's what I've got: ' If there are no TIRs without naratives If Application.CountA(Range("AM2:AM12000")) = 0 Then ' Run this macro InsertNarrLinks Else ' Toggles on AutoFilter Range("AM1").Select Selection.AutoFilter End If Ed |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
"If column is empty" not working
try
If [am2:am12000].HasFormula = False Then "Ed" wrote in message ... In a macro, I insert a formula into a column and copy down to the bottom of the range. The formula is basically "IF there's an error, THEN insert this, ELSE "" ". I'm trying to detect if the column was empty - ie: the formula returns "" - but it's not working. I guess what I'm using is detecting a formula in the cell, so it's not empty? Any suggestions are welcome. Here's what I've got: ' If there are no TIRs without naratives If Application.CountA(Range("AM2:AM12000")) = 0 Then ' Run this macro InsertNarrLinks Else ' Toggles on AutoFilter Range("AM1").Select Selection.AutoFilter End If Ed |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
"If column is empty" not working
Ed
Using CountA will not return 0 since formulas are in your range of cells. Try If Application.Sum(Range("AM2:AM12000")) = 0 This will return 0 if you have no values in your range HTH Regards Pascal "Ed" a écrit dans le message de ... But the cell is supposed to have a formula. It is: =IF(COUNTIF(TIRs!$AL$2:$AL$12000,Sheet1!C2)=0,Shee t1!C2,"") I want to detect if the formula has returned "" in all cells, meaning they are all blank. If so, then run the other macro. Else activate the AutoFilter. It keeps activating the AutoFilter, meaning it doesn't see the cells as blank, even though there is no value in them and the AutoFilter has nothing for me to select. Ed "Don Guillett" wrote in message ... try If [am2:am12000].HasFormula = False Then "Ed" wrote in message ... In a macro, I insert a formula into a column and copy down to the bottom of the range. The formula is basically "IF there's an error, THEN insert this, ELSE "" ". I'm trying to detect if the column was empty - ie: the formula returns "" - but it's not working. I guess what I'm using is detecting a formula in the cell, so it's not empty? Any suggestions are welcome. Here's what I've got: ' If there are no TIRs without naratives If Application.CountA(Range("AM2:AM12000")) = 0 Then ' Run this macro InsertNarrLinks Else ' Toggles on AutoFilter Range("AM1").Select Selection.AutoFilter End If Ed |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
"If column is empty" not working
Thank you, Papou. It will be next Tuesday before I get a chance to try
this - I'll let you how it worked. Ed "papou" <nspm wrote in message ... Ed Using CountA will not return 0 since formulas are in your range of cells. Try If Application.Sum(Range("AM2:AM12000")) = 0 This will return 0 if you have no values in your range HTH Regards Pascal "Ed" a écrit dans le message de ... But the cell is supposed to have a formula. It is: =IF(COUNTIF(TIRs!$AL$2:$AL$12000,Sheet1!C2)=0,Shee t1!C2,"") I want to detect if the formula has returned "" in all cells, meaning they are all blank. If so, then run the other macro. Else activate the AutoFilter. It keeps activating the AutoFilter, meaning it doesn't see the cells as blank, even though there is no value in them and the AutoFilter has nothing for me to select. Ed "Don Guillett" wrote in message ... try If [am2:am12000].HasFormula = False Then "Ed" wrote in message ... In a macro, I insert a formula into a column and copy down to the bottom of the range. The formula is basically "IF there's an error, THEN insert this, ELSE "" ". I'm trying to detect if the column was empty - ie: the formula returns "" - but it's not working. I guess what I'm using is detecting a formula in the cell, so it's not empty? Any suggestions are welcome. Here's what I've got: ' If there are no TIRs without naratives If Application.CountA(Range("AM2:AM12000")) = 0 Then ' Run this macro InsertNarrLinks Else ' Toggles on AutoFilter Range("AM1").Select Selection.AutoFilter End If Ed |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Create Pivot Table Data with Column "Sum" rather than "count" defa | Excel Discussion (Misc queries) | |||
Chg 1 "Last, First Mid" column to 3 "First", "Middle", "Last" colu | Excel Discussion (Misc queries) | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
How do I change the column heading in Excel to display "A" "B" "C | New Users to Excel | |||
Working out age from "Day" "Month" "Year" | Excel Worksheet Functions |