excel - Object Required Error On For Each Loop Only After Importing New Data

Question

For my macro, I import two spreadsheets of data using file.officedialog objects (copying only values, some data validation) and then run a primary loop using counter c that outputs an employee's name, ID, supervisor, performance, job function, etc. on sheet1, data coming from sheets 2 (performance) and 3 (employee details). I run a secondary loop using counter d for outputting the respective job functions of each employee since they may work in multiple departments throughout the day (pick, pack, sort, etc.). The secondary loop goes backwards until there's an empty cell because of how the data is formatted and performing a vlookup using user ID:

https://imgur.com/BpPTF0x

Here's an example of what the output looks like. I haven't fine tuned how output in column G will look like. Have to find a way to remove duplicates within the cell. Anyways, I digress.

https://imgur.com/i6Fo6wy

When I run the macro and choose the two excel files, I receive an object required error on "For Each cell in irange.Cells". However, since at that point the data has been copied into sheets 2 and 3, if I run the macro again but this time cancel twice and not choose any data, the macro runs without errors and outputs the data as it should. I would like to find the source of my problem and understand why it's giving me an error.

Most object required errors I've witnessed in forums deal with inappropriate uses (or lack thereof) of set or using a property incorrectly, but this doesn't appear to be similar to that. I've looked over the code, tried other loops (For i = lastrow to 1 Step 1 (or Step -1)). I tried alternative conditions for my secondary while loop to end. I've looked through my file dialog objects and associating code to make sure data is being imported correctly.

Sub Work_Project()

Dim LastRow_RP1 As Long, LastRow_RP2 As Long, _ 
fd1 As Office.FileDialog, fd2 As Office.FileDialog, _
RP As String, Import As Workbook, cell As Range, irange As Range, _
c As Long, d As Long, data1 As Range, data2 As Range

LastRow_RP1 = Sheet2.Range("A" & Rows.Count).End(xlUp).Row
LastRow_RP2 = Sheet3.UsedRange.Rows.Count
LastRow = Sheet1.UsedRange.Rows.Count
Set fd1 = Application.FileDialog(msoFileDialogFilePicker)
Set fd2 = Application.FileDialog(msoFileDialogFilePicker)
Set irange = Sheet2.Range("$B$7:$B" & LastRow_RP1)

'Importing data from exported spreadsheet into Sheet2

With fd1
  .AllowMultiSelect = False
  .Title = "Please select the Red Prairie Summary Report file."
  .Filters.Clear
  .Filters.Add "Excel Files", "*.xls;*.xlsx;*.xlsm"
  If .Show = True Then
    RP = .SelectedItems(1)
    Set Import = Workbooks.Open(RP)
    Sheet2.Cells.Delete
    Set data1 = Import.Sheets(1).Range("$A$1:$V" & Sheets(1).Range("A" & 
    Rows.Count).End(xlUp).Row)
    Sheet2.Range("A1").Resize(data1.Rows.Count, data1.Columns.Count) _ 
    .Value = data1.Value
    Sheet2.Range("$A$1:$V" & Sheets(1).Range("A" & 
    Rows.Count).End(xlUp).Row).RemoveDuplicates _ 
    Columns:=Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, _ 
    15, 16, 17, 18), Header:=xlNo
    Import.Close False
  End If
End With

'Importing data from exported spreadsheet into Sheet3

With fd2
  .AllowMultiSelect = False
  .Title = "Please select the Red Prairie Authorization Maintenance file."
  .Filters.Clear
  .Filters.Add "Excel Files", "*.xls;*.xlsx;*.xlsm"
  If .Show = True Then
    RP = .SelectedItems(1)
    Set Import = Workbooks.Open(RP)
    Sheet3.Cells.Delete
    Set data2 = Import.Sheets(1).Range("$A$1:$AG" & _
    Sheets(1).UsedRange.Rows.Count)
    Sheet3.Range("A1").Resize(data2.Rows.Count, data2.Columns.Count) _ 
    .Value = data2.Value
    Import.Close False
  End If
End With

With Sheet1

'Undoing existing filtering, clearing existing output cells on sheet1, 
'unprotecting sheet

c = 4
d = -1
***For Each cell In irange.Cells***
  If cell.Value = UCase(cell.Value) And IsEmpty(cell.Value) = False Then
   Sheet1.Range("$A" & c).Value = Application.VLookup(cell.Value, _ 
   Sheet3.Range("$A$2:$AG" & LastRow_RP2), 33, False)
   Sheet1.Range("$B" & c).Value = Application.VLookup(cell.Value, _ 
   Sheet3.Range("$A$2:$AG" & LastRow_RP2), 19, False)
   Sheet1.Range("$C" & c).Value = Application.VLookup(cell.Value, _ 
   Sheet3.Range("$A$2:$AG" & LastRow_RP2), 27, False)
   Sheet1.Range("$D" & c).Value = cell.Offset(0, 1).Value
     If cell.Offset(0, 10).Value = 0 Then
       Sheet1.Range("$E" & c).Value = 0
     Else
       Sheet1.Range("$E" & c).Value = 100 * cell.Offset(0, 3).Value / _ 
       cell.Offset(0, 10).Value
     End If
     Sheet1.Range("$F" & c).Value = cell.Offset(0, 3).Value

   While IsEmpty(cell.Offset(d, 0)) = False
         If IsEmpty(Sheet4.Range("$G" & c).Value) = True Then
            Sheet1.Range("$G" & c).Value = _ 
            WorksheetFunction.IfError(Application.VLookup _
            (cell.Offset(d, 0).Value, Sheet4.Range("$AY$2:$AZ$50"), _ 
            2, False), "N/A")
         Else
            Sheet1.Range("$G" & c).Value = Sheet1.Range("$G" & c) _
            .Value & " " & WorksheetFunction.IfError(Application.VLookup _ 
            (cell.Offset(d, 0).Value, Sheet4.Range("$AY$2:$AZ$50"), _ 
            2, False), "")
         End If

         d = d - 1
   Wend

   c = c + 1
   d = -1
   End If
Next cell

'Sorting, filtering, and enabling protection

End With

End Sub

When I run the macro with data imported, I get error 424: Object Required. If I run the macro with existing data in sheets 2 and 3, I get the correct output.

标签： excelvba

Answer 1

You'll get an Object Required Error if you Set a Range, delete it, and then try to later use it again, which currently happens:

Set irange = Sheet2.Range("$B$7:$B" & LastRow_RP1)
....
Sheet2.Cells.Delete

If you're importing new data into the sheet, you should Set irange after you've done the import, since presumably the UsedRange of Sheet2 is now different.