Prev: Ribbon Customisation
Next: How to code it in macro?
From: Risky Dave on 20 Mar 2010 19:59
Hi,

This feels like one of those things that is probably really easy but my VBA
obviously isn't up to the job :-(

On sheet "A" I have a column containing a list of names.
On sheet "B" I have the same list of names (not necessarily in the same
order) on a different sheet in the same workbook that has a load of other
data associated with it.

What I would like to do is sort the list on sheets A by the date of birth
data field associated with those names on sheet B.

Suggestions on what the code might look like would be appreciated.

TIA

Dave
From: joel on 21 Mar 2010 00:15

Yo need to put the birthdate onto Sheet A and then sort. the code
below looks up each brithdate using column A as the ID field for each
sheet.

Sub SortByBirthdates()


Set sht = Sheets("Sheet A")
Set BirthSht = Sheets("Sheet B")


With sht
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
For RowCount = 1 To LastRow
'get ID field from Column A
ID = .Range("A" & RowCount)
'lookup ID in 2nd sheet

With Sheets("sheet B")
Set c = .Columns("A").Find(what:=ID, _
LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
MsgBox ("Cannot find ID : " & ID)
Else
BirthDate = .Range("B" & c.Row)
sht.Range("B" & RowCount) = BirthDate
End If
End With
Next RowCount

'now sort by birth date
.Rows("1:" & LastRow).Sort _
header:=xlNo, _
key1:=.Range("B1"), _
order1:=xlAscending

End With

End Sub


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

http://www.thecodecage.com/forumz/chat.php

 | 
Pages: 1
Prev: Ribbon Customisation
Next: How to code it in macro?