Help with a search macro

Rave

New Member
Joined
Sep 11, 2006
Messages
38
Hey folks, I could use a little help here. I asked for help earlier this week on a macro that would cross-reference column B with whatever was in column A, and then sort column A and match it to column B. For example if Cell A3 = 6 and Cell B26 = 6, the macro would then place 6 in Cell A26. Here is the code that someone came up with for me, and I thought it was working perfectly.

Sub Sort()

ActiveSheet.Range("A1").Activate

Dim arr()
Dim i As Integer
i = 0

For Each ce In Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)
i = i + 1
ReDim Preserve arr(i)
arr(i) = ce.Value
Next ce
Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row).ClearContents
For i = LBound(arr) To UBound(arr)
Set findit = Range("B:B").Find(what:=arr(i))
findit.Offset(0, -1).Value = arr(i)
Next i

End Sub


This is very close to working perfectly, but I ran into one little glitch and I was hoping for some help. Let's assume that one of the cells in column A is 3. Let's also assume that one of the cells in column B is 3 and another one is 31. If 31 is ahead of 3 in the column, it will get matched up with the 3 in column A instead of the 3 in column B. Does anyone know why this macro is doing this, and what I can do to fix it?

Thanks,

-Rave
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,870
Hi Rave

The Find method has a dangerous trait: some of its optional parameters have no default value, meaning that unless you specify them you never know what's their value.

In you case I think you should specify the parameter LookAt, which tells you if Find is lookin for a whole match or a partial match. Try adding LookAt:=xlWhole.

Check the vba help for the Find method.

Hope this helps
PGC
 

Boller

Banned
Joined
Apr 11, 2006
Messages
2,328
Set findit = Range("B:B").Find(what:=arr(i), LookAt:=xlWhole)


Also, to cover possibility of there being no match, should change :-

findit.Offset(0, -1).Value = arr(i)

to :-

If Not findit Is Nothing Then findit.Offset(0, -1).Value = arr(i)
 

Rave

New Member
Joined
Sep 11, 2006
Messages
38
Thanks Guys! It works great now! I appreciate it!

-Rave
 

Watch MrExcel Video

Forum statistics

Threads
1,114,052
Messages
5,545,730
Members
410,702
Latest member
clizama18
Top