Dave T
10-21-2013, 06:08 AM
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.
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
Any help would be appreciated.
Regards,
Dave T
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.
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
Any help would be appreciated.
Regards,
Dave T