VBA code copying formula not running correctly

[Angus Beaumont]

I am using a piece of code to add a formula when the value in cell E6
is changed, te idea is that it adds a formula to Column B only as far
as there is data in column A. i.e. if data stops at A20 then the
formula in column B should end at B20.

the problem is that what it does instead is copy all the way down to
the end of the column, strangely in some circumstances it does work
usually when there is already data in there and I am changing E6 for
the second time rather than the initial add of the formulas

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$E$6" And Not IsEmpty(Target) Then
Range("B10", Range("B10").End(xlDown)).Formula = "=C10*(1-$E
$6)"
lastrow = Range("A65536").End(xlUp).Row
Range("B10").AutoFill Destination:=Range("B10:B" & lastrow),
Type:=xlFillDefault
End If
End Sub


thanks

 

[GS]

Try...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$E$6" And Not IsEmpty(Target) Then
Dim lLastRow As Long
lLastRow = Cells(Rows.Count, 1).End(xlUp).Row
Range("B10:B" & lLastRow).Formula = "=C10*(1-$E$6)"
End If
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[Angus Beaumont]

Try...

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address = "$E$6" And Not IsEmpty(Target) Then
    Dim lLastRow As Long
    lLastRow = Cells(Rows.Count, 1).End(xlUp).Row
    Range("B10:B" & lLastRow).Formula = "=C10*(1-$E$6)"
  End If
End Sub

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup!
    comp.lang.basic.visual.misc
    microsoft.public.vb.general.discussion

that works perfectly, thank you

 

[GS]

Angus Beaumont formulated on Monday :

that works perfectly, thank you

You're welcome! I appreciate the feedback...

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion