Convert from Worksheet_Change to manual

[Dave T]

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

[Paul_Hossler]

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

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

[Kenneth Hobs]

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.

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

[Paul_Hossler]

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

If Not TypeOf Selection Is Range Then Exit Sub 

Paul

[Dave T]

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