Same VBA Code Producing Two Different Results Across Two Sheets

J

JHud2022

New Member
Joined
Dec 16, 2021
Messages
10
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
Hello,

I have two sheets within the same workbook that have essentially the same UserForm in each, but referencing two different tables. I did this by exporting the UserForm, importing it, and then renaming it, as well as changing the range. The first UserForm inserts new data into the first available row which has formulas in different cells within that row. The second UserForm, which is literally the same thing as the first UserForm besides a different range, is adding a row to the bottom of the table.

UserForm1

VBA Code: 
Private Sub OKButton_Click()

Dim rng As Range
Dim LastRow As Long

Set rng = ActiveSheet.ListObjects("SunSch").Range

LastRow = rng.Find("*", rng.Cells(1), xlValues, xlPart, xlByRows, xlPrevious, False).Row + 1
           
rng.Parent.Cells(LastRow, 1).Value = StoreTextBox.Value
rng.Parent.Cells(LastRow, 7).Value = TrailerTextBox.Value
rng.Parent.Cells(LastRow, 8).Value = TractorTextBox.Value
rng.Parent.Cells(LastRow, 9).Value = ProTextBox.Value
rng.Parent.Cells(LastRow, 10).Value = LoadTextBox.Value
rng.Parent.Cells(LastRow, 12).Value = DriverTextBox.Value
rng.Parent.Cells(LastRow, 15).Value = StopTextBox.Value
rng.Parent.Cells(LastRow, 19).Value = ConfirmedTextBox.Value
   
If DispatchCheck1.Value = True Then
    rng.Parent.Cells(LastRow, 11).Value = "Yes"
ElseIf DispatchCheck2.Value = True Then
    rng.Parent.Cells(LastRow, 11).Value = "No"
Else
    rng.Parent.Cells(LastRow, 11).Value = ""
End If

If InfoCheck1.Value = True Then
    rng.Parent.Cells(LastRow, 20).Value = "Yes"
ElseIf InfoCheck2.Value = True Then
    rng.Parent.Cells(LastRow, 20).Value = "No"
Else
    rng.Parent.Cells(LastRow, 20).Value = ""
End If

If LiveCheck1.Value = True Then
    rng.Parent.Cells(LastRow, 30).Value = "Yes"
ElseIf LiveCheck2.Value = True Then
    rng.Parent.Cells(LastRow, 30).Value = "No"
Else
    rng.Parent.Cells(LastRow, 30).Value = ""
End If

If CancelCheck1.Value = True Then
    rng.Parent.Cells(LastRow, 31).Value = "Yes"
ElseIf CancelCheck2.Value = True Then
    rng.Parent.Cells(LastRow, 31).Value = "No"
Else
    rng.Parent.Cells(LastRow, 31).Value = ""
End If

If SweepCheck1.Value = True Then
    rng.Parent.Cells(LastRow, 32).Value = "Yes"
ElseIf SweepCheck2.Value = True Then
    rng.Parent.Cells(LastRow, 32).Value = "No"
Else
    rng.Parent.Cells(LastRow, 32).Value = ""
End If

If RevCheck1.Value = True Then
    rng.Parent.Cells(LastRow, 33).Value = "Yes"
ElseIf RevCheck2.Value = True Then
    rng.Parent.Cells(LastRow, 33).Value = "No"
Else
    rng.Parent.Cells(LastRow, 33).Value = ""
End If

If BackhaulCheck1.Value = True Then
    rng.Parent.Cells(LastRow, 34).Value = "Yes"
ElseIf BackhaulCheck2.Value = True Then
    rng.Parent.Cells(LastRow, 34).Value = "No"
Else
    rng.Parent.Cells(LastRow, 34).Value = ""
End If

End Sub

UserForm2

VBA Code: 
Private Sub OKButton_Click()

Dim rng As Range
Dim LastRow As Long

Set rng = ActiveSheet.ListObjects("MonSch").Range

LastRow = rng.Find("*", rng.Cells(1), xlValues, xlPart, xlByRows, xlPrevious, False).Row + 1

rng.Parent.Cells(LastRow, 1).Value = StoreTextBox.Value
rng.Parent.Cells(LastRow, 7).Value = TrailerTextBox.Value
rng.Parent.Cells(LastRow, 8).Value = TractorTextBox.Value
rng.Parent.Cells(LastRow, 9).Value = ProTextBox.Value
rng.Parent.Cells(LastRow, 10).Value = LoadTextBox.Value
rng.Parent.Cells(LastRow, 12).Value = DriverTextBox.Value
rng.Parent.Cells(LastRow, 15).Value = StopTextBox.Value
rng.Parent.Cells(LastRow, 19).Value = ConfirmedTextBox.Value

If DispatchCheck1.Value = True Then
    rng.Parent.Cells(LastRow, 11).Value = "Yes"
