Copy row when value NOT found?
 

Excel 2003. This one has me stumped. Not sure where to start, since I'm
relying on data being *not* found*

I need to read each row in "Feb_Sheet" looking up the value in column H
(known as PTN) in "Jan_Sheet".

If the PTN is NOT FOUND in Jan_Sheet, then
copy the row from "FebruarySheet" to a just add sheet "Feb_New"
End if

Thanks a ton.

Copy row when value NOT found?
 

Hi,

You didn't tell us where to look for the PTN in the FEB_SHEET so I used
column A. Change to suit

Sub stance()
Dim MyRange As Range
Dim CopyRange As Range
Set sht = Sheets("Feb_Sheet")
Set lookuprange = Sheets("Jan_Sheet").Range("A1:A100") 'Change to suit
lastrow = sht.Cells(Cells.Rows.Count, "H").End(xlUp).Row
Set MyRange = sht.Range("H1:H" & lastrow)
For Each c In MyRange
If WorksheetFunction.CountIf(lookuprange, c.Value) = 0 And c.Value < ""
Then
If CopyRange Is Nothing Then
Set CopyRange = c.EntireRow
Else
Set CopyRange = Union(CopyRange, c.EntireRow)
End If
End If
Next
If Not CopyRange Is Nothing Then
Worksheets.Add(After:=Worksheets("Feb_Sheet")).Nam e = "Feb_New"
CopyRange.Copy Sheets("Feb_New").Range("A1")
End If
End Sub
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"< AVG Joe" wrote:

Excel 2003. This one has me stumped. Not sure where to start, since I'm
relying on data being *not* found*

I need to read each row in "Feb_Sheet" looking up the value in column H
(known as PTN) in "Jan_Sheet".

If the PTN is NOT FOUND in Jan_Sheet, then
copy the row from "FebruarySheet" to a just add sheet "Feb_New"
End if

Thanks a ton.

Copy row when value NOT found?
 

OOPS,

wrong way round

You didn't tell us where to look for the PTN in the JAN_SHEET so I used
column A. Change to suit

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Mike H" wrote:

Hi,

You didn't tell us where to look for the PTN in the FEB_SHEET so I used
column A. Change to suit

Sub stance()
Dim MyRange As Range
Dim CopyRange As Range
Set sht = Sheets("Feb_Sheet")
Set lookuprange = Sheets("Jan_Sheet").Range("A1:A100") 'Change to suit
lastrow = sht.Cells(Cells.Rows.Count, "H").End(xlUp).Row
Set MyRange = sht.Range("H1:H" & lastrow)
For Each c In MyRange
If WorksheetFunction.CountIf(lookuprange, c.Value) = 0 And c.Value < ""
Then
If CopyRange Is Nothing Then
Set CopyRange = c.EntireRow
Else
Set CopyRange = Union(CopyRange, c.EntireRow)
End If
End If
Next
If Not CopyRange Is Nothing Then
Worksheets.Add(After:=Worksheets("Feb_Sheet")).Nam e = "Feb_New"
CopyRange.Copy Sheets("Feb_New").Range("A1")
End If
End Sub
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"< AVG Joe" wrote:

Excel 2003. This one has me stumped. Not sure where to start, since I'm
relying on data being *not* found*

I need to read each row in "Feb_Sheet" looking up the value in column H
(known as PTN) in "Jan_Sheet".

If the PTN is NOT FOUND in Jan_Sheet, then
copy the row from "FebruarySheet" to a just add sheet "Feb_New"
End if

Thanks a ton.

Copy row when value NOT found?
 

Mike,
Thanks for the start. Your right about the "PTN". It is in column H for both
sheets.
It seems "CopyRange" is always nothing. One thing I've noticed is that from
month to month, I never how many rows I'll to work with. Here is the code
after I've played with it some:

Sub stance()

Dim MyRange As Range
Dim CopyRange As Range
Dim lookuprange As Range
Dim sht As Worksheet

Set sht = Sheets("Feb_Sheet")
Set lookuprange = Sheets("Jan_Sheet").Range("H1:H10805") 'Change to suit

lastrow = sht.Cells(Cells.Rows.Count, "H").End(xlUp).Row

Set MyRange = sht.Range("H1:H" & lastrow)
For Each c In MyRange
If WorksheetFunction.CountIf(lookuprange, c.Value) = 0 And c.Value < ""
Then
If CopyRange Is Nothing Then
Set CopyRange = c.EntireRow
Else
Set CopyRange = Union(CopyRange, c.EntireRow)
End If
End If
Next

If Not CopyRange Is Nothing Then
Worksheets.Add(After:=Worksheets("Feb_Sheet")).Nam e = "Feb_New"
CopyRange.Copy Sheets("Feb_New").Range("A1")
End If

End Sub



"Mike H" wrote:

OOPS,

wrong way round

You didn't tell us where to look for the PTN in the JAN_SHEET so I used
column A. Change to suit

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Mike H" wrote:

Hi,

You didn't tell us where to look for the PTN in the FEB_SHEET so I used
column A. Change to suit

Sub stance()
Dim MyRange As Range
Dim CopyRange As Range
Set sht = Sheets("Feb_Sheet")
Set lookuprange = Sheets("Jan_Sheet").Range("A1:A100") 'Change to suit
lastrow = sht.Cells(Cells.Rows.Count, "H").End(xlUp).Row
Set MyRange = sht.Range("H1:H" & lastrow)
For Each c In MyRange
If WorksheetFunction.CountIf(lookuprange, c.Value) = 0 And c.Value < ""
Then
If CopyRange Is Nothing Then
Set CopyRange = c.EntireRow
Else
Set CopyRange = Union(CopyRange, c.EntireRow)
End If
End If
Next
If Not CopyRange Is Nothing Then
Worksheets.Add(After:=Worksheets("Feb_Sheet")).Nam e = "Feb_New"
CopyRange.Copy Sheets("Feb_New").Range("A1")
End If
End Sub
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"< AVG Joe" wrote:

Excel 2003. This one has me stumped. Not sure where to start, since I'm
relying on data being *not* found*

I need to read each row in "Feb_Sheet" looking up the value in column H
(known as PTN) in "Jan_Sheet".

If the PTN is NOT FOUND in Jan_Sheet, then
copy the row from "FebruarySheet" to a just add sheet "Feb_New"
End if

Thanks a ton.