Convert from Worksheet_Change to manual

Hello All,
I have found a Worksheet_Change macro that I would like to convert to a 'standard' macro that I can run using a button on the worksheet.
Can someone please help me with what I need to do to change it.
I have temporarily commented out some of the code.

[VBA]Private Sub Worksheet_Change(ByVal Target As Range)
'http://answers.microsoft.com/en-us/office/forum/office_2007-excel/insert-formula-for-row-using-worksheet-change/aa1d08d9-9981-4f96-ad57-b1bd1d72e7bb?msgId=1660522c-db3b-4a74-8fd0-fe4a25676dbd
Dim r As Variant, s As Variant
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Columns(1)) Is Nothing Then
Application.EnableEvents = False
On Error Resume Next
With Target.Offset(, 1)
.Formula = "=VLookup(" & Target.Address & ", Data, 5, False)"
.Value = .Value
End With
' With Target.Offset(, 2) 'Column C
' .Formula = "=VLookup(" & Target.Address & ", Data, 6, False)"
' .Value = .Value
' End With
On Error GoTo 0
'get rid of NA
If WorksheetFunction.IsNA(Target.Offset(, 1)) Then
Target.Offset(, 1).ClearContents
End If
' If WorksheetFunction.IsNA(Target.Offset(, 2)) Then
' Target.Offset(, 2).ClearContents
' End If
End If
Application.EnableEvents = True
End Sub[/VBA]

Any help would be appreciated.

Regards,
Dave T

If you want to select a single cell and run the macro, this untested sub might work

Code:

---

Sub UsedToBe_Worksheet_Change()
    Dim r As Range
   
    If Not TypeOf Selection Is Range Then Exit Sub
   
    If Selection.Cells.Count > 1 Then Exit Sub
   
    If Intersect(Selection, Columns(1)) Is Nothing Then Exit Sub
   
    Set r = Selection
   
    Application.EnableEvents = False
    On Error Resume Next
   
    With r.Offset(, 1)
        .Formula = "=VLookup(" & r.Address & ", Data, 5, False)"
        .Value = .Value
    End With
   
    With r.Offset(, 2)  'Column C
        .Formula = "=VLookup(" & r.Address & ", Data, 6, False)"
      .Value = .Value
    End With
    On Error GoTo 0
   
    'get rid of NA
    If WorksheetFunction.IsNA(r.Offset(, 1)) Then r.Offset(, 1).ClearContents
    If WorksheetFunction.IsNA(r.Offset(, 2)) Then r.Offset(, 2).ClearContents
    Application.EnableEvents = True
End Sub

---

There are other ways, but this one seems to be truest to your code



Paul

As Paul did, I would use the Selection range as the Target range. Notice that I just changed the first 3 lines of code.

When you write worksheet or workbook event code, you can send the input parameter(s) to a Sub if you ever need to reuse the code. Then just call that routine in your events as needed. Of course if you write event code to iterate all cells in a Target range, that event will fire for each cell in the target intersection. Then just cut and paste back to get data up to date.

Code:

---

Sub Module_Change()
    'http://answers.microsoft.com/en-us/office/forum/office_2007-excel/insert-formula-for-row-using-worksheet-change/aa1d08d9-9981-4f96-ad57-b1bd1d72e7bb?msgId=1660522c-db3b-4a74-8fd0-fe4a25676dbd
    Dim r As Variant, s As Variant, Target as Range
    Set Target = Selection
    If Target.Cells.Count > 1 Then Exit Sub
    If Not Intersect(Target, Columns(1)) Is Nothing Then
        Application.EnableEvents = False
        On Error Resume Next
        With Target.Offset(, 1)
            .Formula = "=VLookup(" & Target.Address & ", Data, 5, False)"
            .Value = .Value
        End With
        '    With Target.Offset(, 2)  'Column C
        '      .Formula = "=VLookup(" & Target.Address & ", Data, 6, False)"
        '      .Value = .Value
        '    End With
        On Error Goto 0
        'get rid of NA
        If WorksheetFunction.IsNA(Target.Offset(, 1)) Then
            Target.Offset(, 1).ClearContents
        End If
        '    If WorksheetFunction.IsNA(Target.Offset(, 2)) Then
        '      Target.Offset(, 2).ClearContents
        '    End If
    End If
    Application.EnableEvents = True
End Sub

---

1. Modularization is always good

2. Since any type of object on the worksheet can be selected, e.g. a textbox or other shape, I like to test the selected object's type to avoid Type Mismatch errors before proceeding under the assumption that it's a range

Code:

---


If Not TypeOf Selection Is Range Then Exit Sub


---

Paul

Hello Paul and Kenneth,

I really do appreciate your replies and also the helpful comments.
The simple additions by Kenneth to the original coding along with his comments will help me in the future.

Regards,
Dave T