AutoFill Formula with code

[Desert Piranha]

Hi all,

This whole thing needs to change based on the last used cell in column
C.

There is a formula in:
G2 '=IF(D2="","",(D2*F2))
I2 '=IF(D2="","",(H2/G2))
J2 '=IF(E2="","",(H2/E2))

Want to Auto Fill the formulas down, but only down as far as the last
used cell in column C.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 3 Then
With Cells(Rows.Count, 3).End(xlUp).Select
Range("g2").AutoFill Destination:=Range("g3 to ???????")
Range("I2").AutoFill Destination:=Range("I3 to ???????")
Range("J2").AutoFill Destination:=Range("J3 to ???????")
End With
End If
End Sub

Any Direction would be great.

 

[Gary Keramidas]

not exactly your code, but what i have used in the past. you should be avble to
adapt it.


Dim lastRow As Long
lastRow = Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row

Range("g2").Formula = "=sum(b2:f2)"
Range("g2").AutoFill Range("G2:g" & lastRow)

--


Gary


"Desert Piranha" <[email protected]>
wrote in message
news[email protected]...

 

[Norman Jones]

Hi Dave,

Try:

'=============>>
Private Sub Worksheet_Change(ByVal Target As Range)
Dim LRow As Long
If Target.Column = 3 Then
LRow = Cells(Rows.Count, 3).End(xlUp).Row
Range("G2").AutoFill Destination:=Range("G2:G" & LRow)
Range("I2").AutoFill Destination:=Range("I2:I" & LRow)
Range("J2").AutoFill Destination:=Range("J2:J" & LRow)
End If
End Sub
'<<=============


---
Regards,
Norman


"Desert Piranha"

 

[Dave Peterson]

I think I'd disable the events, so that the worksheet change doesn't fire
itself:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim LRow As Long
If Target.Column = 3 Then
LRow = Cells(Rows.Count, 3).End(xlUp).Row
application.enableevents = false
Range("G2").AutoFill Destination:=Range("G2:G" & LRow)
Range("I2").AutoFill Destination:=Range("I2:I" & LRow)
Range("J2").AutoFill Destination:=Range("J2:J" & LRow)
application.enableevents = true
End If
End Sub

Hi Dave,

Try:

'=============>>
Private Sub Worksheet_Change(ByVal Target As Range)
Dim LRow As Long
If Target.Column = 3 Then
LRow = Cells(Rows.Count, 3).End(xlUp).Row
Range("G2").AutoFill Destination:=Range("G2:G" & LRow)
Range("I2").AutoFill Destination:=Range("I2:I" & LRow)
Range("J2").AutoFill Destination:=Range("J2:J" & LRow)
End If
End Sub
'<<=============

 

[Norman Jones]

Hi Dave,

Hi Dave,

I think I'd disable the events, so that the worksheet change doesn't fire
itself

I agree! I altered Dave's autofill syntax and looked no further!

Sloppy, but fortunately you picked it up.

Thank you!

 

[Desert Piranha]

Hi Gary, Dave, Norman,

I got Garys code to kinda work, needs tweeking.

Normans code is copy and paste, Works Great.

With the:

Application.EnableEvents = False
Application.EnableEvents = True

stuff, the code dosn't run so i am leaving it out.

Thx much for all the input.
Dave

Hi Dave,

Try:

'=============>>
Private Sub Worksheet_Change(ByVal Target As Range)
Dim LRow As Long
If Target.Column = 3 Then
LRow = Cells(Rows.Count, 3).End(xlUp).Row
Range("G2").AutoFill Destination:=Range("G2:G" & LRow)
Range("I2").AutoFill Destination:=Range("I2:I" & LRow)
Range("J2").AutoFill Destination:=Range("J2:J" & LRow)
End If
End Sub
'<<=============


---
Regards,
Norman


"Desert Piranha"
message

 

[Dave Peterson]

My bet is that you stopped the code with application.enableevents turned off.

I'd put those lines back into Norman's code.

And I bet he would agree with me that it's better with those lines in it.

 

[Norman Jones]

Hi Dave,

With the:

Application.EnableEvents = False
Application.EnableEvents = True

stuff, the code dosn't run so i am leaving it out.

Turning events off and, subsequently, back on is advisable and works for me.
I would suggest that you retry this.

BTW, the code could be slightly simplified by replacing:

with

Range("I2").AutoFill Destination:=Range("I2:J" & LRow)


---
Regards,
Norman


"Desert Piranha"

 

[Norman Jones]

Hi Dave,

I'd put those lines back into Norman's code.

And I bet he would agree with me that it's better with those lines in it.

Your bet is a winner!

 

[Desert Piranha]

Hi Norm, Dave,
I think i better stick with:

Range("I2").AutoFill Destination:=Range("I2:I" & LRow)
Range("J2").AutoFill Destination:=Range("J2:J" & LRow)
as
Range("I2").AutoFill Destination:=Range("I2:J" & LRow)
gives me:
"AutoFill Method of Range Class Failed"
May have something to do with the fact there are different
formulas in I2 and J2.


I put this 'Application.EnableEvents = True' in a different place and
it
seems to be working so far.
Thx
Dave

Hi Dave,


Turning events off and, subsequently, back on is advisable and works
for me.
I would suggest that you retry this.

BTW, the code could be slightly simplified by replacing:


with

Range("I2").AutoFill Destination:=Range("I2:J" & LRow)


---
Regards,
Norman


"Desert Piranha"
message

 

[Norman Jones]

Hi Dave,

Range("I2").AutoFill Destination:=Range("I2:J" & LRow)
gives me:
"AutoFill Method of Range Class Failed"

My fault! It should be:

Range("I2:J2").AutoFill Destination:=Range("I2:J" & LRow)

 

[Desert Piranha]

Hi Norman,
Yep, that works.
Thanks to you and Dave P. for all your help on this matter.
Its working wonderfully.

and yes i got the:
'Application.EnableEvents = False'
'Application.EnableEvents = True'
working i think.

I need to tweek some border stuff, but its working
Dave

Hi Dave,


My fault! It should be:

Range("I2:J2").AutoFill Destination:=Range("I2:J" & LRow)