Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 579
  • Last Modified:

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
0
leezac
Asked:
leezac
  • 3
  • 3
1 Solution
 
mvidasCommented:
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
0
 
leezacAuthor Commented:
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
0
 
mvidasCommented:
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.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
leezacAuthor Commented:
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
0
 
leezacAuthor Commented:
Do I need to close and repost??
0
 
mvidasCommented:
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
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now