Prev: How to re-enable events after a VB error in Excel?
Next: Item Count vs Item Cost
From: joel on 31 Oct 2009 11:15

I used autofilter to get the unamtched items. I added a header row to
sheet 1 and then deleted the row. Autofilter doesn't work properly if
you don't have a header row.

Sub SortColumns()

Set Sht1 = Sheets("Sheet1")
Set Sht2 = Sheets("Sheet2")


'copy column A to sheet 2
Sht1.Columns("A").Copy _
Destination:=Sht2.Columns("A")

With Sht2
'lookup column A on sht2 with column b on sht1
RowCount = 1
Do While .Range("A" & RowCount) <> ""
'remove file extension
FName = .Range("A" & RowCount)
FName = Left(FName, InStrRev(FName, ".") - 1)
'remove Folder name
FName = Mid(FName, InStrRev(FName, "\") + 1)


Set c = Sht1.Columns("B").Find(what:=FName, _
LookIn:=xlValues, lookat:=xlPart)
If Not c Is Nothing Then
.Range("B" & RowCount) = c
'put Match into column C on sheet 1
c.Offset(0, 1).Value = "X"
End If
RowCount = RowCount + 1
Loop
NewRow = RowCount
End With

With Sht1
'get items not checked
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
'Insert new row 1 so autofilter works properly
.Rows(1).Insert
.Range("C1") = "Header"
'check if there is at leat one blnak in column C
'so autofilter doesn't fail
Set FilterRange = .Range("C2:C" & LastRow)
Set c = FilterRange.Find(what:="", _
LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
.Columns("C").AutoFilter
.Columns("C").AutoFilter Field:=1, Criteria1:="="
.Range("B2:B" & LastRow).SpecialCells( _
Type:=xlCellTypeVisible).Copy _
Destination:=Sht2.Range("B" & NewRow)
'turn off autfilter
.Columns.AutoFilter
End If
'delte added header row
.Rows(1).Delete
End With

End Sub


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=149008

From: K on 1 Nov 2009 04:36
Thanks joel for you help

First  |  Prev  | 
Pages: 1 2 3
Prev: How to re-enable events after a VB error in Excel?
Next: Item Count vs Item Cost