Solved

Posted on 2011-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

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

".Range("AI2:AT2").Autofil

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
```

Title | # Comments | Views | Activity |
---|---|---|---|

Third Sunday of the Month | 10 | 50 | |

VBA filters | 2 | 23 | |

Excel VBA, find a string in a column, update a cell | 7 | 24 | |

Dynamic Bar chart in Excel | 5 | 14 |

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

Connect with top rated Experts

**22** Experts available now in Live!