Macro to select a list of values greater than or equal to a value
 

I am trying to write a macro that will search through a list of values (this
list will vary in size) for all values greater than or equal to a specific
value (say =100). I want to activate all the cells containing those values
and copy them to another location. Can anyone help with this?

Macro to select a list of values greater than or equal to a value
 

Easiest is to use
datafilterautofiltercustom

--
Don Guillett
SalesAid Software

"DOOGIE" wrote in message
...
I am trying to write a macro that will search through a list of values
(this
list will vary in size) for all values greater than or equal to a specific
value (say =100). I want to activate all the cells containing those
values
and copy them to another location. Can anyone help with this?

Macro to select a list of values greater than or equal to a va
 

OK, I understand the use of the autofilter. Where I am confused is how to use
a macro to select those cells that the autofilter returns and copy them to
another location. Can you help me with that?

"Don Guillett" wrote:

Easiest is to use
datafilterautofiltercustom

--
Don Guillett
SalesAid Software

"DOOGIE" wrote in message
...
I am trying to write a macro that will search through a list of values
(this
list will vary in size) for all values greater than or equal to a specific
value (say =100). I want to activate all the cells containing those
values
and copy them to another location. Can anyone help with this?

Macro to select a list of values greater than or equal to a value
 

will copy values =100 in column A and paste in column B.

Sub test()
Dim rng As Range
Dim c As Range
Dim rngCopy As Range

With ActiveSheet
Set rng = .Range(.Range("A1"), .Range("A65535").End(xlUp))
End With
For Each c In rng
If c.Value = 100 Then
If rngCopy Is Nothing Then
Set rngCopy = c
Else
Set rngCopy = Union(rngCopy, c)
End If
End If
Next c
If Not rngCopy Is Nothing Then
rngCopy.Copy ActiveSheet.Range("B1")
End If

End Sub


--
Hope that helps.

Vergel Adriano


"DOOGIE" wrote:

I am trying to write a macro that will search through a list of values (this
list will vary in size) for all values greater than or equal to a specific
value (say =100). I want to activate all the cells containing those values
and copy them to another location. Can anyone help with this?

Macro to select a list of values greater than or equal to a va
 

record a macor while doing itclean it up and post your result for comments.

--
Don Guillett
SalesAid Software

"DOOGIE" wrote in message
...
OK, I understand the use of the autofilter. Where I am confused is how to
use
a macro to select those cells that the autofilter returns and copy them to
another location. Can you help me with that?

"Don Guillett" wrote:

Easiest is to use
datafilterautofiltercustom

--
Don Guillett
SalesAid Software

"DOOGIE" wrote in message
...
I am trying to write a macro that will search through a list of values
(this
list will vary in size) for all values greater than or equal to a
specific
value (say =100). I want to activate all the cells containing those
values
and copy them to another location. Can anyone help with this?

Macro to select a list of values greater than or equal to a va
 

OK, I will give it a shot.

"Don Guillett" wrote:

record a macor while doing itclean it up and post your result for comments.

--
Don Guillett
SalesAid Software

"DOOGIE" wrote in message
...
OK, I understand the use of the autofilter. Where I am confused is how to
use
a macro to select those cells that the autofilter returns and copy them to
another location. Can you help me with that?

"Don Guillett" wrote:

Easiest is to use
datafilterautofiltercustom

--
Don Guillett
SalesAid Software

"DOOGIE" wrote in message
...
I am trying to write a macro that will search through a list of values
(this
list will vary in size) for all values greater than or equal to a
specific
value (say =100). I want to activate all the cells containing those
values
and copy them to another location. Can anyone help with this?

Macro to select a list of values greater than or equal to a va
 

Thanks! I will try it! I appreciate the help!

"Vergel Adriano" wrote:

will copy values =100 in column A and paste in column B.

Sub test()
Dim rng As Range
Dim c As Range
Dim rngCopy As Range

With ActiveSheet
Set rng = .Range(.Range("A1"), .Range("A65535").End(xlUp))
End With
For Each c In rng
If c.Value = 100 Then
If rngCopy Is Nothing Then
Set rngCopy = c
Else
Set rngCopy = Union(rngCopy, c)
End If
End If
Next c
If Not rngCopy Is Nothing Then
rngCopy.Copy ActiveSheet.Range("B1")
End If

End Sub


--
Hope that helps.

Vergel Adriano


"DOOGIE" wrote:

I am trying to write a macro that will search through a list of values (this
list will vary in size) for all values greater than or equal to a specific
value (say =100). I want to activate all the cells containing those values
and copy them to another location. Can anyone help with this?

Macro to select a list of values greater than or equal to a va
 

I tried this and it works great! One more question: if I also want to copy
the values in the 2 columns to the right of each cell that contains a value
greater than or equal to 100, how would I do that? Would I use Offset?

"Vergel Adriano" wrote:

