VBA using dropdown to select another cell value

[dknipe]

Ok pulling my hair out here-
trying to get the value from column B in sheet2, by selecting dropdown list from column A

I have a dropdown list in sheet1 column J taking values from sheet2 column A
I created a defined name for column A and column B in sheet2 "my_defined_name"

I added VBA code to sheet1:

Private Sub Dropdown_Change(ByVal Target As Range)
selectedVal = Target.Value


If Target.Column = 10 Then
selectedNum = Application.VLookup(selectedVal, Worksheets("Sheet2").Range("my_defined_name"), 2, False)


If Not IsError(selectedNum) Then
Target.Value = selectedNum
End If


End If
End Sub

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your Sheet1 and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Make a selection in column J.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("J:J")) Is Nothing Then Exit Sub
    Dim foundVal As Range
    Set foundVal = Sheets("Sheet2").Range("A:A").Find(Target, LookIn:=xlValues, lookat:=xlWhole)
    If Not foundVal Is Nothing Then
        Target = foundVal.Offset(0, 1)
    End If
End Sub

 

[dknipe]

thanks for help

still not working tho - I am using Mac, but can't see anywhere that this would be factor here

 

[mumps]

My understanding of what you want to do is to find the value selected in the drop down list from Sheet1 in column A of Sheet2 and return the value in column B to the right of the found value. Then replace the value selected in the drop down list with the value form column B. If this is not correct, please clarify in detail. This works on a PC but I have no experience on a Mac.

 

[dknipe]

yes you are correct, but for some reason not working on my Mac. I guess it must be a Mac thing. cheers