Why am I getting an object required error?

[nidenikolev]

Working on a script and this line of code keeps coming up with an object required error : .AutoFill Destination:=.Range("AT2:AT" & lastrow)

I also have it labeled towards the bottom of the script, can anyone help out?

Sub Update()
                
                
                    'File Paths
                    Dim Preplan As String
                    Dim PS_Export As String
                    Preplan = "M:\Template2.xlsx"
                    PS_Export = "M:\PS_Export.xlsx"
                
                    'Open WB's
                    Dim PP_WB As Workbook
                    Dim PS_WB As Workbook
                    Set PP_WB = Workbooks.Open(Filename:=Preplan, Password:="")
                    Set PS_WB = Workbooks.Open(Filename:=PS_Export)
                
                    Dim PP_WS As Worksheet
                    Set PP_WS = PP_WB.Sheets("2017 Pre-Planning Emp Detail")
                    
                    Dim PS_WS As Worksheet
                    Set PS_WS = PS_WB.Sheets("ps")
                    
                    Dim AVCell As Long
                
                
                    
                    lastrow = PP_WS.Range("A" & Rows.Count).End(xlUp).Row
                    lastrow2 = PS_WS.Range("A" & Rows.Count).End(xlUp).Row
                
                    Application.ScreenUpdating = False
                
                
                    PP_WB.Activate
                    
                    With PP_WS
                    
                             
                             With .Range("AE2")
                                 .Formula = "=VLOOKUP(A2,[PS_Export.xlsx]ps!$A:$K,11,FALSE)"
                                 .AutoFill Destination:=Range("AE2:AE" & lastrow)
                             End With
                            
                                With .Range("AF2")
                                    .Formula = "=VLOOKUP(A2,[PS_Export.xlsx]ps!$A:$H,8,FALSE)"
                                    .AutoFill Destination:=Range("AF2:AF" & lastrow)
                                End With
                            
                                    With .Range("AG2")
                                        .Formula = "=VLOOKUP(A2,[PS_Export.xlsx]ps!$A:$AY,50,FALSE)"
                                        .AutoFill Destination:=Range("AG2:AG" & lastrow)
                                    End With
                            
                                          With .Range("AH2")
                                            .Formula = "=VLOOKUP(A2,[PS_Export.xlsx]ps!$A:$O,15,FALSE)"
                                            .AutoFill Destination:=Range("AH2:AH" & lastrow)
                                          End With
                            
                                                With .Range("AI2")
                                                    .Formula = "=VLOOKUP(A2,[PS_Export.xlsx]ps!$A:$P,16,FALSE)"
                                                    .AutoFill Destination:=Range("AI2:AI" & lastrow)
                                                End With
        
                    End With
        
                           PS_WB.Activate
                    
                        With PS_WS
                            .Columns("AH:AH").Insert Shift:=xlToRight
                        
    
    
                           With .Range("AH2")
                                .Formula = "=AD2+AG2"
                                .AutoFill Destination:=Range("AH2:AH" & lastrow2)
                                .Range("AH1") = "Variable Comp"
                           End With
    
                            
                        End With
                             
                                PP_WB.Activate
                                               
                                                
                                With PP_WS
                                             
                                            With .Range("AR2")
                                                 .Formula = "=IF(F2=""X"",VLOOKUP(A2,[PS_Export.xlsx]ps!$A:$AH,34,FALSE),(AS2+AU2+AX2))"
                                                 .AutoFill Destination:=Range("AR2:AR" & lastrow)
                                            End With
             
                                                        With .Range("AS2")
                                                                .Formula = "=IF(F2="""",VLOOKUP(A2,[PS_Export.xlsx]ps!$A:$AD,30,FALSE),(AR2-AX2))"
                                                                .AutoFill Destination:=Range("AS2:AS" & lastrow)
                                                        End With
    
                                                                Range("AT:AV").Insert Shift:=xlToRight
                                                                   
                                                                    
                                                                    
                                                                    With .Range("AT2").Formula = "=IF(F2="""",VLOOKUP(A2,[PS_Export.xlsx]ps!$A:$AD,30,FALSE))"
                                                                           
 .AutoFill Destination:=.Range("AT2:AT" & lastrow) <-------**THIS IS THE ROW WHERE I'M GETTING AN OBJECT REQURIED ERROR**

                                                                            .Range("AU2").Formula = "=IF(F2=""X"",AR2-AX2)"
                                                                            .AutoFill Destination:=.Range("AU2:AU" & lastrow)
                                                                            .Range("AV1") = "2017 Planned Annual IC"
                                                                    End With
                                                              
    
                                                                     For AVCell = 2 To lastrow
                                                                      .Range("AV" & AVCell).Value = .Range("AT" & AVCell).Value & .Range("AU" & AVCell).Value
                
                                                                     Next AVCell
    
    
    
    
    
                                                    End With

 

