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.

[TABLE="width: 237"]
<tbody>[TR]
[TD]Contractor (A)[/TD]
[TD]Pay (B)[/TD]
[/TR]
[TR]
[TD]Jill[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Fred[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jack[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Megan
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

"List" worksheet - Two columns; (A) is the list of contractors & (B) is the pay.
[TABLE="width: 131"]
<tbody>[TR]
[TD]Contractor[/TD]
[TD]Pay[/TD]
[/TR]
[TR]
[TD]Sarah[/TD]
[TD="align: right"]$18.00[/TD]
[/TR]
[TR]
[TD]Jill[/TD]
[TD="align: right"]$20.00[/TD]
[/TR]
[TR]
[TD]Tim[/TD]
[TD="align: right"]$22.00[/TD]
[/TR]
[TR]
[TD]Fred[/TD]
[TD="align: right"]$20.00[/TD]
[/TR]
[TR]
[TD]Jack[/TD]
[TD="align: right"]$15.00[/TD]
[/TR]
[TR]
[TD]Megan[/TD]
[TD="align: right"]$15.00[/TD]
[/TR]
</tbody>[/TABLE]


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