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?