Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofill
Hi group!
I have been searching the groups for macro to do exactly what this thread answers: http://groups.google.com/group/micro...76cb3eb?hl=en# I didn't know whether to post to that thread or start another one, so if I have stepped on the protocol, my apologies. I have a spreadsheet where I needed a simpler way to copy a formula down to the last used row than what I'm using (a helper column where the formula result is pasted to the desired column). It works, but lengthy. I tried the macro in the the thread above and this is my variation of it: endRow = Cells(Rows.Count, 20).End(xlUp).Row Range("U3").Autofill Destination:=Range("U3:U" & endRow) It resides on a Worksheet_Change . It copies the formula down correctly, but when a selection is made in Col 20 (col "T"), it recalculates the entire "U" column. So i tried this: If Not Intersect(Target, Range("T:T")) Is Nothing Then endRow = Cells(Rows.Count, 20).End(xlUp).Row Range("U3").Autofill Destination:=Range("U3:U" & endRow) End If It works very well, EXCEPT, after the macro runs, I can no longer select a column, any column, or row. I can select cells, macros work, everything else still works, except the row or column selection. What have I done here? Any ideas will greatly appreciated! Thank you in advance! Ken |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofill
Ken,
Your code runs any and every time a cell changes in column T or when any selection that intersects with column T changes. Do you really want that? Why not autofill on request... by adding a button to the sheet and running the code from it. It the above isn't what you want, post back with all of the code in the sheet module. -- Jim Cone Portland, Oregon USA "Ken" wrote in message Hi group! I have been searching the groups for macro to do exactly what this thread answers: http://groups.google.com/group/micro...76cb3eb?hl=en# I didn't know whether to post to that thread or start another one, so if I have stepped on the protocol, my apologies. I have a spreadsheet where I needed a simpler way to copy a formula down to the last used row than what I'm using (a helper column where the formula result is pasted to the desired column). It works, but lengthy. I tried the macro in the the thread above and this is my variation of it: endRow = Cells(Rows.Count, 20).End(xlUp).Row Range("U3").Autofill Destination:=Range("U3:U" & endRow) It resides on a Worksheet_Change . It copies the formula down correctly, but when a selection is made in Col 20 (col "T"), it recalculates the entire "U" column. So i tried this: If Not Intersect(Target, Range("T:T")) Is Nothing Then endRow = Cells(Rows.Count, 20).End(xlUp).Row Range("U3").Autofill Destination:=Range("U3:U" & endRow) End If It works very well, EXCEPT, after the macro runs, I can no longer select a column, any column, or row. I can select cells, macros work, everything else still works, except the row or column selection. What have I done here? Any ideas will greatly appreciated! Thank you in advance! Ken |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofill
Jim,
I had already changed from the "intersect" to the following: Private Sub Worksheet_Change(ByVal Target As Range) Dim endRow As Long If Target.Column = "T" And Target.Row 3 Then endRow = Cells(Rows.Count, 20).End(xlUp).Row Range("U3").Autofill Destination:=Range("U3:U" & endRow) End If End Sub I actually want the value resulting from the formula in "U" to automatically be entered into "U" without having to click a button. The formula is this and it increments with the rows: =IF(ISNA(VLOOKUP(T3, DELIVERY, 2, FALSE)),"",VLOOKUP(T3, DELIVERY, 2, FALSE)) Changing the macro did straighten out the problem with selection of rows and columns, but not the recalculation of all 8700 values when data is entered into Col "T". That's the big drawback. Just trying to make the spreadsheet better....thanks for your help! Ken |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofill
If Target.Column = "T" should be changed to... If Target.Column = 20
The code runs almost instantly for me and it fills in 8700 rows of vlookup formulas and calculates a new value from my "Delivery" range. This is in xl2002. Question: Why the auto fill? The formulas are already filled down 8700 rows. -- Jim Cone Portland, Oregon USA "Ken" wrote in message Jim, I had already changed from the "intersect" to the following: Private Sub Worksheet_Change(ByVal Target As Range) Dim endRow As Long If Target.Column = "T" And Target.Row 3 Then endRow = Cells(Rows.Count, 20).End(xlUp).Row Range("U3").Autofill Destination:=Range("U3:U" & endRow) End If End Sub I actually want the value resulting from the formula in "U" to automatically be entered into "U" without having to click a button. The formula is this and it increments with the rows: =IF(ISNA(VLOOKUP(T3, DELIVERY, 2, FALSE)),"",VLOOKUP(T3, DELIVERY, 2, FALSE)) Changing the macro did straighten out the problem with selection of rows and columns, but not the recalculation of all 8700 values when data is entered into Col "T". That's the big drawback. Just trying to make the spreadsheet better....thanks for your help! Ken |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Autofill | Excel Discussion (Misc queries) | |||
Autofill Until | Excel Discussion (Misc queries) | |||
Autofill | Excel Worksheet Functions | |||
Autofill: Need to autofill one week block, (5) weekday only into cells. | Excel Discussion (Misc queries) | |||
Q. Autofill question: Can I autofill alpha characters like I can numbers? | Excel Programming |