will copy values =100 in column A and paste in column B.

Sub test()
Dim rng As Range
Dim c As Range
Dim rngCopy As Range

With ActiveSheet
Set rng = .Range(.Range("A1"), .Range("A65535").End(xlUp))
End With
For Each c In rng
If c.Value = 100 Then
If rngCopy Is Nothing Then
Set rngCopy = c
Else
Set rngCopy = Union(rngCopy, c)
End If
End If
Next c
If Not rngCopy Is Nothing Then
rngCopy.Copy ActiveSheet.Range("B1")
End If

End Sub


--
Hope that helps.

Vergel Adriano


"DOOGIE" wrote:

I am trying to write a macro that will search through a list of values (this
list will vary in size) for all values greater than or equal to a specific
value (say =100). I want to activate all the cells containing those values
and copy them to another location. Can anyone help with this?

Macro to select a list of values greater than or equal to a va
 

Doogie,

You would do it somewhat like this:

'Find values =100 in Col A, copy that and the 2 cells its right. paste in
Col D
Sub test()
Dim rng As Range
Dim c As Range
Dim rngCopy As Range

With ActiveSheet
Set rng = .Range(.Range("A1"), .Range("A65535").End(xlUp))
End With
For Each c In rng
If c.Value = 100 Then
If rngCopy Is Nothing Then
Set rngCopy = c.Resize(1, 3)
Else
Set rngCopy = Union(rngCopy, c.Resize(1, 3))
End If
End If
Next c
If Not rngCopy Is Nothing Then
rngCopy.Copy ActiveSheet.Range("D1")
End If

End Sub




--
Hope that helps.

Vergel Adriano


"DOOGIE" wrote:

I tried this and it works great! One more question: if I also want to copy
the values in the 2 columns to the right of each cell that contains a value
greater than or equal to 100, how would I do that? Would I use Offset?

"Vergel Adriano" wrote:

will copy values =100 in column A and paste in column B.

Sub test()
Dim rng As Range
Dim c As Range
Dim rngCopy As Range

With ActiveSheet
Set rng = .Range(.Range("A1"), .Range("A65535").End(xlUp))
End With
For Each c In rng
If c.Value = 100 Then
If rngCopy Is Nothing Then
Set rngCopy = c
Else
Set rngCopy = Union(rngCopy, c)
End If
End If
Next c
If Not rngCopy Is Nothing Then
rngCopy.Copy ActiveSheet.Range("B1")
End If

End Sub


--
Hope that helps.

Vergel Adriano


"DOOGIE" wrote:

I am trying to write a macro that will search through a list of values (this
list will vary in size) for all values greater than or equal to a specific
value (say =100). I want to activate all the cells containing those values
and copy them to another location. Can anyone help with this?

Macro to select a list of values greater than or equal to a va
 

Thank you very much! This will help me out a great deal with a daily report
that I prepare at my plant. I was not familiar with the Union or Resize
functions. This is what I like about these discussion groups, they are
fantastic learning experiences.

"Vergel Adriano" wrote:

Doogie,

You would do it somewhat like this:

'Find values =100 in Col A, copy that and the 2 cells its right. paste in
Col D
Sub test()
Dim rng As Range
Dim c As Range
Dim rngCopy As Range

With ActiveSheet
Set rng = .Range(.Range("A1"), .Range("A65535").End(xlUp))
End With
For Each c In rng
If c.Value = 100 Then
If rngCopy Is Nothing Then
Set rngCopy = c.Resize(1, 3)
Else
Set rngCopy = Union(rngCopy, c.Resize(1, 3))
End If
End If
Next c
If Not rngCopy Is Nothing Then
rngCopy.Copy ActiveSheet.Range("D1")
End If

End Sub




--
Hope that helps.

Vergel Adriano


"DOOGIE" wrote:

I tried this and it works great! One more question: if I also want to copy
the values in the 2 columns to the right of each cell that contains a value
greater than or equal to 100, how would I do that? Would I use Offset?

"Vergel Adriano" wrote:

will copy values =100 in column A and paste in column B.

Sub test()
Dim rng As Range
Dim c As Range
Dim rngCopy As Range

With ActiveSheet
Set rng = .Range(.Range("A1"), .Range("A65535").End(xlUp))
End With
For Each c In rng
If c.Value = 100 Then
If rngCopy Is Nothing Then
Set rngCopy = c
Else
Set rngCopy = Union(rngCopy, c)
End If
End If
Next c
If Not rngCopy Is Nothing Then
rngCopy.Copy ActiveSheet.Range("B1")
End If

End Sub


--
Hope that helps.

Vergel Adriano


"DOOGIE" wrote:

I am trying to write a macro that will search through a list of values (this
list will vary in size) for all values greater than or equal to a specific
value (say =100). I want to activate all the cells containing those values
and copy them to another location. Can anyone help with this?