Home   |   Register   |   Rules   |   Calendar   |   Get Daily   |     |  
 


If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > PC based Database Applications > Microsoft Excel > autofill problem in macro

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old 03-22-10, 11:24
chris07tibgs chris07tibgs is offline
Registered User
  
Join Date: Oct 2009
Posts: 185
autofill problem in macro
Hello,

I ran through a macro and it does what I want...sort of. In 3 separate places in this macro I autofilled a formula by double clicking to get it to the last row....figuring it would do what I did instead of save the actual place. Instead, every time I run this macro, it autofills to 107,xxx and that's not what I want. How do I manipulate this to do what I want?

Here is a sample of the code:

Code:
Selection.AutoFill Destination:=Range("R2:R107394")
    Range("R2:R107394").Select
    ActiveWindow.ScrollRow = 149
    ActiveWindow.ScrollRow = 593
    ActiveWindow.ScrollRow = 2219
    ActiveWindow.ScrollRow = 4881
    ActiveWindow.ScrollRow = 10057
    ActiveWindow.ScrollRow = 18782
    ActiveWindow.ScrollRow = 27360
    ActiveWindow.ScrollRow = 38303
    ActiveWindow.ScrollRow = 47029
    ActiveWindow.ScrollRow = 54571
    ActiveWindow.ScrollRow = 61226
    ActiveWindow.ScrollRow = 64627
    ActiveWindow.ScrollRow = 68324
    ActiveWindow.ScrollRow = 70247
    ActiveWindow.ScrollRow = 71873
    ActiveWindow.ScrollRow = 73204
    ActiveWindow.ScrollRow = 72909
    ActiveWindow.ScrollRow = 73204
    ActiveWindow.ScrollRow = 73500
    ActiveWindow.ScrollRow = 73648
    ActiveWindow.ScrollRow = 74240
    ActiveWindow.ScrollRow = 76014
    ActiveWindow.ScrollRow = 78085
    ActiveWindow.ScrollRow = 80894
    ActiveWindow.ScrollRow = 84296
    ActiveWindow.ScrollRow = 86958
    ActiveWindow.ScrollRow = 89472
    ActiveWindow.ScrollRow = 91838
    ActiveWindow.ScrollRow = 93908
    ActiveWindow.ScrollRow = 95683
    ActiveWindow.ScrollRow = 97753
    ActiveWindow.ScrollRow = 98937
    ActiveWindow.ScrollRow = 100267
    ActiveWindow.ScrollRow = 101155
    ActiveWindow.ScrollRow = 102190
    ActiveWindow.ScrollRow = 102782
    ActiveWindow.ScrollRow = 103077
    ActiveWindow.ScrollRow = 103373
    ActiveWindow.ScrollRow = 103669
    ActiveWindow.ScrollRow = 104113
    ActiveWindow.ScrollRow = 104408
    ActiveWindow.ScrollRow = 104852
    ActiveWindow.ScrollRow = 105296
    ActiveWindow.ScrollRow = 105443
    ActiveWindow.ScrollRow = 105591
    ActiveWindow.ScrollRow = 105739
    ActiveWindow.ScrollRow = 106035
    ActiveWindow.ScrollRow = 106183
    ActiveWindow.ScrollRow = 106331
    ActiveWindow.ScrollRow = 106479
    ActiveWindow.ScrollRow = 107070
    ActiveWindow.ScrollRow = 107218
    ActiveWindow.ScrollRow = 107366
    Columns("R:R").ColumnWidth = 11.29
    ActiveWindow.ScrollRow = 107070
    ActiveWindow.ScrollRow = 106035
    ActiveWindow.ScrollRow = 104408
    ActiveWindow.ScrollRow = 102338
    ActiveWindow.ScrollRow = 100711
    ActiveWindow.ScrollRow = 99380
    ActiveWindow.ScrollRow = 98197
    ActiveWindow.ScrollRow = 96275
    ActiveWindow.ScrollRow = 93317
    ActiveWindow.ScrollRow = 88141
    ActiveWindow.ScrollRow = 83704
    ActiveWindow.ScrollRow = 80894
    ActiveWindow.ScrollRow = 80747
    ActiveWindow.ScrollRow = 80599
    ActiveWindow.ScrollRow = 80451
    ActiveWindow.ScrollRow = 80303
    ActiveWindow.ScrollRow = 80155
    ActiveWindow.ScrollRow = 80007
    ActiveWindow.ScrollRow = 79859
    ActiveWindow.ScrollRow = 79711
    ActiveWindow.ScrollRow = 79563
    ActiveWindow.ScrollRow = 78972
    ActiveWindow.ScrollRow = 77345
    ActiveWindow.ScrollRow = 74979
    ActiveWindow.ScrollRow = 70542
    ActiveWindow.ScrollRow = 63888
    ActiveWindow.ScrollRow = 59155
    ActiveWindow.ScrollRow = 52944
    ActiveWindow.ScrollRow = 46289
    ActiveWindow.ScrollRow = 37860
    ActiveWindow.ScrollRow = 31501
    ActiveWindow.ScrollRow = 24846
    ActiveWindow.ScrollRow = 20853
    ActiveWindow.ScrollRow = 18191
    ActiveWindow.ScrollRow = 16268
    ActiveWindow.ScrollRow = 13902
    ActiveWindow.ScrollRow = 12128
    ActiveWindow.ScrollRow = 10353
    ActiveWindow.ScrollRow = 8726
    ActiveWindow.ScrollRow = 6508
    ActiveWindow.ScrollRow = 5177
    ActiveWindow.ScrollRow = 3698
    ActiveWindow.ScrollRow = 2515
    ActiveWindow.ScrollRow = 1332
    ActiveWindow.ScrollRow = 149
    ActiveWindow.ScrollRow = 1
