?
Solved

Autofill error

Posted on 2011-05-11
7
Medium Priority
?
296 Views
Last Modified: 2012-05-11
Hi,

Im getting an error on the last line of the attached code

".Range("AI2:AT2").Autofill Destination:=.Range("AI2:AT" & r), Type:=xlFillDefault"

It says

"Object Variable or with block variable not set"

Does anyone know why this error is occurring, i recently expanded fromula from AU to AT, but thats the only change

Thanks
Seamus

Sub Autofill()
  Dim r As Long

 

    With Sheets("T-2")
 
 r = .Range("A" & Rows.Count).End(xlUp).Row

Sheets("T-2").Select

Range("AI2").Select
ActiveCell.Formula = "=IF(E2>1000000,""Yes"",""No"")"
Range("AJ2").Select
ActiveCell.Formula = "=IF(AI2=""Yes"",Q2,"""")"
Range("AK2").Select
ActiveCell.Formula = "=IF(AI2=""Yes"",E2,"""")"
Range("AL2").Select
ActiveCell.Formula = "=I2"
Range("AM2").Select
ActiveCell.Formula = "=IF(ISERROR(O2-AF2),"""",(O2-AF2))"
Range("AN2").Select
ActiveCell.Formula = "=IF(AM2<=5,""0-5"",IF(AM2<=15,""6-15"",IF(AM2<=30,""16-30"",IF(AM2<=90,""31-90"",""91+""))))"
Range("AO2").Select
ActiveCell.Formula = "=IF(AI2=""No"","""",IF(AM2<=5,""0-5"",IF(AM2<=15,""6-15"",IF(AM2<=30,""16-30"",IF(AM2<=90,""31-90"",""91+"")))))"
Range("AP2").Select
ActiveCell.Formula = "=IF(AK2<>"""",1,0)"
Range("AQ2").Select
ActiveCell.Formula = "=IF(X2=O2,""Keep"",""Delete"")"




.Range("AI2:AQ2").Autofill Destination:=.Range("AI2:AQ" & r), Type:=xlFillDefault

End With

With Sheets("Raw Data")
   r = .Range("A" & Rows.Count).End(xlUp).Row
   Sheets("Raw Data").Select
Range("AI2").Select
ActiveCell.Formula = "=IF(E2>1000000,""Yes"",""No"")"
Range("AJ2").Select
ActiveCell.Formula = "=IF(AI2=""Yes"",Q2,"""")"
Range("AK2").Select
ActiveCell.Formula = "=IF(AI2=""Yes"",E2,"""")"
Range("AL2").Select
ActiveCell.Formula = "=I2"
Range("AM2").Select
ActiveCell.Formula = "=IF(ISERROR(O2-AF2),"""",(O2-AF2))"
Range("AN2").Select
ActiveCell.Formula = "=IF(AM2<=5,""0-5"",IF(AM2<=15,""6-15"",IF(AM2<=30,""16-30"",IF(AM2<=90,""31-90"",""91+""))))"
Range("AO2").Select
ActiveCell.Formula = "=IF(AI2=""No"","""",IF(AM2<=5,""0-5"",IF(AM2<=15,""6-15"",IF(AM2<=30,""16-30"",IF(AM2<=90,""31-90"",""91+"")))))"
Range("AP2").Select
ActiveCell.Formula = "=IF(AK2<>"""",1,0)"
Range("AQ2").Select
ActiveCell.Formula = "=IF(X2=O2,""Keep"",""Delete"")"
Range("AR2").Select
ActiveCell.Formula = "=VLOOKUP(RC[-34],'Mapping Table'!C[-43]:C[-37],7,0)"
Range("AS2").Select
ActiveCell.Formula = "=IF(P2<=30,""0-30"",""30+"")"
Range("AT2").Select
ActiveCell.Formula = "=RIGHT(C2,6)"
Range("AU2").Select
ActiveCell.Formula = "=IF(H3>30,"">30"",IF(H3>14,""15-30"",""0-14""))"
Range("AV2").Select
ActiveCell.Formula = "=LEFT(J2,3)"


.Range("AI2:AV2").Autofill Destination:=.Range("AI2:AV" & r), Type:=xlFillDefault

End With

Open in new window

0
Comment
Question by:Seamus2626
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 43

Assisted Solution

by:Saqib Husain, Syed
Saqib Husain, Syed earned 668 total points
ID: 35738931
What is the value of r at this point. It must be greater than 2
0
 

Author Comment

by:Seamus2626
ID: 35738966
Hey ssaqibh, theres 3450 lines in the worksheet and ColA at this point

Thanks
Seamus
0
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 668 total points
ID: 35738980
Not just that: the start of the destination range must be >2.  I.e. There cannot be overlap between source range and destination range.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 35738982
Not just that: the start of the destination range must be >2.  I.e. There cannot be overlap between source range and destination range.
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 35739024
Patrick, this is contrary to what the help file says. Check out the example there.
0
 
LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 664 total points
ID: 35739432
I can't see that line in your code at all.
0
 

Author Closing Comment

by:Seamus2626
ID: 35739452
An error in another sub, all good now

Thanks
Seamus
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question