Vlook up Error 1004

[Sukumar]

I donot know why I am getting Error 1004 in following program: -

Private Sub calc_process()

Dim endofrows_processed, endofcols_processed, counter_processed As Long
Dim PRSD, EMS As Worksheets
Dim Source_val, Lookup_rng As Range

Set PRSD = ThisWorkbook.Worksheets("processed")
Set EMS = ThisWorkbook.Worksheets("ems")

' operations to happen over processed sheet ... first operation

  'find blank cells and delete such rows from sheets
  Sheets("processed").Select
  ActiveSheet.Columns(1).Select
  ActiveSheet.Columns(1).SpecialCells(xlBlanks).EntireRow.Delete   
   
  endofrows_processed = Cells(Rows.Count, 1).End(xlUp).Row
  endofcols_processed = Cells(1, Columns.Count).End(xlToLeft).Column

  Columns("A:A").Select
  Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
  Cells(1, 1).Value = "Date"
   
  Columns("D:D").Select
  Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
  Range("D1").Value = "ID"
   
' operations to happen over processed sheet ... second operation
   
  For counter_processed = 2 To endofrows_processed
   
  With PRSD.Cells(counter_processed, 1)
  .Value = Date - 1
  .NumberFormat = "dd-mmm-yy"
  End With
   
' vlookup program on processed sheet ... FIRST VLOOKUP
   
  Set Source_val = Nothing
  Set Lookup_rng = Nothing
   
  Set Source_val = PRSD.Cells(counter_processed, 3)
   
  Set Lookup_rng = ems.Columns("B:C") 'range on sheet
   
  With PRSD
  .Cells(counter_processed, 4) = Application.WorksheetFunction.VLookup(Source_val, Lookup_rng, 2, False)
  End With
   
  Next counter_processed

End Sub

 

[Chihiro]

When you go to debug mode. Which line is highlighted?

 

[Sukumar]

Hi, error begins on this line

ActiveSheet.Columns(1).SpecialCells(xlBlanks).EntireRow.Delete

Also in vlookup function.

 

[Aravindhan Bharathi]

Selection.EntireRow.SpecialCells(xlBlanks).EntireRow.Delete

 

[Sukumar]

Selection.EntireRow.SpecialCells(xlBlanks).EntireRow.Delete

I thank for your repky, but error 1004

 

[Aravindhan Bharathi]

Sub er()
ActiveSheet.Columns(1).Select
Selection.SpecialCells(xlBlanks).EntireRow.Delete
End Sub

 

[Aravindhan Bharathi]

check this. since in your first code itself you have selected the first column then you have again coded to select column 1. hence there may be issue.