Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

Solved

Application defined or Object defined error

Posted on 2014-01-27

Microsoft Excel

1 solution

Medium Priority

568 Views

Last Modified: 2014-01-29

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

Comment

Question by:leezac

[X]

Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

Help others & share knowledge
Earn cash & points
Learn & ask questions

Join The Community

6 Comments

LVL 35

Expert Comment

by:mvidas

ID: 398127022014-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

Author Comment

by:leezac

ID: 398127552014-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

Expert Comment

by:mvidas

ID: 398128022014-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.

Author Comment

by:leezac

ID: 398130232014-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

Author Comment

by:leezac

ID: 398133132014-01-27

Do I need to close and repost??

LVL 35

Accepted Solution

by:mvidas

mvidas earned 2000 total points

ID: 398133322014-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

Featured Post

Question has a verified solution.

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

How to calculate Duration from text and number cell value

Article by: Shums

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.

Automating Microsoft Excel from Access (Part I)

Article by: Dale Fye

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications. You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…

Changing Sort Order for Chart Legend Values in Excel

Video by: AnnieCushing

This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

Microsoft Excel

Creating Interactive Pivot Charts in Excel

Video by: AnnieCushing

This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.