Completely Boggled

This one has me banging my head against the wall cause I see no point why it does this. The following macro is used in my Copier Repair Center where I keep an inventory of Copiers that I repair. This macro is to remove a copier from the inventory and for some dang (I really want to use stronger language but I will refrain) whenever it gets to the point where it deletes the entire row it suddenly jumps to a completely different macro. It is the same macro every time but I don't see any reason why it would do this... Someone assist me please before I have a serious concussion. I have tried activating the cell underneath and having it erase the row just above it but that hasn't worked either. Here's the code with brief explanations.

*** Starts on the Copier Inventory Control Page ***
Sub Copier_Remove()
Range("D17").Select
Mdl = ActiveCell.Text *** Obtains Model ***
Range("G17").Select
Srl = ActiveCell.Text *** Obtains Serial Number ***
Chck = MsgBox("WARNING! This will remove the " & Mdl & " copier with serial number " & Srl & " from your inventory, do you wish to continue?", vbOKCancel)
If Chck = vbOK Then
Sheets("Copier Inventory").Select
Application.Range("B1").Select

Else
Exit Sub


End If


Line2:
Do
ActiveCell.Offset(1, 0).Select
If ActiveCell.Value = "" Then GoTo Line1

Loop Until ActiveCell.Value = Srl

If ActiveCell.Offset(0, -1).Value = Mdl Then *** Double Checks that Model and Serial Match ***
ActiveCell.EntireRow.Delete *** This is where it completely jumps to another Macro ***
Sheets("Copier Inventory Main").Select
A = MsgBox("The " & Mdl & " copier with serial number " & Srl & " has been deleted from your inventory.", vbInformation)
Exit Sub

Else
A = MsgBox("The Model and Serial number did not match in my search. I will now double check the results.", vbCritical)
GoTo Line2

End If

Line1:
Sheets("Copier Inventory Main").Select
B = MsgBox("Was unable to locate proper serial number and model match. Please check your inventory to ensure it is correct.", vbCritical)

End Sub

swatsp0p stated

If ActiveCell.Value = "" Then GoTo Line1 ****when does this IF stop?****

The if your refering to stops pretty much right there as it directs itself to Line1:

Line1:
Sheets("Copier Inventory Main").Select
B = MsgBox("Was unable to locate proper serial number and model match. Please check your inventory to ensure it is correct.", vbCritical)

End Sub

This was designed to keep the macro from running endlessly when it came to the end of the inventory. That is why it was contained within this loop

Do
ActiveCell.Offset(1, 0).Select
If ActiveCell.Value = "" Then GoTo Line1

Loop Until ActiveCell.Value = Srl

This loop would cause 1 of 2 conditions. Either it would find the serial number and move off to check and see if the model matched the serial and then delete it, or it would search until it came to the end of the inventory.



swatsp0p stated

ActiveCell.EntireRow.Delete *** This is where it completely jumps to another Macro ***####Does this create a "" case?####


If you follow the path on this, once it deletes the row, it should jump to the next line in the code.

If ActiveCell.Offset(0, -1).Value = Mdl Then *** Double Checks that Model and Serial Match ***
ActiveCell.EntireRow.Delete *** This is where it completely jumps to another Macro ***
Sheets("Copier Inventory Main").Select
A = MsgBox("The " & Mdl & " copier with serial number " & Srl & " has been deleted from your inventory.", vbInformation)
Exit Sub

Once it deletes the row, it should jump over to the main control sheet for my inventory, pop up a message stating that it has been deleted, then exit the macro. For some reason it doesn't get to changing over the the Copier Inventory Main sheet. So the row delete doesn't have a chance to cause a "" case.

Hello Janos,

I wasn't referring to the Object Browser but the Visual Basic Editor. After you have opened the VBE using Alt + F11 from Excel, up in the left corner under the menu bar you should see a window labeld "Project - VBA Project". This is the Project Explorer Window. If it isn't visible then use Ctrl + R to display it. This will show you the objects in your Workbook.

Double Left Click on "ThisWorkbook". This will display the code on the right side. Above the code window are 2 list boxes displaying "(General)" and "(Declarations)" . Click the Down Arrow on the list box with "(General)". In the list you will see "Workbook" . Click "Workbook". The boxes will display "Workbook" and "Open" . This will show you code for the object "Workbook" and it's event "Open". Click the down arrow on "Open" and you will see a complete list of events that the object "Workbook" recognizes. Select one and click it to see if there is any code for the event. Repeat this for the Worksheet.

If I had known you were First Class Fire Control Tech, I would have done this first time. Just kidding. I was Second Class Display DS myself. Did you change rates so you blow stuff up when didn't work right? I served aboard the Midway, Towers, and Reeves. How about you? Let me know.

Hope this answers some questions,
Leith Ross

Hello Janos,

Why don't you e-mail the workbook to me and I can delve into the problem for you. My e-mail is LeithRoss@aol.com.

Thanks,
Leith Ross