Lookup

[mks]

Hi,
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
I need the macros for lookup.
<o> </o>
I have drop down list ( List 1) in Cell “C2” and sheet name is “Location”.
<o> </o>
I have three drop menu for lookup named – List 1, List 2 & list 3 on same sheet “Location”.
<o> </o>
Range – List 1 = AE10:AE25, List 2 = AF10:AF25 & List 3 = AG10:AG25
<o> </o>
I have the following function, but I need the Macros if you can help me.
<o> </o>
= if(C2=””,””,Lookup(C2,List 1, List2)) - This function is Cell “C3”
= if(C2=””,””,Lookup(C2,List 1, List3)) - This function is Cell “C4”
<o> </o>
Thanks for our help

 

[DauntingGecko]

Option Explicit

Private Sub myExample()
Dim myValue As Integer
Dim cell1 As Range
Dim cell2 As Range

On Error GoTo err

    myValue = Sheets("Location").[C2].Value
    Set cell1 = Sheets("Location").[C3]
    Set cell2 = Sheets("Location").[C4]
    
    If myValue = 0 Then
        cell1 = ""
        cell2 = ""
    Else
        cell1 = Application.Lookup(myValue, [List1], [List2])
        cell2 = Application.Lookup(myValue, [List1], [List3])
    End If
    
    Exit Sub

err:
    MsgBox err.Description, vbExclamation, "Error"

End Sub

 

[mks]

DauntingGecko,

Thanks


But it does not run itself, when I select the value from List 1 in Cell C2. I have to run macros to update value.