Sub test()


validationRange = ActiveSheet.Range("X1").Validation.Formula1
'remove equal sign
validationRange = Mid(validationRange, 2)
validationdata = ActiveSheet.Range("X1")
Set selectedcell = Range(validationRange).Find(what:=validationdata, _
LookIn:=xlValues, lookat:=xlWhole)

With Sheets("Sheet2")
Set c = .Columns("A:A").Find(what:=validationdata, _
LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
YValue = c.Offset(0, 1)

selectedcell.Offset(0, 1) = YValue

End If

End With
End Sub


"born2achieve" wrote:

i have 2 sheets in my excel workbook. in my second workbook i have the values
as follows

A B

ss 10
yy 20
zz 30

so in my first sheet values:

X Y

(List)
ss
yy
zz

in my first sheet X range i have list which consists ss,yy,zz
so i f choose ss from the list it has to check the valu for ss in sheet2 and
has to fetch 10 and display in Y range

my excepcted output:
X Y

(List)
ss 10

please give me some sample code for this please