Dropdown list and Vlookup simplification

[jmcconnell]

Hi,

I've got a drop down list (SiteList) and when I select an option for that list it looks up details in a spreadsheet called AMSM and fills in a text box(SMName) on the same sheet as the drop down list. There is about 30 options in the drop down list. There is a sample of 2 below:


Private Sub SiteList_Change()
Dim ws As Worksheet
Dim SManager As String
Set ws = Sheets("AMSM")

If SiteList = "Kelburn" Then
SManager = Application.VLookup("Kelburn", ws.Range("A1:c112"), 3)
SMName = SManager
ElseIf SiteList = "Garreg Lwyd" Then
SManager = Application.VLookup("Garreg Lwyd", ws.Range("A1:c112"), 3)
SMName = SManager
End IF

Rather than having if statements as above for all 30 options, is there a way of simplifying this - Perhaps with a loop?

Thank you for your help!!!
Kind regards,
James.

 

[jmcconnell]

That worked great. Thank you very much. So much simpler.

 

[Fluff]

You're welcome & thanks for the feedback.