Help with a search macro

[Rave]

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

 

[pgc01]

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]

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]

Thanks Guys! It works great now! I appreciate it!

-Rave