Macro to clear cells
I need a macro that if I type a value in column N the cells in the same row
in coloms M,J and K needs to be cleared. This is what I have at the moment but I get runtime error 13 Type mismatch. I am just starting to learn myself VBA so please help. Thanx Private Sub Worksheet_Change(ByVal Target As Range) Dim row As Integer row = Target.row If Target.Column = "NA" Then Cells(row, 10).Clear Cells(row, 11).Clear Cells(row, 13).Clear Cells(row, 14).Clear End If End Sub |
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit: Application.EnableEvents = False With Target If .Column = 14 Then .Offset(0,-1).Clear .Offset(0,-3).Clear .Offset(0,-4).Clear End If End With ws_exit: Application.EnableEvents = True End Sub -- HTH RP (remove nothere from the email address if mailing direct) "Esrei" wrote in message ... I need a macro that if I type a value in column N the cells in the same row in coloms M,J and K needs to be cleared. This is what I have at the moment but I get runtime error 13 Type mismatch. I am just starting to learn myself VBA so please help. Thanx Private Sub Worksheet_Change(ByVal Target As Range) Dim row As Integer row = Target.row If Target.Column = "NA" Then Cells(row, 10).Clear Cells(row, 11).Clear Cells(row, 13).Clear Cells(row, 14).Clear End If End Sub |
Hi
Try If Not Range("N:N") Is Nothing Then in place of If Target.Column = "NA" Then Tina "Esrei" wrote: I need a macro that if I type a value in column N the cells in the same row in coloms M,J and K needs to be cleared. This is what I have at the moment but I get runtime error 13 Type mismatch. I am just starting to learn myself VBA so please help. Thanx Private Sub Worksheet_Change(ByVal Target As Range) Dim row As Integer row = Target.row If Target.Column = "NA" Then Cells(row, 10).Clear Cells(row, 11).Clear Cells(row, 13).Clear Cells(row, 14).Clear End If End Sub |
Sorry my suggestion does not work please follows bob advice
tina "tina" wrote: Hi Try If Not Range("N:N") Is Nothing Then in place of If Target.Column = "NA" Then Tina "Esrei" wrote: I need a macro that if I type a value in column N the cells in the same row in coloms M,J and K needs to be cleared. This is what I have at the moment but I get runtime error 13 Type mismatch. I am just starting to learn myself VBA so please help. Thanx Private Sub Worksheet_Change(ByVal Target As Range) Dim row As Integer row = Target.row If Target.Column = "NA" Then Cells(row, 10).Clear Cells(row, 11).Clear Cells(row, 13).Clear Cells(row, 14).Clear End If End Sub |
Tina,
It would if you just extended it to If Not Intersect(Target, Range("N:N")) Is Nothing Then but you must also add the event disbaling/re-enabling code that I added. -- HTH RP (remove nothere from the email address if mailing direct) "tina" wrote in message ... Hi Try If Not Range("N:N") Is Nothing Then in place of If Target.Column = "NA" Then Tina "Esrei" wrote: I need a macro that if I type a value in column N the cells in the same row in coloms M,J and K needs to be cleared. This is what I have at the moment but I get runtime error 13 Type mismatch. I am just starting to learn myself VBA so please help. Thanx Private Sub Worksheet_Change(ByVal Target As Range) Dim row As Integer row = Target.row If Target.Column = "NA" Then Cells(row, 10).Clear Cells(row, 11).Clear Cells(row, 13).Clear Cells(row, 14).Clear End If End Sub |
Thanks Bob
"Bob Phillips" wrote: Tina, It would if you just extended it to If Not Intersect(Target, Range("N:N")) Is Nothing Then but you must also add the event disbaling/re-enabling code that I added. -- HTH RP (remove nothere from the email address if mailing direct) "tina" wrote in message ... Hi Try If Not Range("N:N") Is Nothing Then in place of If Target.Column = "NA" Then Tina "Esrei" wrote: I need a macro that if I type a value in column N the cells in the same row in coloms M,J and K needs to be cleared. This is what I have at the moment but I get runtime error 13 Type mismatch. I am just starting to learn myself VBA so please help. Thanx Private Sub Worksheet_Change(ByVal Target As Range) Dim row As Integer row = Target.row If Target.Column = "NA" Then Cells(row, 10).Clear Cells(row, 11).Clear Cells(row, 13).Clear Cells(row, 14).Clear End If End Sub |
All times are GMT +1. The time now is 04:12 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com