Looking for a way to avoid many If/Then statements

[Paul987]

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

[Mark Lincoln]

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

[Bob Phillips]

=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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Tom Ogilvy]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Tom Ogilvy]

set rng = Range("AE1:BG1")
? rng.count
29


--
Regards,
Tom Ogilvy


"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> =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" <[email protected]> wrote in
> message news:[email protected]...
> >
> > 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
> >
>
>

[Bob Phillips]

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" <[email protected]> wrote in message
news:[email protected]...
> set rng = Range("AE1:BG1")
> ? rng.count
> 29
>
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Bob Phillips" <[email protected]> wrote in message
> news:[email protected]...
> > =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" <[email protected]> wrote
in
> > message news:[email protected]...
> > >
> > > 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
> > >
> >
> >
>
>