Solved

Application defined or Object defined error

Posted on 2014-01-27

Microsoft Excel

1 solution

545 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

Suggested Solutions

Title	# Comments	Views	Activity
ActiveX component can't create object VBA how to fix it	6	60	2017-05-11
VBA error subscript out of range help	5	29	2017-05-11
EXCEL String-handling question ...	7	54	2017-05-15
Excel - lookup two values and return a value	3	30	2017-05-15

Automated Link Checker

Article by: Josh

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…

Freeze Panes feature

Article by: Rob

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.