Application-defined or Object-defined error 1004

[malibu65k]

I get an error 1004 on a line but with the exact same line of code above it, I don't...

What am I doing wrong?


Do While ThisWorkbook.Sheets("Part_List").Cells(rowPartList, 1) <> "End" << No Error here


If (ThisWorkbook.Sheets("Part_List").Cells(rowPartList, 3) = "") Or (ThisWorkbook.Sheets("Part_List").Cells(rowPartList, 3) = "Part number") Then
Rows(rowPartList).EntireRow.Delete
rowPartList = rowPartList - 1
End If
rowPartList = rowPartList + 1
If ThisWorkbook.Sheets("Part_List").Cells(rowPartList - 1, 1) = "End" Then
Exit Do
End If

Loop
wb1.Close



Set wb2 = Workbooks("CHP Facility Inventory.xls")

rowPartList = 4

Do While ThisWorkbook.Sheets("Part_List").Cells(rowPartList, 1) <> "End" << BUT Error Here

rowInventory = 3
Do Until (wb2.Sheets("Inventory List").Cells(rowInventory, 1) = "") And (wb2.Sheets("Inventory List").Cells(rowInventory, 4) = "") And (wb2.Sheets("Inventory List").Cells(rowInventory, 5) = "")

If wb2.Sheets("Inventory List").Cells(rowInventory, 1) <> "" Then
If ThisWorkbook.Sheets("Part_List").Cells(rowPartList, 1) = wb2.Sheets("Inventory List").Cells(rowInventory, 1) Then
ThisWorkbook.Sheets("Part_List").Cells(rowPartList, 1).Interior.ColorIndex = 4
End If
End If

rowInventory = rowInventory + 1

Loop

rowPartList = rowPartList = 1

Loop

 

[PHV]

What are ThisWorkbook and wb1 ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

Use this technique to help DEBUG your issues:

faq707-4594

BTW, If you close wb1 then why not use wb1 again rather than creating another workbook object? Minor point.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[malibu65k]

wb1 was...

Set wb1 = Workbooks("AMSRRList.XLSX")

ThisWorkbook is my current workbook where the code is...
I created wb2 before I decided to close wb1. I will eventually use wb1 while opening and closing each workbook after its use.


I got the one line to work by changing it to...

Dim i as integer

Do While ThisWorkbook.Sheets(1).Cells(i, 1) <> "End"

I changed the rowPartList to an i. I felt like I should have been able to reuse rowPartList but it didn't like it. Maybe it can't be reassigned after using it twice. LOL!!!

Any ideas why this would be a problem?

 

[SkipVought]

Row index numbers in Excel ought to be declared as LONG rather than Integer.

When I code a loop, it is usually a column that has data in every cell in the table and ...

dim r as range

with SomeSheetOBJECT
  for each r in .range(.[A2], .[A2].end(xldown))

  next
end with

or idenically...

dim r as range

with SomeSheetOBJECT
  for each r in .range(.cells(2, 1), .cells(2, 1).end(xldown))

  next
end with

This range construct give you the same results as
[tt]
select A2
[Shift]+{ [End] [Down Arrow] }
[/tt]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}