ElseIf DispatchCheck2.Value = True Then
    rng.Parent.Cells(LastRow, 11).Value = "No"
Else
    rng.Parent.Cells(LastRow, 11).Value = ""
End If

If InfoCheck1.Value = True Then
    rng.Parent.Cells(LastRow, 20).Value = "Yes"
ElseIf InfoCheck2.Value = True Then
    rng.Parent.Cells(LastRow, 20).Value = "No"
Else
    rng.Parent.Cells(LastRow, 20).Value = ""
End If

If LiveCheck1.Value = True Then
    rng.Parent.Cells(LastRow, 30).Value = "Yes"
ElseIf LiveCheck2.Value = True Then
    rng.Parent.Cells(LastRow, 30).Value = "No"
Else
    rng.Parent.Cells(LastRow, 30).Value = ""
End If

If CancelCheck1.Value = True Then
    rng.Parent.Cells(LastRow, 31).Value = "Yes"
ElseIf CancelCheck2.Value = True Then
    rng.Parent.Cells(LastRow, 31).Value = "No"
Else
    rng.Parent.Cells(LastRow, 31).Value = ""
End If

If SweepCheck1.Value = True Then
    rng.Parent.Cells(LastRow, 32).Value = "Yes"
ElseIf SweepCheck2.Value = True Then
    rng.Parent.Cells(LastRow, 32).Value = "No"
Else
    rng.Parent.Cells(LastRow, 32).Value = ""
End If

If RevCheck1.Value = True Then
    rng.Parent.Cells(LastRow, 33).Value = "Yes"
ElseIf RevCheck2.Value = True Then
    rng.Parent.Cells(LastRow, 33).Value = "No"
Else
    rng.Parent.Cells(LastRow, 33).Value = ""
End If

If BackhaulCheck1.Value = True Then
    rng.Parent.Cells(LastRow, 34).Value = "Yes"
ElseIf BackhaulCheck2.Value = True Then
    rng.Parent.Cells(LastRow, 34).Value = "No"
Else
    rng.Parent.Cells(LastRow, 34).Value = ""
End If

End Sub

I am incredibly curious as to why this effect is occurring. I have deleted and re-entered the code manually to see if there was an issue and I still get the result of a new row being added to the bottom of the table. I have 5 more sheets in the same workbook that I will be doing the exact same thing for, but with different ranges, so I am really hoping that I can understand what I am doing wrong.

Thank you so much!
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Sort by date Sort by votes
The only explanation there is is that both tables are different from each other. You indicate that you're using formulas. Where a formula evaluates to "" (no value) that cell will never be returned in the result of the Find method.

FYI, you could get more benefit from using tables. After all, they are dynamic ranges, so you can simply add data at the bottom. You don't have to search for the next available row, you simply grow the table by adding data. Format and formulas will automagically copied over to the newly added row if the ListRow.Add method is used.
 
Upvote 0
Solution
Correction on my previous post: formulas will NOT be copied, data validation, on the other hand, will be copied.
 
Upvote 0
The only issue is that I have another macro that protects certain columns from users being able to alter them in any way, as I have had issues with that recently so I want to lock it down better.

Each table is the exact same format, but with different formulas as each sheet is referencing a different day. It is just weird that two tables setup the exact same way with the exact same VBA code (besides the named range) produce two different results.

Very weird.
 
Upvote 0
If your curiosity persists, you might consider uploading a sanitized mock-up of your workbook to a public facility on the net, like DropBox, Google Drive or WeTransfer.
Make sure the workbook is marked for public/shared before creating a link and post this link within this thread.
Obviously, that workbook must exhibit the same undesirable and for you as yet inexplicable behavior as described in your post #1.
 
Upvote 0
I believe I figured out what was going on. Yes, you are correct as the tables somehow are different. For a workaround, I copied the sheet, inserted a new UserForm, pasted the VBA code into the new UserForm, switched the range and table name, and then it works perfectly! I went off what you said, so thank you for helping me narrow down what was going on!
 
Upvote 0
You're welcome and thanks for the follow-up (y)
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
VBA UserForm ListObjects
Replies
5
Views
1K
JHud2022
J
E
  • Question
Help Cleaning Up / Optimizing VBA Code
Replies
6
Views
269
offthelip
O
O
  • Question
VBA Lookup from 2 different tables (in different sheets from a single workbook) to make logical conclusion and print result in cell
Replies
0
Views
334
ouvay
O
SkywardPalm
  • Solved
Finding and comparing dollar totals calculated from two different sheets to raise a warning if they do not match
Replies
5
Views
399
DanteAmor
DanteAmor
Z
  • Solved
Document number not appearing through VBA code
Replies
2
Views
320
Zubair
Z

Forum statistics

Threads
1,214,541
Messages
6,120,110
Members
448,945
Latest member
Vmanchoppy

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top