Macro

N

nc

I tried running this macro but it fails, any solution?



Private Sub Worksheet_Change(ByVal Target As Range)

Range("J1").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[1]:RC[6])"
Selection.AutoFill Destination:=Range("J1:J26"),
Type:=xlFillDefault
Range("A1").Select
End Sub


Thnaks.
 
D

Dave Peterson

The worksheet_change event seems like an unusual spot for this macro.

This event fires whenever you change something on that worksheet. And using
autofill changes the worksheet--which causes the event to fire (again and again
and again).

Try changing the name of the procedure and put it in a general module--not
behind the worksheet:

Your code worked ok for me when I did that:

Option Explicit

Sub FillTheRange()
Range("J1").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[1]:RC[6])"
Selection.AutoFill Destination:=Range("J1:J26"), Type:=xlFillDefault
Range("A1").Select
End Sub

And another way is to just fill the range directly:

Sub FillTheRange2()
Range("J1:J26").FormulaR1C1 = "=SUM(RC[1]:RC[6])"
End Sub

(with no selections)

I tried running this macro but it fails, any solution?

Private Sub Worksheet_Change(ByVal Target As Range)

Range("J1").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[1]:RC[6])"
Selection.AutoFill Destination:=Range("J1:J26"),
Type:=xlFillDefault
Range("A1").Select
End Sub

Thnaks.
Click to expand...
 
N

nc

Dave

Thanks for your response.

What I was trying achieve is run this macro automatically
when a cell in that range is deleted/cleared
accidentally. I cannot use protect as I need to run an
addin that does not work with protected sheets.

-----Original Message-----
The worksheet_change event seems like an unusual spot for this macro.

This event fires whenever you change something on that worksheet. And using
autofill changes the worksheet--which causes the event to fire (again and again
and again).

Try changing the name of the procedure and put it in a general module--not
behind the worksheet:

Your code worked ok for me when I did that:

Option Explicit

Sub FillTheRange()
Range("J1").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[1]:RC[6])"
Selection.AutoFill Destination:=Range("J1:J26"), Type:=xlFillDefault
Range("A1").Select
End Sub

And another way is to just fill the range directly:

Sub FillTheRange2()
Range("J1:J26").FormulaR1C1 = "=SUM(RC[1]:RC[6])"
End Sub

(with no selections)

I tried running this macro but it fails, any solution?

Private Sub Worksheet_Change(ByVal Target As Range)

Range("J1").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[1]:RC[6])"
Selection.AutoFill Destination:=Range("J1:J26"),
Type:=xlFillDefault
Range("A1").Select
End Sub

Thnaks.
Click to expand...

--

Dave Peterson
[email protected]
.
Click to expand...
 
D

Dave Peterson

Maybe it would better to check that range explicitly:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myCell As Range
Dim myRng As Range

Set myRng = Intersect(Target, Me.Range("j1:j26"))

If myRng Is Nothing Then
Exit Sub
End If

On Error GoTo errHandler:
Application.EnableEvents = False
For Each myCell In myRng.Cells
myCell.FormulaR1C1 = "=SUM(RC[1]:RC[6])"
Next myCell

errHandler:
Application.EnableEvents = True

End Sub

This just plops that formula back into the cell in J1:J26 (whether it needs it
or not).


Dave

Thanks for your response.

What I was trying achieve is run this macro automatically
when a cell in that range is deleted/cleared
accidentally. I cannot use protect as I need to run an
addin that does not work with protected sheets.
-----Original Message-----
The worksheet_change event seems like an unusual spot for this macro.

This event fires whenever you change something on that worksheet. And using
autofill changes the worksheet--which causes the event to fire (again and again
and again).

Try changing the name of the procedure and put it in a general module--not
behind the worksheet:

Your code worked ok for me when I did that:

Option Explicit

Sub FillTheRange()
Range("J1").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[1]:RC[6])"
Selection.AutoFill Destination:=Range("J1:J26"), Type:=xlFillDefault
Range("A1").Select
End Sub

And another way is to just fill the range directly:

Sub FillTheRange2()
Range("J1:J26").FormulaR1C1 = "=SUM(RC[1]:RC[6])"
End Sub

(with no selections)

I tried running this macro but it fails, any solution?

Private Sub Worksheet_Change(ByVal Target As Range)

Range("J1").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[1]:RC[6])"
Selection.AutoFill Destination:=Range("J1:J26"),
Type:=xlFillDefault
Range("A1").Select
End Sub

Thnaks.
Click to expand...

--

Dave Peterson
[email protected]
.
Click to expand...
Click to expand...
 
Top