PC Review


Reply
Thread Tools Rate Thread

Select rows which satisfy 2 criteria

 
 
Colin Hayes
Guest
Posts: n/a
 
      17th Mar 2012

HI

I need to select rows which satisfy criteria contained in 2 different
columns.

For example , select those rows which have cells in column A with a
number below 200 and also have the word 'Green' in column F.

The cells should be selected rather than filtered if possible.


Grateful any help.

 
Reply With Quote
 
 
 
 
Vacuum Sealed
Guest
Posts: n/a
 
      17th Mar 2012
On 18/03/2012 1:32 AM, Colin Hayes wrote:
>
> HI
>
> I need to select rows which satisfy criteria contained in 2 different
> columns.
>
> For example , select those rows which have cells in column A with a
> number below 200 and also have the word 'Green' in column F.
>
> The cells should be selected rather than filtered if possible.
>
>
> Grateful any help.
>

G'day Colin

this code will do what you want, it will also highlight those rows where
your criteria is met.

This will highlight the entire row (Light Green) for each row that matches.

Sub mySelection()
Dim myRng As Range
Dim c As Range
Set myRng = Range("A1:A10")
For Each c In myRng
If c < 200 And c.Offset(0, 5).Value = "Green" Then
With c
.EntireRow.Interior.Color = 5296274
End With
End If
Next c
End Sub

