Userform/VBA incorrectly adding data below the table

[powellku]

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

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

 

[Fluff]

Check that you don't have anything in col A below the table.

 

[powellku]

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]

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]

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]

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]

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

 

[powellku]

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]

You're welcome, glad to help & thanks for the feeedback.