Nader
02-25-2008, 10:33 PM
I replaced this line in my code. because the UsedRange cause me problem but didn't succed with me
The new code:
R = Sheets("Material").Range("A1").End(xlDown).Row.Count
The last code :
R = Sheets("Material").UsedRange.Rows.Count
Dim myRange As Range
Dim R As Long
Dim cell As Range
Set myRange = Worksheets("sheet1").Range("A19:A100")
For Each cell In myRange
If IsError(Application.Match(cell.Value, Sheets("Material").Columns(1), 0)) _
Or IsError(Application.Match(cell.Offset(0, 1).Value, Sheets("Material").Columns(2), 0)) _
Or IsError(Application.Match(cell.Offset(0, 2).Value, Sheets("Material").Columns(3), 0)) _
Or IsError(Application.Match(cell.Offset(0, 3).Value, Sheets("Material").Columns(4), 0)) Then
R = Sheets("Material").Range("A1").End(xlDown).Row.Count
If R <> 1 Or Sheets("Material").Range("A1").Value <> "" Then R = R + 1
cell.Resize(, 8).Copy Sheets("Material").Range("a" & R)
End If
Next cell
The new code:
R = Sheets("Material").Range("A1").End(xlDown).Row.Count
The last code :
R = Sheets("Material").UsedRange.Rows.Count
Dim myRange As Range
Dim R As Long
Dim cell As Range
Set myRange = Worksheets("sheet1").Range("A19:A100")
For Each cell In myRange
If IsError(Application.Match(cell.Value, Sheets("Material").Columns(1), 0)) _
Or IsError(Application.Match(cell.Offset(0, 1).Value, Sheets("Material").Columns(2), 0)) _
Or IsError(Application.Match(cell.Offset(0, 2).Value, Sheets("Material").Columns(3), 0)) _
Or IsError(Application.Match(cell.Offset(0, 3).Value, Sheets("Material").Columns(4), 0)) Then
R = Sheets("Material").Range("A1").End(xlDown).Row.Count
If R <> 1 Or Sheets("Material").Range("A1").Value <> "" Then R = R + 1
cell.Resize(, 8).Copy Sheets("Material").Range("a" & R)
End If
Next cell