And this will only color those cell within a specified range, change the
( With c.Resize(1, 6) range to suit your needs e.g (1, 10).

Sub mySelection()
Dim myRng As Range
Dim c As Range
Set myRng = Range("A1:A10")
For Each c In myRng
If c < 200 And c.Offset(0, 5).Value = "Green" Then
With c.Resize(1, 6)
.Interior.Color = 5296274
End With
End If
Next c
End Sub

HTH
Mick.
 
Reply With Quote
 
 
 
 
Don Guillett
Guest
Posts: n/a
 
      17th Mar 2012
On Saturday, March 17, 2012 9:32:24 AM UTC-5, Colin Hayes wrote:
> HI
>
> I need to select rows which satisfy criteria contained in 2 different
> columns.
>
> For example , select those rows which have cells in column A with a
> number below 200 and also have the word 'Green' in column F.
>
> The cells should be selected rather than filtered if possible.
>
>
> Grateful any help.


It is much better and faster to filter and then act of the
specialcells(xlvisible)
than it is to loop.....

 
Reply With Quote
 
Colin Hayes
Guest
Posts: n/a
 
      17th Mar 2012
In article <wF29r.4543$(E-Mail Removed)>,
Vacuum Sealed <(E-Mail Removed)> writes
>G'day Colin
>
>this code will do what you want, it will also highlight those rows where your criteria
>is met.
>
>This will highlight the entire row (Light Green) for each row that matches.
>
>Sub mySelection()
>Dim myRng As Range
>Dim c As Range
>Set myRng = Range("A1:A10")
>For Each c In myRng
> If c < 200 And c.Offset(0, 5).Value = "Green" Then
> With c
> .EntireRow.Interior.Color = 5296274
> End With
> End If
>Next c
>End Sub


Hi Mick

OK thanks for that. It works fine.

Would it be an easy thing to extend the code so that the two columns and
the two values could be entered as variables via popup? This would save
re-writing the code if the parameters change.

Also , because of this , the concerned rows would just need simple
selection rather than colour-coding I would think.

Grateful for your advice on this.



Best Wishes

 
Reply With Quote
 
Vacuum Sealed
Guest
Posts: n/a
 
      18th Mar 2012
On 18/03/2012 4:21 AM, Don Guillett wrote:
> On Saturday, March 17, 2012 9:32:24 AM UTC-5, Colin Hayes wrote:
>> HI
>>
>> I need to select rows which satisfy criteria contained in 2 different
>> columns.
>>
>> For example , select those rows which have cells in column A with a
>> number below 200 and also have the word 'Green' in column F.
>>
>> The cells should be selected rather than filtered if possible.
>>
>>
>> Grateful any help.

>
> It is much better and faster to filter and then act of the
> specialcells(xlvisible)
> than it is to loop.....
>


Colin

I tend to agree with Don, but if you're happy to continue with your
requirement then you will need to adopt "Helper Cells" so that your
Userform/User input can assign the values to. I the following example, I
have used [K1] for your Value & [M1] for your Color, you can change them
to whatever you want so long as you change the code to match.

I am not proficient enough in Excel as to assist you with creating a
UserForm/Input Form for passing the parameter input though.

HTH
Mick.

Sub mySelection()
Dim myRng As Range
Dim vCell As Range, cCell As Range
Dim c As Range
Set myRng = Range("A1:A10")
Set vCell = [K1]
Set cCell = [M1]
For Each c In myRng
If c < vCell And c.Offset(0, 5).Value = cCell Then
With c
.EntireRow.Select
End With
End If
Next c
End Sub

 
Reply With Quote
 
Colin Hayes
Guest
Posts: n/a
 
      18th Mar 2012
>Colin
>
>I tend to agree with Don, but if you're happy to continue with your
>requirement then you will need to adopt "Helper Cells" so that your
>Userform/User input can assign the values to. I the following example, I
>have used [K1] for your Value & [M1] for your Color, you can change them
>to whatever you want so long as you change the code to match.
>
>I am not proficient enough in Excel as to assist you with creating a
>UserForm/Input Form for passing the parameter input though.
>
>HTH
>Mick.
>
>Sub mySelection()
>Dim myRng As Range
>Dim vCell As Range, cCell As Range
>Dim c As Range
>Set myRng = Range("A1:A10")
>Set vCell = [K1]
>Set cCell = [M1]
>For Each c In myRng
> If c < vCell And c.Offset(0, 5).Value = cCell Then
> With c
> .EntireRow.Select
> End With
> End If
>Next c
>End Sub
>


Hi Mick

OK Thanks for that.

The problem I have is that I want to run an existing macro on the
results produced by this procedure. This depends on cells being selected
rather than filtered. If I were starting from scratch I would adopt the
method that Don has explained so well. Unfortunately though I'm having
to fit this into an exiting framework which wouldn't work on filtered
results , and would be a nightmare to unpick.

Thanks anyway for your time.



Best Wishes

 
Reply With Quote
 
Don Guillett
Guest
Posts: n/a
 
      18th Mar 2012
On Sunday, March 18, 2012 9:15:16 AM UTC-5, Colin Hayes wrote:
> >Colin
> >
> >I tend to agree with Don, but if you're happy to continue with your
> >requirement then you will need to adopt "Helper Cells" so that your
> >Userform/User input can assign the values to. I the following example, I
> >have used [K1] for your Value & [M1] for your Color, you can change them
> >to whatever you want so long as you change the code to match.
> >
> >I am not proficient enough in Excel as to assist you with creating a
> >UserForm/Input Form for passing the parameter input though.
> >
> >HTH
> >Mick.
> >
> >Sub mySelection()
> >Dim myRng As Range
> >Dim vCell As Range, cCell As Range
> >Dim c As Range
> >Set myRng = Range("A1:A10")
> >Set vCell = [K1]
> >Set cCell = [M1]
> >For Each c In myRng
> > If c < vCell And c.Offset(0, 5).Value = cCell Then
> > With c
> > .EntireRow.Select
> > End With
> > End If
> >Next c
> >End Sub
> >

>
> Hi Mick
>
> OK Thanks for that.
>
> The problem I have is that I want to run an existing macro on the
> results produced by this procedure. This depends on cells being selected
> rather than filtered. If I were starting from scratch I would adopt the
> method that Don has explained so well. Unfortunately though I'm having
> to fit this into an exiting framework which wouldn't work on filtered
> results , and would be a nightmare to unpick.
>
> Thanks anyway for your time.
>
>
>
> Best Wishes


What may be a nightmare to you may/?? not be to some.
 
Reply With Quote
 
Vacuum Sealed
Guest
Posts: n/a
 
      19th Mar 2012
On 19/03/2012 9:09 AM, Don Guillett wrote:
> On Sunday, March 18, 2012 9:15:16 AM UTC-5, Colin Hayes wrote:
>>> Colin
>>>
>>> I tend to agree with Don, but if you're happy to continue with your
>>> requirement then you will need to adopt "Helper Cells" so that your
>>> Userform/User input can assign the values to. I the following example, I
>>> have used [K1] for your Value& [M1] for your Color, you can change them
>>> to whatever you want so long as you change the code to match.
>>>
>>> I am not proficient enough in Excel as to assist you with creating a
>>> UserForm/Input Form for passing the parameter input though.
>>>
>>> HTH
>>> Mick.
>>>
>>> Sub mySelection()
>>> Dim myRng As Range
>>> Dim vCell As Range, cCell As Range
>>> Dim c As Range
>>> Set myRng = Range("A1:A10")
>>> Set vCell = [K1]
>>> Set cCell = [M1]
>>> For Each c In myRng
>>> If c< vCell And c.Offset(0, 5).Value = cCell Then
>>> With c
>>> .EntireRow.Select
>>> End With
>>> End If
>>> Next c
>>> End Sub
>>>

>>
>> Hi Mick
>>
>> OK Thanks for that.
>>
>> The problem I have is that I want to run an existing macro on the
>> results produced by this procedure. This depends on cells being selected
>> rather than filtered. If I were starting from scratch I would adopt the
>> method that Don has explained so well. Unfortunately though I'm having
>> to fit this into an exiting framework which wouldn't work on filtered
>> results , and would be a nightmare to unpick.
>>
>> Thanks anyway for your time.
>>
>>
>>
>> Best Wishes

>
> What may be a nightmare to you may/?? not be to some.

Colin

As I, and doubtless many contributors here know it can be daunting when
you inherit older hand-me-down workbooks and their problems as one
person's logic and the way they structure may not translate the same,
that said! As Don has intimated, by having an outsiders perspective may
indeed prove not so much a nightmare as you think.

Many of the long-term contributors here astound and amaze me with their
insight and knowledge, of which they give of so freely, I frequently
require help and turn to this NG for assistance, and in turn I give back
what little I have to offer also.

Although the task for you may appear overwhelming and nightmarish,
sharing the entire problem here may bring about a best case outcome as
opposed to placing band-aids here & there.

Give it some serious thought.

Cheers
Mick.
 
Reply With Quote
 
GS
Guest
Posts: n/a
 
      19th Mar 2012
Colin Hayes laid this down on his screen :
>>Colin
>>
>>I tend to agree with Don, but if you're happy to continue with your
>>requirement then you will need to adopt "Helper Cells" so that your
>>Userform/User input can assign the values to. I the following example, I
>>have used [K1] for your Value & [M1] for your Color, you can change them
>>to whatever you want so long as you change the code to match.
>>
>>I am not proficient enough in Excel as to assist you with creating a
>>UserForm/Input Form for passing the parameter input though.
>>
>>HTH
>>Mick.
>>
>>Sub mySelection()
>>Dim myRng As Range
>>Dim vCell As Range, cCell As Range
>>Dim c As Range
>>Set myRng = Range("A1:A10")
>>Set vCell = [K1]
>>Set cCell = [M1]
>>For Each c In myRng
>> If c < vCell And c.Offset(0, 5).Value = cCell Then
>> With c
>> .EntireRow.Select
>> End With
>> End If
>>Next c
>>End Sub
>>

>
> Hi Mick
>
> OK Thanks for that.
>
> The problem I have is that I want to run an existing macro on the results
> produced by this procedure. This depends on cells being selected rather than
> filtered. If I were starting from scratch I would adopt the method that Don
> has explained so well. Unfortunately though I'm having to fit this into an
> exiting framework which wouldn't work on filtered results , and would be a
> nightmare to unpick.
>
> Thanks anyway for your time.
>
>
>
> Best Wishes


The results return a 'Selection' in the form of an entire row. In this
case you could pass a ref to the selected row to the macro you want to
run on it. If there's multiple rows that qualify then you could built a
delimited string of their respective row nums and pass that to your
macro. So for example, modifying Mick's code sample, you could do
something like...

Sub MyMacro
Dim sRefs As String, rng As Range
Dim vCell As Range, cCell As Range
Set vCell = [K1]: Set cCell = [M1]

For Each rng in Range("A1:A10")
If rng < vCell And rng.Offset(, 5).Value = cCell Then _
sRefs = sRefs & "," & CStr(rng.Row)
Next 'rng
Call MyOtherMacro(Mid$(sRefs, 2)) 'trim leading comma beforehand

'Cleanup
Set vCell = Nothing: Set cCell = Nothing
End Sub

Sub MyOtherMacro(ByVal RowRefs As String)
Dim v As Variant
For Each v In Split(RowRefs, ",")
With Rows(v)
'do stuff
End With 'Rows(v)
Next 'v
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion


 
Reply With Quote
 
GS
Guest
Posts: n/a
 
      19th Mar 2012
Mick, some suggested revisions...

> Sub mySelection()

Dim vCell As Range, cCell As Range, c As Range
Set vCell = [K1]: Set cCell = [M1]
For Each c In Range("A1:A10")
If c < vCell And c.Offset(0, 5).Value = cCell Then _
c.EntireRow.Select
> Next c

Set vCell = Nothing: Set cCell = Nothing
> End Sub


HTH: Though the loop does nothing with its selection, it demos stepping
through a range one cell at a time.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion


 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
data doesn't satisfy the criteria in the underlying record source ERROR Message conleyli@mindspring.com Microsoft Access ADP SQL Server 2 8th Dec 2005 07:55 PM
add a column only if 5 other cells on the row satisfy criteria =?Utf-8?B?enViaW4=?= Microsoft Excel Worksheet Functions 1 14th Sep 2005 09:27 AM
Summing totals if record satisfy criteria =?Utf-8?B?UnVkaQ==?= Microsoft Access Queries 7 19th Jul 2005 01:35 PM
Jump to record that satisfy some criteria =?Utf-8?B?UnVkaQ==?= Microsoft Access 7 2nd Jun 2005 08:21 AM
Workaround: ...because it doesn't satisfy the criteria in the underlying record source Malcolm Cook Microsoft Access ADP SQL Server 2 23rd Jun 2004 10:51 AM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 11:07 PM.