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

doing this type of exercise is usually don e better by using a find to
match the columns. Try this code. The items that matched I put an X in
column c. If you need the non matched items I can mdoify the code to
take the items without an X a move them to the bottom of the new list.


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) <> ""
Folder = .Range("A" & RowCount)
Set c = sht1.Columns("B").Find(what:=Folder, _
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) = "X"
End If

RowCount = RowCount + 1
Loop
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 31 Oct 2009 07:05
Hi joel thanks for replying. your macro is not working. please see my
excel file in below link in which i explained every thing.
http://www.mediafire.com/?sharekey=68e40059f0508c1b08f8df73f2072ed6e04e75f6e8ebb871

From: joel on 31 Oct 2009 07:58

Solution is real simple. I simply removed the file extension from the
filename in column A and the folder Name.

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)


'remove file extension from filename
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
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 31 Oct 2009 10:14
Thanks lot Joel. i tried your code as well and it works superb. you
are the best
From: K on 31 Oct 2009 10:29
Just last question joel that what kind of code i need after line

..Range("B" & RowCount) = c
in your code that i can have unmatched item get listed on the bottom
of column B of sheet2. Because at the moment your macro only putting
the matched itmes in column B of sheet2 but i need that after putting
matched items then macro should list unmatched items on the bottom.
First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: How to re-enable events after a VB error in Excel?
Next: Item Count vs Item Cost