Get Cell Value From Name Range Excel 2007 VBA

[bkatsma]

Hi,

I am looking for some help creating a VBA that will help me. I have two worksheets, contractor & list. Assume that Column (A) on the "contractor" worksheet is a named range from Column (A) on the "list" worksheet. On the "contractor" worksheet I would like to put in the contractors name, and auto populate the pay value in column (B). I have been using a Vlookup formula, but need to automate this process a bit more. Any help would be great. I hope this make sense.

"Contractor" worksheet - Two columns: (A) I will input the contractors name from a dropdown list based on name range from my "list" worksheet. (B) is where I would like to populate the pay base on column (B) in my "list" worksheet.

Contractor (A)	Pay (B)
Jill
Fred
Jack
Megan

<tbody>
</tbody>

"List" worksheet - Two columns; (A) is the list of contractors & (B) is the pay.

Contractor	Pay
Sarah	$18.00
Jill	$20.00
Tim	$22.00
Fred	$20.00
Jack	$15.00
Megan	$15.00

<tbody>
</tbody>

As an aside, does anyone know how to add an attachment. I know examples help, and would have like to include one if I could find out how.

 

[dhn46]

You can to try this code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim CFind As Range
    Set CFind = Sheets("List").[A2:A65536].Find(Target, LookIn:=xlFormulas, lookat:=xlWhole)
    If Not CFind Is Nothing Then
        Target.Offset(, 1) = CFind.Offset(, 1)
    End If
End Sub

 

[bkatsma]

You can to try this code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim CFind As Range
    Set CFind = Sheets("List").[A2:A65536].Find(Target, LookIn:=xlFormulas, lookat:=xlWhole)
    If Not CFind Is Nothing Then
        Target.Offset(, 1) = CFind.Offset(, 1)
    End If
End Sub

I entered the code within the contractors worksheet, not a module. I am getting an error. "Run time error #9, Subscript out of range". Any thoughts?

 

[dhn46]

I entered the code within the contractors worksheet, not a module. I am getting an error. "Run time error #9, Subscript out of range". Any thoughts?

The error may be you change the cell is not Column A.
I edited code form #2, please see code below

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim CFind As Range
    If Target.Column = 1 Then
        Set CFind = Sheets("List").[A2:A65536].Find(Target, LookIn:=xlFormulas, lookat:=xlWhole)
        If Not CFind Is Nothing Then
            Target.Offset(, 1) = CFind.Offset(, 1)
        End If
    End If
End Sub