How to use VBA to select the first iterm in a drop down list in Excel

Question

I used Data Validation to create some drop down lists. Anyone know how to use VBA to select the first iterm in a drop down list?

I tried 'Split' function:

cell.Value = Split(cell.Validation.Formula1, ",")(0)

but it did not work well, it will only work if I put like "option1, option2" in the source in Data Validation window. If I refer the source to a range of options, then it will return with errors.

I guess there should some smarter ways.

Thanks!

Answer 1

Sub test()
    Dim adr As String
    With Range("c4")
        adr = Mid(.Validation.Formula1, 2)
        Debug.Print Range(adr).Cells(1, 1)
    End With
End Sub

so your answer is:

set c = range("c4")
c.Value = Range(Mid(c.Validation.Formula1, 2)).Cells(1, 1).Value