| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looking for a way to avoid many If/Then statements
I have a sheet containing rows of 20 cells (AE1:BG1) with formulas that result in True/False. I would like to creat a macro that screens for rows where the values are either all "True" or "Blank". (anything but "False"). The Macro would then copy that row and paste it to another sheet. Is there a way to state If Cells AE1:BG1 < False Then... If not, is there a better way to go about this? There has to be a better way than 20 If/Thens... TIA Paul -- Paul987 ------------------------------------------------------------------------ Paul987's Profile: http://www.excelforum.com/member.php...o&userid=24850 View this thread: http://www.excelforum.com/showthread...hreadid=519459 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Looking for a way to avoid many If/Then statements
This is quick and dirty, but it works. I have this running in a
Module. You'll have to designate the worksheet you're pasting to, as well as whatever you actually want pasted (I just pasted everything as is). Option Explicit Sub TestForFalse() Dim c As Object, NoFalses As Boolean NoFalses = True For Each c In Range("AE1:BG1") If c.Value = "False" Then NoFalses = False Exit For End If Next If NoFalses Then Range("AE1:BG1").Copy 'change the next line to suit your destination 'and pasting parameters Range("AE7:BG7").PasteSpecial (xlPasteAll) End If End Sub |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Looking for a way to avoid many If/Then statements
=IF(COUNTIF(AE1:BG1,TRUE)=24, ...
if all are TRUE or 0 if any are TRUE -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Paul987" wrote in message ... I have a sheet containing rows of 20 cells (AE1:BG1) with formulas that result in True/False. I would like to creat a macro that screens for rows where the values are either all "True" or "Blank". (anything but "False"). The Macro would then copy that row and paste it to another sheet. Is there a way to state If Cells AE1:BG1 < False Then... If not, is there a better way to go about this? There has to be a better way than 20 If/Thens... TIA Paul -- Paul987 ------------------------------------------------------------------------ Paul987's Profile: http://www.excelforum.com/member.php...o&userid=24850 View this thread: http://www.excelforum.com/showthread...hreadid=519459 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Looking for a way to avoid many If/Then statements
To check if at least one cell is not False:
set rng = Range("AE1:BG1") if not (application.Countif(rng,False) = rng.count) the Hard to tell from your description what the actual criteria is. -- Regards, Tom Ogilvy "Paul987" wrote in message ... I have a sheet containing rows of 20 cells (AE1:BG1) with formulas that result in True/False. I would like to creat a macro that screens for rows where the values are either all "True" or "Blank". (anything but "False"). The Macro would then copy that row and paste it to another sheet. Is there a way to state If Cells AE1:BG1 < False Then... If not, is there a better way to go about this? There has to be a better way than 20 If/Thens... TIA Paul -- Paul987 ------------------------------------------------------------------------ Paul987's Profile: http://www.excelforum.com/member.php...o&userid=24850 View this thread: http://www.excelforum.com/showthread...hreadid=519459 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Looking for a way to avoid many If/Then statements
set rng = Range("AE1:BG1")
? rng.count 29 -- Regards, Tom Ogilvy "Bob Phillips" wrote in message ... =IF(COUNTIF(AE1:BG1,TRUE)=24, ... if all are TRUE or 0 if any are TRUE -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Paul987" wrote in message ... I have a sheet containing rows of 20 cells (AE1:BG1) with formulas that result in True/False. I would like to creat a macro that screens for rows where the values are either all "True" or "Blank". (anything but "False"). The Macro would then copy that row and paste it to another sheet. Is there a way to state If Cells AE1:BG1 < False Then... If not, is there a better way to go about this? There has to be a better way than 20 If/Thens... TIA Paul -- Paul987 ------------------------------------------------------------------------ Paul987's Profile: http://www.excelforum.com/member.php...o&userid=24850 View this thread: http://www.excelforum.com/showthread...hreadid=519459 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Looking for a way to avoid many If/Then statements
Thank you, I counted on my fingers, obviously missed a group <vbg
-- HTH Bob Phillips (remove nothere from email address if mailing direct) "Tom Ogilvy" wrote in message ... set rng = Range("AE1:BG1") ? rng.count 29 -- Regards, Tom Ogilvy "Bob Phillips" wrote in message ... =IF(COUNTIF(AE1:BG1,TRUE)=24, ... if all are TRUE or 0 if any are TRUE -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Paul987" wrote in message ... I have a sheet containing rows of 20 cells (AE1:BG1) with formulas that result in True/False. I would like to creat a macro that screens for rows where the values are either all "True" or "Blank". (anything but "False"). The Macro would then copy that row and paste it to another sheet. Is there a way to state If Cells AE1:BG1 < False Then... If not, is there a better way to go about this? There has to be a better way than 20 If/Thens... TIA Paul -- Paul987 ------------------------------------------------------------------------ Paul987's Profile: http://www.excelforum.com/member.php...o&userid=24850 View this thread: http://www.excelforum.com/showthread...hreadid=519459 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| avoid embedded if statements | Excel Worksheet Functions | |||
| IF Statements (Mutliple Statements) | Excel Worksheet Functions | |||
| what is this, how do I avoid it? | Excel Discussion (Misc queries) | |||
| operator statements, shorting when reusing one of the statements? | Excel Programming | |||
| avoid div by zero | Excel Programming | |||
![Paul987[_14_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
