Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > AutoFill Formula with code
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default 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

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,494
Default 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



Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,302
Default 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



Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 35,218
Default 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
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,302
Default 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


Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default 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

Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 35,218
Default 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
Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,302
Default 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


Reply With QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,302
Default 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




Reply With QuoteReply With Quote
  #10   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default 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

Reply With QuoteReply With Quote
  #11   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,302
Default 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


Reply With QuoteReply With Quote
  #12   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default 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

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Autofill data based on an assigned code/prefix anrcreations Excel Worksheet Functions 2 February 11th 10 07:50 PM
Autofill formula to next row ASU Excel Discussion (Misc queries) 4 September 12th 06 11:29 PM
Autofill: Need to autofill one week block, (5) weekday only into cells. dstock Excel Discussion (Misc queries) 1 June 17th 05 08:21 PM
VBA code to Autofill one cell to many rows below where row count will change TrainingGoddess Excel Programming 5 March 28th 05 03:53 AM
Q. Autofill question: Can I autofill alpha characters like I can numbers? George[_22_] Excel Programming 5 August 7th 04 10:33 AM


All times are GMT +1. The time now is 06:52 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement