Application defined or Object defined error

When I step through this code, I get this error Application defined or Object defined error on this line Range("C3").AutoFill Destination:=Range("C3:C" & Lastrow)
and sometimes the yellow line goes to another procedure that is not even called or associated to this one. I think it has to do with the Range but not sure how to fix. Thanks in advance.

Sub InsertVS()
Dim Lastrow As Long

Application.ScreenUpdating = False

UnProt Sheet27.Name

Sheet27.Activate

Sheet27.Range("C:D").EntireColumn.Insert

Sheet27.Range("C2").Select
ActiveCell.FormulaR1C1 = "Security Employee"
Sheet27.Range("D2").Select
ActiveCell.FormulaR1C1 = "ID"
Sheet27.Range("D3").Select

Lastrow = Cells(Rows.Count, "B").End(xlUp).Row

Sheet27.Range("C3").FormulaR1C1 = _
"=(VLOOKUP(B3,'Vendor'!$E$2:G497,2,FALSE))"

Sheet27.Range("C3").Select

Range("C3").AutoFill Destination:=Range("C3:C" & Lastrow)

Sheet27.Range("D3").FormulaR1C1 = _
"=VLOOKUP(B3,'CD Unique Hold Vendor'!$E$2:H497,3,FALSE)"

Sheet27.Range("D3").Select

Sheet27.Range("D3").AutoFill Destination:=Range("D3:D" & Lastrow)
Application.ScreenUpdating = True

End Sub

leezac

Asked: 2014-01-27

Advertise Here

Microsoft Excel

1 Solution

LVL 35

mvidasCommented: 2014-01-27

You can insert your formula without autofill. As an example:

'    Sheet27.Range("C3").FormulaR1C1 = _
'        "=(VLOOKUP(B3,'Vendor'!$E$2:G497,2,FALSE))"
'    Sheet27.Range("C3").Select
'    Range("C3").AutoFill Destination:=Range("C3:C" & LastRow)

   Sheet27.Range("C3:C" & LastRow).FormulaR1C1 = "=(VLOOKUP(B3,'Vendor'!$E$2:G497,2,FALSE))"

Open in new window

Along that line, you can also remove the need to select anything in your subroutine:

Sub InsertVS()
    Dim Lastrow As Long

    Application.ScreenUpdating = False
    
    UnProt Sheet27.Name

    Sheet27.Range("C:D").EntireColumn.Insert
    Sheet27.Range("C2").FormulaR1C1 = "Security Employee"
    Sheet27.Range("D2").FormulaR1C1 = "ID"
  
    Lastrow = Sheet27.Cells(Sheet27.Rows.Count, "B").End(xlUp).Row
 
    Sheet27.Range("C3:C" & Lastrow).FormulaR1C1 = "=(VLOOKUP(B3,'Vendor'!$E$2:G497,2,FALSE))"
    Sheet27.Range("D3:D" & Lastrow).FormulaR1C1 = "=VLOOKUP(B3,'CD Unique Hold Vendor'!$E$2:H497,3,FALSE)"
 
    Application.ScreenUpdating = True
End Sub

Open in new window

Matt

leezacAuthor Commented: 2014-01-27

That seems to work but I am also having to use this code to replace the "=" because the formula stays a formul not the value. How would I change this for C and D columns???

Range("C3").Select
ActiveCell.Replace What:="=", Replacement:="=", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

LVL 35

mvidasCommented: 2014-01-27

I'm guessing that column B must be a text format, so that is becoming the format for the newly inserted column C. You can make column C a format of General after inserting it:

Sheet27.Range("C:D").NumberFormat = "General"

Open in new window

Just make sure to do that BEFORE entering the formulas into those cells.

leezacAuthor Commented: 2014-01-27

No I need to copy down the "=" -- It seems to be the only thing that converts to value. I mean do a find and replace

leezacAuthor Commented: 2014-01-27

Do I need to close and repost??

LVL 35

mvidasCommented: 2014-01-27

I can help, I just want to make sure I understand. You're running the .NumberFormat line before inserting the .FormulaR1C1, and not afterwards, right?

Sub InsertVS()
    Dim Lastrow As Long

    Application.ScreenUpdating = False
    
    UnProt Sheet27.Name

    Sheet27.Range("C:D").EntireColumn.Insert
    Sheet27.Range("C:D").NumberFormat = "General"
    Sheet27.Range("C2").FormulaR1C1 = "Security Employee"
    Sheet27.Range("D2").FormulaR1C1 = "ID"
  
    Lastrow = Sheet27.Cells(Sheet27.Rows.Count, "B").End(xlUp).Row
 
    Sheet27.Range("C3:C" & Lastrow).FormulaR1C1 = "=(VLOOKUP(B3,'Vendor'!$E$2:G497,2,FALSE))"
    Sheet27.Range("D3:D" & Lastrow).FormulaR1C1 = "=VLOOKUP(B3,'CD Unique Hold Vendor'!$E$2:H497,3,FALSE)"
 
    Application.ScreenUpdating = True
End Sub

Open in new window

If so, then there is not a good reason that you should have to do the find/replace to fix the cells.

If you want to amend your fix to cover both columns, just quantify the Sheet/range in place of activecell
Sheet27.Range("C:D").Replace What:="=", Replacement:="=", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Expand your skills with this month's free Premium course.

Application defined or Object defined error

Join & Write a Comment Already a member? Login.

Featured Post