Autofill

[Ken]

Hi group!
I have been searching the groups for macro to do exactly what this
thread answers:
http://groups.google.com/group/micr.../browse_thread/thread/18eeef8d976cb3eb?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

 

[Jim Cone]

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" <[email protected]>
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/micr.../browse_thread/thread/18eeef8d976cb3eb?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

 

[Ken]

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

 

[Jim Cone]

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" <[email protected]>
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