Here's a first stab at it
Sub Gate1()
Dim c As Range
Dim datarng As Range
Dim Gate1 As String
Dim counter
Gate1 = "Complete ECN Task"
counter = 0
With Sheets("Sheet3")
.Select
Set datarng = .Range("C1:C" & .Range("C65536").End(xlUp).Row)
End With
For Each c In datarng
If c.Value Like "*" & Gate1 & "*" Then
If WorksheetFunction.CountIf(Sheets("Sheet4").Rows(c. Row), _
Range("A" & c.Row).Value) 0 Then
Else
Range("A" & c.Row).Copy Destination:= _
Sheets("Sheet4").Range("A" & c.Row)
counter = counter + 1
End If
Else: End If
Next c
Sheets("Sheet2").Range("E5").Value = counter
End Sub
--
HTH
Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
"morry " wrote in message
...
I have a spreadsheet with some repeated data. I need to search the list
and only count new occurances and list the count on sheet2. Sheet2 also
contains in col A a log of numbers that have been already counted. I
will list the steps of what I am trying to accomplish below. Any help
that anyone can offer would be appreciated.
__________________________________________________ __________________
Search sheet1 Column C
IF data in cell contains "Complete Task" somewhere in it THEN
Compare value in that row col A with all values in col A on sheet2 to
try to find a match.
IF there is a match THEN
Don't count and continue searching for the next occurance of "Complete
Task"
ELSEIF there is no match then add 1 to counter and add value in Sheet1
col A (same row) to end of col A on sheet2
ELSE
Continue searching the list for next occurance until end of file
ENDIF
__________________________________________________ ___________
Here is the code that I have so far:
-----------------------------------------
Sub Gate1()
Dim c As Range
Dim datarng As Range
Dim Gate1 As String
Dim counter
Gate1 = "Complete ECN Task"
counter = 0
Sheets("Sheet3").Select
Set datarng = Sheets("Sheet3").Range("C1:C" & _
Sheets("Sheet3").Range("C65536").End(xlUp).Row)
For Each c In datarng
If Range("C" & c).Value = "*" & Gate1 & "*" Then
If Range("A:A").Value = Sheets("Sheet4").Range("A:A").Value Then
counter = counter + 0
Else
Sheets("Sheet3").Range("A" & c).Value.Copy
Sheets("Sheet4").Range("A" & c).Paste
counter = counter + 1
End If
Else: End If
Next c
Sheets("Sheet2").Range("E5").Value = counter
End Sub
--------------------------------------------------------------------
Hope this is descriptive enough.
Thank you for the help
Morry
---
Message posted from http://www.ExcelForum.com/