jmcconnell
New Member
- Joined
- Feb 2, 2019
- Messages
- 35
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.
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.