Hi,
One more query…

If there is no items to dislay when autofilter on certain criteria it showing me error.

Invalid procedure call or argument in the below line

cel = Left(cel, Len(cel) - 8)

If there are no items to display it means the value is zero
Is there a way to do this..??

[vba]With Sheets("Sheet2")
.Range("$A$1:$l$1000").AutoFilter Field:=8, Criteria1:="Retail"
For Each cel In .Range(.Cells(2, 1), .Cells(Rows.Count, 1).End(xlUp)).SpecialCells(xlCellTypeVisible)
cel = Left(cel, Len(cel) - 8)

'MsgBox cel
Set c = Sheets("Sheet3").Columns(7).Find(cel)



If c Is Nothing Then
j = j + 1

msg = msg & cel & vbCr
End If
Next
[/vba]

-Sindhuja