AutoFill Formula with code
 

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.


--
Desert Piranha


------------------------------------------------------------------------
Desert Piranha's Profile: http://www.excelforum.com/member.php...o&userid=28934
View this thread: http://www.excelforum.com/showthread...hreadid=509141

AutoFill Formula with code
 

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" <Desert.Piranha.22tya2_1139266810.6088@excelforu m-nospam.com
wrote in message
news:Desert.Piranha.22tya2_1139266810.6088@excelfo rum-nospam.com...

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.


--
Desert Piranha


------------------------------------------------------------------------
Desert Piranha's Profile:
http://www.excelforum.com/member.php...o&userid=28934
View this thread: http://www.excelforum.com/showthread...hreadid=509141

AutoFill Formula with code
 

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"
<Desert.Piranha.22tya2_1139266810.6088@excelforu m-nospam.com wrote in
message news:Desert.Piranha.22tya2_1139266810.6088@excelfo rum-nospam.com...

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.


--
Desert Piranha


------------------------------------------------------------------------
Desert Piranha's Profile:
http://www.excelforum.com/member.php...o&userid=28934
View this thread: http://www.excelforum.com/showthread...hreadid=509141

AutoFill Formula with code
 

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

Norman Jones wrote:

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"
<Desert.Piranha.22tya2_1139266810.6088@excelforu m-nospam.com wrote in
message news:Desert.Piranha.22tya2_1139266810.6088@excelfo rum-nospam.com...

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.


--
Desert Piranha


------------------------------------------------------------------------
Desert Piranha's Profile:
http://www.excelforum.com/member.php...o&userid=28934
View this thread: http://www.excelforum.com/showthread...hreadid=509141


--

Dave Peterson

AutoFill Formula with code
 

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!

---
Regards,
Norman



"Dave Peterson" wrote in message
...
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

AutoFill Formula with code
 

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

Norman Jones Wrote:
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"
<Desert.Piranha.22tya2_1139266810.6088@excelforu m-nospam.com wrote in
message
news:Desert.Piranha.22tya2_1139266810.6088@excelfo rum-nospam.com...

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.


--
Desert Piranha



------------------------------------------------------------------------
Desert Piranha's Profile:
http://www.excelforum.com/member.php...o&userid=28934
View this thread:
http://www.excelforum.com/showthread...hreadid=509141



--
Desert Piranha


------------------------------------------------------------------------
Desert Piranha's Profile: http://www.excelforum.com/member.php...o&userid=28934
View this thread: http://www.excelforum.com/showthread...hreadid=509141

AutoFill Formula with code
 

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.

Desert Piranha wrote:

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

Norman Jones Wrote:
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"
<Desert.Piranha.22tya2_1139266810.6088@excelforu m-nospam.com wrote in
message
news:Desert.Piranha.22tya2_1139266810.6088@excelfo rum-nospam.com...

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.


--
Desert Piranha



------------------------------------------------------------------------
Desert Piranha's Profile:
http://www.excelforum.com/member.php...o&userid=28934
View this thread:
http://www.excelforum.com/showthread...hreadid=509141


--
Desert Piranha

------------------------------------------------------------------------
Desert Piranha's Profile: http://www.excelforum.com/member.php...o&userid=28934
View this thread: http://www.excelforum.com/showthread...hreadid=509141

--

Dave Peterson

AutoFill Formula with code
 

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:

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

with

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


---
Regards,
Norman


"Desert Piranha"
<Desert.Piranha.22u9dz_1139281202.1139@excelforu m-nospam.com wrote in
message news:Desert.Piranha.22u9dz_1139281202.1139@excelfo rum-nospam.com...

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

AutoFill Formula with code
 

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!


---
Regards,
Norman

AutoFill Formula with code
 

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
Norman Jones Wrote:
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:

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

with

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


---
Regards,
Norman


"Desert Piranha"
<Desert.Piranha.22u9dz_1139281202.1139@excelforu m-nospam.com wrote in
message
news:Desert.Piranha.22u9dz_1139281202.1139@excelfo rum-nospam.com...

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


--
Desert Piranha


------------------------------------------------------------------------
Desert Piranha's Profile: http://www.excelforum.com/member.php...o&userid=28934
View this thread: http://www.excelforum.com/showthread...hreadid=509141

AutoFill Formula with code
 

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)


---
Regards,
Norman

AutoFill Formula with code
 

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
Norman Jones Wrote:
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)


---
Regards,
Norman


--
Desert Piranha


------------------------------------------------------------------------
Desert Piranha's Profile: http://www.excelforum.com/member.php...o&userid=28934
View this thread: http://www.excelforum.com/showthread...hreadid=509141