[Fluff]

Wrong

 

[Fluff]

Remove the parts in red & add the parts in blue

   [COLOR=#ff0000]With [/COLOR].Range("AT2").Formula = "=IF(F2="""",VLOOKUP(A2,[PS_Export.xlsx]ps!$A:$AD,30,FALSE))"
   
   [COLOR=#0000ff].Range("AT2")[/COLOR].AutoFill Destination:=.Range("AT2:AT" & LastRow)
   
   .Range("AU2").Formula = "=IF(F2=""X"",AR2-AX2)"
   [COLOR=#0000ff].Range("AU2")[/COLOR].AutoFill Destination:=.Range("AU2:AU" & LastRow)
   .Range("AV1") = "2017 Planned Annual IC"
   [COLOR=#ff0000]End With[/COLOR]

 

[shg]

Establish the reference, insert the formula, do the Autofill:

  With .Range("AT2")
    .Formula = "=IF(F2="""",VLOOKUP(A2,[PS_Export.xlsx]ps!$A:$AD,30,FALSE))"
    .AutoFill Destination:=.Range("AT2:AT" & lastrow)
  End With

Or, ensemble,

  .Range("AT2:AT" & lastrow).Formula = "=IF(F2="""",VLOOKUP(A2,[PS_Export.xlsx]ps!$A:$AD,30,FALSE))"

The formulas will adjust appropriately.

 

[nidenikolev]

.Range("AV" & AVCell).Value = .Range("AT" & AVCell).Value & .Range("AU" & AVCell).Value

Now I'm getting a type mismatch error here

 

[nidenikolev]

@Fluff, I get a mismatch error on this line:

[.Range("AV" & AVCell).Value = .Range("AT" & AVCell).Value & .Range("AU" & AVCell).Value]

how can I fix that?

 

[Fluff]

Do any of the formulas return an error?

 

[nidenikolev]

@Fluff

I Looked at the column and this is how it's laid out:

Column AT number + Column AU formula, column AV spits out (e.g. 48000False)
Column AT blank cell + Column AU formula, column AV spits out (e.g. false48000)

What I wanted to achieve in column AV was this:

I have a column of number values (AT) and a column containing a small number of formulas (AU). The formulas I need to keep are very sparse and populate numbers based on an IF statement criteria in column (AV).
How can I concatenate the numbers (Column AT) & the formulas (Column AU) into Column (AV) so I bring over numbers where they need to be and the select few formulas in the select areas they need to be?
for example, I need it to look like this:

819289
999002
932111
FORMULA
55092938
388888
FORMULA
686888
495380
FORMULA (AS2+AU2)

To reiterate, I need the formulas to stay as formulas, not be converted to values, but I need the values to move over to AV as values.

Is this possible with the For/Loop statement I had at the bottom?

 

[Fluff]

As both AT & AU are formulae, I don't understand what you are saying.

 

[nidenikolev]

@Fluff Sorry!! I omitted my copy+pastevalues section.

So pretend that column AT is copy + xlpastevalues and they're now number values instead of a formula