Excel 2010 VBA - Insert Row Not Working

[reberryjr]

I have a User Form where data entered would be entered in certain cells on either the Client Info tab and/or the Client Measurements tab. For some reason, the code isn't working. Now, I've copied it from another workbook where the code works, and changed the variables, but no success. Any suggestions?

Private Sub cmd_AddNewClient_Click()


Dim iRow As Long
Dim jRow As Long
Dim ws1 As Worksheet
Dim ws2 As Worksheet


Set ws1 = Worksheets("Client Info")
Set ws2 = Worksheets("Client Measurements")


'Finds the last occupied row in the worksheet.


If Range("A2") <> "" Then
Rows("2:2").Select
Selection.Insert shift:=x1Down
End If


'Insert an empty row (specifically row 2) in the Client Info and Client Measurements tabs.


iRow = Worksheets("Client Info").Cells(Rows.Count, 1).End(x1up).Offset(1, 0).Row
jRow = Worksheets("Client Measurements").Cells(Rows.Count, 1).End(x1up).Offset(1, 0).Row


'Copies the date entered in frm_NewClients, to the appropriate tabs.


ws1.Range("A2").Value = Me.txt_First
ws1.Range("B2").Value = Me.txt_Last
ws1.Range("C2").Value = Me.txt_Suffix
ws1.Range("D2").Value = Me.txt_Client
ws1.Range("F2").Value = Me.txt_DietStart
ws1.Range("H2").Value = Me.txt_TrainingStart
ws1.Range("J2").Value = Me.cobo_Gender
ws1.Range("K2").Value = Me.txt_DoB
ws1.Range("L2").Value = Me.txt_Age
ws2.Range("C2").Value = Me.txt_Client
ws2.Range("D2").Value = Me.cobo_EntryType
ws2.Range("E2").Value = Me.txt_Height
ws2.Range("F2").Value = Me.txt_Weight
ws2.Range("G2").Value = Me.txt_Chest
ws2.Range("H2").Value = Me.txt_Hips
ws2.Range("I2").Value = Me.txt_Waist
ws2.Range("J2").Value = Me.txt_BicepL
ws2.Range("K2").Value = Me.txt_BicepR
ws2.Range("L2").Value = Me.txt_ThighL
ws2.Range("M2").Value = Me.txt_ThighR
ws2.Range("N2").Value = Me.txt_CalfL
ws2.Range("O2").Value = Me.txt_CalfR


End Sub

 

[StephenCrump]

For some reason, the code isn't working.

Can you perhaps clarify what you mean by "isn't working". What is happening? What do you think should be happening?

The code snippet below will insert a new Row 2 in the ActiveSheet (because you haven't qualified your Range reference) if A2 is non-blank. That's not how the code is commented: Insert an empty row (specifically row 2) in the Client Info and Client Measurements tabs.

If Range("A2") <> "" Then
    Rows("2:2").Select
    Selection.Insert shift:=x1Down
End If

The code is also commented: Finds the last occupied row in the worksheet.

You determine iRow (for Worksheets("Client Info")) and jRow (for Worksheets("Client Measurements")) but the code never uses these variables?

 

[reberryjr]

Thank you for responding Stephen. To be honest, I'm new to VBA, so I'm learning as I go, piecing things together from books and the web. Test, fix, test, fix, etc. The code that I pasted above was taken from another workbook that I built, where it worked fine. Now, I did have to modify it some to include updating a 2nd worksheet, so I may have screwed it up.

The intent is, the User fills out a form, clicks a button and the data is entered in a null row, on the appropriate tab. I would be good with it entering the data in the first null row, or inserting a row and then entering the data. Seems like the former would be cleaner to code though.

Any suggestions?

 

[reberryjr]

So, I watched some more videos and came across one that I really liked. However, I can't seem to get the next row now. The code keeps failing at the line in red font. I don't understand why, as the code matches exactly what the video shows, except for the ws1. The video shows ssheets, where I show ws1, but I declared it the same.

Private Sub cmd_Submit_Click()


Dim ws1 As Worksheet


Set ws1 = ThisWorkbook.Sheets("Client Info")


nr = ws1.Cells(Rows.Count, 1).End(x1up).Row + 1


ws1.Cells(nr, 2) = Me.txt_First
ws1.Cells(nr, 3) = Me.txt_Last
ws1.Cells(nr, 4) = Me.txt_Suffix
ws1.Cells(nr, 6) = CDate(Me.txt_DietStart)
ws1.Cells(nr, 8) = CDate(Me.txt_TrainingStart)
ws1.Cells(nr, 10) = Me.cobo_Gender
ws1.Cells(nr, 11) = CDate(Me.txt_DoB)
ws1.Cells(nr, 12) = Me.txt_StartingAge




End Sub

 

[StephenCrump]

It's xlUp, not x1Up, i.e. letter l rather than number 1.

 

[reberryjr]

I'm looking for a dark corner to hide in now. Thank you!

 

[StephenCrump]

I'm looking for a dark corner to hide in now.

Not a problem!

Have a look at Option Explicit and consider using it in all your code - highly recommended. It would have more helpfully identified x1Up as a typo.

Simple typos can cause big problems with code, and can be hard to debug if you don't use Option Explicit, e.g. look at Post #4 here: https://www.mrexcel.com/forum/excel-questions/1026630-assigning-function-values-loop.html