VBA variable assigned value changes automatically

[bsg001]

Hello,

I am creating an application that alters reservation database information for a restaurant. The variables are all declared public and used in multiple procedures. I am having an issue while running a certain code that assigns reservations to a table. The program updates a record in the reservation with table information and guest count information. The problem I am having is the variable assigned to the row number increases value after updating the adult count column. So the next column to update, which is the junior count, gets updated on the next row of the database rather than the current/correct row. There is no code in between that would cause the row number to change. I am posting the code below and would appreciate someone's help. The variable in question is "row1".

Application.ScreenUpdating = False
On Error GoTo Error:
If Range("Tables_eFloorplan").Find(What:="*," & t1 & "(*", LookIn:=xlValues) Is Nothing Then
gc1 = Sheets("Reservations").Cells(row1, Range("Count_PartyActual").Column) - Sheets("Reservations").Cells(row1, Range("Count_PartyAssigned").Column)
ac1 = Sheets("Reservations").Cells(row1, Range("Count_AdultActual").Column) - Sheets("Reservations").Cells(row1, Range("Count_AdultAssigned").Column)
cc1 = Sheets("Reservations").Cells(row1, Range("Count_ChildActual").Column) - Sheets("Reservations").Cells(row1, Range("Count_ChildAssigned").Column)
ic1 = Sheets("Reservations").Cells(row1, Range("Count_InfantActual").Column) - Sheets("Reservations").Cells(row1, Range("Count_InfantAssigned").Column)
If Sheets("Reservations").Cells(row1, Range("Tables_eFloorplan").Column).value = vbNullString Then
Sheets("Reservations").Cells(row1, Range("Tables_eFloorplan").Column) = "," & t1 & "(" & gc1 & "/" & ac1 & "a0j" & cc1 & "c" & ic1 & "i)"
Else
Sheets("Reservations").Cells(row1, Range("Tables_eFloorplan").Column) = Sheets("Reservations").Cells(row1, Range("Tables_eFloorplan").Column) & "," & t1 & "(" & gc1 & "/" & ac1 & "a0j" & cc1 & "c" & ic1 & "i)"
End If
Else
MsgBox "Table already assigned"
End If
Sheets("Reservations").Cells(row1, Range("Count_AdultAssigned").Column) = Sheets("Reservations").Cells(row1, Range("Count_AdultAssigned").Column) + ac1
Sheets("Reservations").Cells(row1, Range("Count_ChildAssigned").Column) = Sheets("Reservations").Cells(row1, Range("Count_ChildAssigned").Column) + cc1
Sheets("Reservations").Cells(row1, Range("Count_InfantAssigned").Column) = Sheets("Reservations").Cells(row1, Range("Count_InfantAssigned").Column) + ic1
Sheets("Reservations").Cells(row1, Range("Status_Reservation").Column).value = "Assigned"
With Application.WorksheetFunction
Sheets("Reservations").Cells(row1, Range("Notes_History").Column).value = "Assigned to table " & t1 & "  by [" & .Index(Range("User_LAN"), .Match(Range("User_Active"), Range("User_PERNER"), 0)) & " : " & Now & "]" & Chr(10) & Chr(10) & Sheets("Reservations").Cells(row1, Range("Notes_History").Column).value
End With
Run "Selection_Clear"
Exit Sub
Error:
subname = "'Tables_Assign'"
Run "Error_Report"
End Sub

The error occurs after completing line

Sheets("Reservations").Cells(row1, Range("Count_AdultAssigned").Column) = Sheets("Reservations").Cells(row1, Range("Count_AdultAssigned").Column) + ac1

and changes at line

Sheets("Reservations").Cells(row1, Range("Count_ChildAssigned").Column) = Sheets("Reservations").Cells(row1, Range("Count_ChildAssigned").Column) + cc1

Thanks for your help!

 

[Norie]

Where do you set the value of row1?

Are you sure no other code is being executed?

Perhaps some sheet based event code?

PS What happens when you remove On Error Resume Next?

 

[Jonmo1]

Yep, I would guess there is a Worksheet_Change event procedure on the Reservations sheet.
And that is triggered when you put a value in a cell of that sheet.


Try disabling events at the beginning of the code, and re-enabling at the end.

