Userform/VBA incorrectly adding data below the table

powellku

New Member
Joined
Aug 13, 2020
Messages
12
Office Version
  1. 2016
Platform
  1. Windows
Hi Excel Gurus!

Problem:
I believe I have a userform/ VBA coding issue. I've created a userform that has successfully worked up until now. Previously, my userform added data to the bottom of the table correctly without any issues. Now, my userform adds data many cells below my table -- see image below.

Attempted Solutions:

I've reviewed a few other threads on similair issues, but I can't seem to crack it. I've reviewed excel's auto correct settings and they are fine. I've attempted to resize my table and that doesn't seem to do it. I've attached the userform/ VBA coding below. In this instance, I am adding the same data to two different databases for two different analysis that have to take place separately. The issue is occuring in both databases, but if one of them can be resolved then I am sure that the second one can be resolved. I am at a loss as to what I can do to resolve this so I appreciate any insights.

Thank you! :)

Userform/ VBA

VBA Code:
Private Sub CommandButton1_Click()

Dim sh1 As Worksheet
Set sh1 = ThisWorkbook.Sheets("Database2")
Dim sh2 As Worksheet
Set sh2 = ThisWorkbook.Sheets("TA Raw Database")
Dim i As Long

n = sh1.Range("A" & Application.Rows.Count).End(xlUp).Row
n = sh2.Range("A" & Application.Rows.Count).End(xlUp).Row

sh1.Range("G" & n + 1).Value = Me.ComboBox1.Value
sh1.Range("I" & n + 1).Value = Me.ComboBox2.Value
sh1.Range("K" & n + 1).Value = Me.ComboBox3.Value
sh1.Range("M" & n + 1).Value = Me.ComboBox4.Value
sh1.Range("D" & n + 1).Value = Me.ComboBox5.Value
sh1.Range("T" & n + 1).Value = Me.ComboBox6.Value
sh1.Range("O" & n + 1).Value = Me.TextBox1.Value
sh1.Range("X" & n + 1).Value = Me.TextBox4.Value
sh1.Range("Y" & n + 1).Value = Me.TextBox5.Value
sh1.Range("P" & n + 1).Value = Me.TextBox7.Value
sh1.Range("Q" & n + 1).Value = Me.TextBox8.Value

sh2.Range("F" & n + 1).Value = Me.ComboBox1.Value
sh2.Range("H" & n + 1).Value = Me.ComboBox2.Value
sh2.Range("J" & n + 1).Value = Me.ComboBox3.Value
sh2.Range("L" & n + 1).Value = Me.ComboBox4.Value
sh2.Range("E" & n + 1).Value = Me.ComboBox5.Value
sh2.Range("R" & n + 1).Value = Me.ComboBox6.Value
sh2.Range("N" & n + 1).Value = Me.TextBox7.Value
sh2.Range("O" & n + 1).Value = Me.TextBox8.Value

MsgBox "New record added successfully.", vbInformation

End Sub
 

Attachments

  • image001.png
    image001.png
    16.8 KB · Views: 8

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,682
Office Version
  1. 365
Platform
  1. Windows
Check that you don't have anything in col A below the table.
 

powellku

New Member
Joined
Aug 13, 2020
Messages
12
Office Version
  1. 2016
Platform
  1. Windows
Check that you don't have anything in col A below the table.

Hi Fluff,

No, there isn't anything below the table in column A or anywhere. I've deleted all the rows below the table and used the clear all function to ensure that there isn't anything.

Sincerely,

kp
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,682
Office Version
  1. 365
Platform
  1. Windows
You are calculating the last row based on col A in the "TA Raw Database" sheet. So unless the tables are always the same size your code won't work for the "Database2" sheet.
If the code is putting the data below the last row of the table on the "TA Raw Database" sheet, then there must be something in col A of that sheet on the row above the new data.
 

powellku

New Member
Joined
Aug 13, 2020
Messages
12
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

You are calculating the last row based on col A in the "TA Raw Database" sheet. So unless the tables are always the same size your code won't work for the "Database2" sheet.
If the code is putting the data below the last row of the table on the "TA Raw Database" sheet, then there must be something in col A of that sheet on the row above the new data.
I've used the same functions: delete rows and clear all, and I am having the same issue -- there isn't anything below. I'll take out the TA Raw Database portion update and see if that changes anything.
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,962
Office Version
  1. 365
Platform
  1. Windows
Try moving this line:
n = sh2.Range("A" & Application.Rows.Count).End(xlUp).Row

to the right above this line:
sh2.Range("F" & n + 1).Value = Me.ComboBox1.Value
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,682
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

What is the name of the table on the Database2 sheet & which column does it start in?
 

powellku

New Member
Joined
Aug 13, 2020
Messages
12
Office Version
  1. 2016
Platform
  1. Windows
Try moving this line:
n = sh2.Range("A" & Application.Rows.Count).End(xlUp).Row

to the right above this line:
sh2.Range("F" & n + 1).Value = Me.ComboBox1.Value

Organizing the coding did the trick. Keep everything organized by the two sheet types worked!

Thank you all for your help!
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,962
Office Version
  1. 365
Platform
  1. Windows
You're welcome, glad to help & thanks for the feeedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,861
Messages
5,627,314
Members
416,239
Latest member
Counselor85027

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
Top