asked on

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

mvidas

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

leezac

ASKER

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

mvidas

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.

leezac

ASKER

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

leezac

ASKER

Do I need to close and repost??

ASKER CERTIFIED SOLUTION

mvidas

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial