VBA Lookup and insert on cell change

Question

Main Worksheet:

Student | Grade | Target Code | Target Text

Jim | A | Code1 | This is the text that corresponds with Code1

Lookup Table, defined as a 'name' "TargetCodes" in a worksheet called 'Targets':

Code1 | This is the text that corresponds with Code1

Code2 | This is the text that corresponds with Code2

I need some VBA so that when the TargetCode field for any record is changed, the corresponding text is placed, in text form, into the Target Text column. I can't use a LOOKUP in the Target Text column because the text needs to be editable and if you try to edit that, you'd just be editing the LOOKUP formula. Help of any sort would be greatly appreciated.

I've cargo-culted some code together from picking over other bits of StackExchange:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell_to_test As Range, cells_changed As Range
Dim result As String
Dim sheet As Worksheet

    Set cells_changed = Target(1, 1)
    Set cell_to_test = Range("D2")

    If Not Intersect(cells_changed, cell_to_test) Is Nothing Then 

        Set sheet = ActiveWorkbook.Sheets("Persuasive Speaking")
        Set TargetSheet = ActiveWorkbook.Sheets("Targets")
        result = Application.WorksheetFunction.Lookup(sheet.Range("D2"),     sheet.Range("WritingTargets"))
        MsgBox ("Test")
    End If
End Sub-

But I get the error "Method 'Range' of object '_Worksheet' failed...

Any help greatly appreciated.

Which part of this are you having a problem with? Do you have any code to post? — Tim Williams
I need some VBA -> Use the Worksheet_Change event in the Targets worksheet to accomplish this. If you get stuck in your coding, post the code and tell us where you are stuck. Then we can help you more efficiently. See this for more info. — Scott Holtzman
Sorry that was dense of me. I've updated the question with the code I've got so far. Thanks a lot for your time, it's really appreciated. — Ed Fearon

Tim Williams Tim Williams · Accepted Answer · 2013-01-10T18:58:42

Private Sub Worksheet_Change(ByVal Target As Range)

Const COL_IDS As Long = 3
Const COL_TARG_TEXT As Long = 4

Dim rngIds As Range, c As Range, val
Dim rngTable As Range, tmp, result

    On Error GoTo haveError

    Set rngIds = Application.Intersect(Target, Target.Parent.Columns(COL_IDS))

    If Not rngIds Is Nothing Then
        Set rngTable = ThisWorkbook.Sheets("Targets").Range("TargetCodes")
        For Each c In rngIds.Cells
            tmp = Trim(c.Value)
            If Len(tmp) > 0 Then
                val = Application.VLookup(tmp, rngTable, 2, False)
                'disable events to avoid re-triggering this sub                 
                Application.EnableEvents = False
                c.EntireRow.Cells(COL_TARG_TEXT).Value = _
                                    IIf(IsError(val), "Not found!", val)
                Application.EnableEvents = True
            End If
        Next c
    End If

    Exit Sub

haveError:
    'MsgBox Err.Description
    Application.EnableEvents = True

End Sub

VBA Lookup and insert on cell change

1 Answers