Reply With Quote
  #2 (permalink)  
Old 03-22-10, 12:21
Colin Legg Colin Legg is offline
Registered User
  
Join Date: Sep 2008
Location: London, UK
Posts: 495
Hi,

First of all we can tidy it up a little to make it clearer what's going on.

You don't need all of those ActiveWindow.ScrollRow calls which are recordings of you scrolling up and down the window. Once they're removed, your code becomes:
Code:
    Selection.AutoFill Destination:=Range("R2:R107394")
    Range("R2:R107394").Select
    Columns("R:R").ColumnWidth = 11.29

Quote:
In 3 separate places in this macro I autofilled a formula by double clicking to get it to the last row....figuring it would do what I did instead of save the actual place. Instead, every time I run this macro, it autofills to 107,xxx and that's not what I want. How do I manipulate this to do what I want?
You'll have to add some logic to tell it to fill to the "last row". When you say "last row", do you mean the last row on the worksheet, or do you mean the last used row in the worksheet?

Hope that helps..
__________________
Colin

RAD Excel Blog

Other tutorials:
Array Formulas | Deleting Rows with VBA
Reply With Quote
  #3 (permalink)  
Old 03-22-10, 16:18
chris07tibgs chris07tibgs is offline
Registered User
  
Join Date: Oct 2009
Posts: 185
Quote:
Originally Posted by Colin Legg View Post
Hi,

First of all we can tidy it up a little to make it clearer what's going on.

You don't need all of those ActiveWindow.ScrollRow calls which are recordings of you scrolling up and down the window. Once they're removed, your code becomes:
Code:
    Selection.AutoFill Destination:=Range("R2:R107394")
    Range("R2:R107394").Select
    Columns("R:R").ColumnWidth = 11.29


You'll have to add some logic to tell it to fill to the "last row". When you say "last row", do you mean the last row on the worksheet, or do you mean the last used row in the worksheet?

Hope that helps..
That gave me a good starting point and I was able to get the rest working as desired.

However, I discovered that if my button for the macro is over a cell that gets autofilled, it also autofills the button......Why.........?

How do I keep that from happening, other than just simply moving the button?
Reply With Quote
  #4 (permalink)  
Old 03-23-10, 05:20
Colin Legg Colin Legg is offline
Registered User
  
Join Date: Sep 2008
Location: London, UK
Posts: 495
Hi Chris,

Please can you zip and attach an example workbook for me to see?
__________________
Colin

RAD Excel Blog

Other tutorials:
Array Formulas | Deleting Rows with VBA
Reply With Quote
  #5 (permalink)  
Old 03-25-10, 09:37
chris07tibgs chris07tibgs is offline
Registered User
  
Join Date: Oct 2009
Posts: 185
Quote:
Originally Posted by Colin Legg View Post
Hi Chris,

Please can you zip and attach an example workbook for me to see?
Here it is. I copy all from the .xlsx and paste into .xlsm, then click the button. It's just if I have the button over one of the columns that gets autofilled, like d-f or q-r, it also autofills the button...seems weird to me. The way it is now, it works fine.
Attached Files
File Type: zip packing10324.zip (253.8 KB, 11 views)
Reply With Quote
  #6 (permalink)  
Old 03-25-10, 10:49
Colin Legg Colin Legg is offline
Registered User
  
Join Date: Sep 2008
Location: London, UK
Posts: 495
Hi Chris,

Thanks for the attachment; I understand the problem now.

If you right click on the button and choose Format control, navigate to the Properties Tab and choose "Don't move or size with cells" in the object positioning section, then you should find that the button will not be copied and filled down the column.

Hope that helps...
__________________
Colin

RAD Excel Blog

Other tutorials:
Array Formulas | Deleting Rows with VBA
Reply With Quote
  #7 (permalink)  
Old 03-25-10, 10:55
chris07tibgs chris07tibgs is offline
Registered User
  
Join Date: Oct 2009
Posts: 185
Yes, that got it.

Thank you!
Reply With Quote
Reply

« Previous Thread | Next Thread »
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


 
 
 
Home  |   Register  |   Get New  |   FAQ's  |   Sitemap

Powered by vBulletin
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.