Application.ScreenUpdating = False
Application.EnableEvents = False
On Error GoTo Error:
If Range("Tables_eFloorplan").Find(What:="*," & t1 & "(*", LookIn:=xlValues) Is Nothing Then
gc1 = Sheets("Reservations").Cells(row1, Range("Count_PartyActual").Column) - Sheets("Reservations").Cells(row1, Range("Count_PartyAssigned").Column)
ac1 = Sheets("Reservations").Cells(row1, Range("Count_AdultActual").Column) - Sheets("Reservations").Cells(row1, Range("Count_AdultAssigned").Column)
cc1 = Sheets("Reservations").Cells(row1, Range("Count_ChildActual").Column) - Sheets("Reservations").Cells(row1, Range("Count_ChildAssigned").Column)
ic1 = Sheets("Reservations").Cells(row1, Range("Count_InfantActual").Column) - Sheets("Reservations").Cells(row1, Range("Count_InfantAssigned").Column)
If Sheets("Reservations").Cells(row1, Range("Tables_eFloorplan").Column).value = vbNullString Then
Sheets("Reservations").Cells(row1, Range("Tables_eFloorplan").Column) = "," & t1 & "(" & gc1 & "/" & ac1 & "a0j" & cc1 & "c" & ic1 & "i)"
Else
Sheets("Reservations").Cells(row1, Range("Tables_eFloorplan").Column) = Sheets("Reservations").Cells(row1, Range("Tables_eFloorplan").Column) & "," & t1 & "(" & gc1 & "/" & ac1 & "a0j" & cc1 & "c" & ic1 & "i)"
End If
Else
MsgBox "Table already assigned"
End If
Sheets("Reservations").Cells(row1, Range("Count_AdultAssigned").Column) = Sheets("Reservations").Cells(row1, Range("Count_AdultAssigned").Column) + ac1
Sheets("Reservations").Cells(row1, Range("Count_ChildAssigned").Column) = Sheets("Reservations").Cells(row1, Range("Count_ChildAssigned").Column) + cc1
Sheets("Reservations").Cells(row1, Range("Count_InfantAssigned").Column) = Sheets("Reservations").Cells(row1, Range("Count_InfantAssigned").Column) + ic1
Sheets("Reservations").Cells(row1, Range("Status_Reservation").Column).value = "Assigned"
With Application.WorksheetFunction
Sheets("Reservations").Cells(row1, Range("Notes_History").Column).value = "Assigned to table " & t1 & "  by [" & .Index(Range("User_LAN"), .Match(Range("User_Active"), Range("User_PERNER"), 0)) & " : " & Now & "]" & Chr(10) & Chr(10) & Sheets("Reservations").Cells(row1, Range("Notes_History").Column).value
End With
Run "Selection_Clear"
Exit Sub
Error:
subname = "'Tables_Assign'"
Run "Error_Report"
Application.EnableEvents = True
End Sub

 

[bsg001]

The row1 variable is designated by a search procedure from another code. It searches for the reservation confirmation number in the database, then assigns the row1 variable to that row. I know that the code is working fine because the first half of the procedure updates correctly.

There should not be any other code running at the time (though I'm really not sure how to check that to make sure), and I do not use any sheet based event codes. All code used is in the same module at the workbook level. The On Error code does not do anything to the procedure...it was recently added but I was having the same error before.

 

[Jonmo1]

and I do not use any sheet based event codes. All code used is in the same module at the workbook level.

A workbook level event code could be an issue just as easily.

There should not be any other code running at the time (though I'm really not sure how to check that to make sure),

Step through the code using F8, this will let you observe the code as it runs 1 line at a time.

 

[bsg001]

Wow, thanks Jonmo1! Apparently I hadn't tried that when encountering this error.

I stepped through as suggested and sure enough it is running another procedure when it gets to those lines. I really don't understand why, though. Here's what is happening...maybe one of you has some insight. In order to obtain the confirmation number which in turn locates the correct record in the database, the user clicks on the last name for the reservation in a separate form. When that last name is selected, the confirmation number value is loaded into the variable I have assigned. The code then searches for that confirmation in the database list to obtain the row in which that record exists.

Fast forward to the issue in the code above and for some reason the code I just mentioned (where the user clicks on the last name in the form listbox) is running. There is nothing in the problem code that mentions or calls the other procedure, nor is any form data being used. I'm at a complete loss.

 

[bsg001]

BTW Jonmo1, it is running that procedure even with application events disabled.

 

[Jonmo1]

That's going to be hard to track down without seeing the whole thing.

What is the 'Other procedure' it runs after that line ?
Is it a Function or Sub ?

 

[bsg001]

Is there a way to determine where a form event is being triggered? The code that is being called is

Application.ScreenUpdating = False
On Error GoTo Error:
c1 = Menu_Floorplan.Database_UnassignedGuests.value
row1 = Sheets("Reservations").Cells.Find(What:=c1, After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Row
Exit Sub
Error:
subname = "'Selection_UnassignedGuests'"
Run "Error_Report"

 

[bsg001]

All code is inside public subs. I don't have any functions in my project.