Solved

Application defined or Object defined error

Posted on 2014-01-27

Microsoft Excel

1 solution

560 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 500 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

Excel - Did You Know !

Article by: Shums

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…

Dynamic Data Vaildation

Article by: Rob

After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.

Creating an Efficient Dashboard in Excel

Video by: Katie

Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

Microsoft Excel

Creating an Interactive Chart for Marketers in Excel

Video by: AnnieCushing

This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.