Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I pause a macro to select specific cells | Excel Worksheet Functions | |||
Macro to delete data in 'green' cells only | Excel Worksheet Functions | |||
repeat macro formula to all cells | Excel Discussion (Misc queries) | |||
Clear Contents - NonBold cells | Excel Discussion (Misc queries) | |||
Convert data of cells to any type: Number, Date&Time, Text | Excel Discussion (Misc queries) |