Code to place userform data into next empty row

Hi,

I have the below code which currently populates the next empty row with information from my userform. However this code only works if Column A is empty, unfortunately I will always have data in column A is there a way to change the code so it looks for the next empty row in Column B?

This is an (Extract of my code)

Private Sub BtnBookAppt_Click()
Dim erow As Long
erow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1).Row

            Sheet2.Cells(erow, 2).Value = Me.tbxName.Value
            Sheet2.Cells(erow, 3).Value = UserForm2.tbxTown.Value
            Sheet2.Cells(erow, 4).Value = UserForm2.TbxCounty.Value
            Sheet2.Cells(erow, 6).Value = UserForm2.tbxPostCode.Value
            Sheet2.Cells(erow, 7).Value = UserForm2.tbxEmail.Value
            Sheet2.Cells(erow, 8).Value = UserForm2.tbxContact1.Value
            Sheet2.Cells(erow, 9).Value = UserForm2.tbxContact2.Value
            Sheet2.Cells(erow, 11).Value = UserForm2.CboPriority.Value
            Sheet2.Cells(erow, 12).Value = UserForm2.tbxApptDate.Value
            Sheet2.Cells(erow, 14).Value = UserForm2.tbxComments.Value
Unload UserForm2
    UserForm2.Show
    
End Sub

Many thanks
Michelle

Hi Michelle,

Why don't you take a look at this - it's a very simple change...

Tony

Hi Tony

Thanks for replying, as you can tell i am not overly knowledgeable on VBA and seem to somehow muddle my way through. However I am not sure how to apply the code you gave me to my userform? This is what i have done.

Function FindEmptyCellInColumn(pColumn As Long) As Long

'   Find the first empty cell in the requested column

FindEmptyCellInColumn = Sheet2.Cells(Sheet2.Rows.Count, pColumn).End(xlUp).Row

End Function



Private Sub BtnBookAppt_Click()

Dim a As Long


a = FindEmptyCellInColumn(2)

erow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1).Row

            Sheet2.Cells(erow, 2).Value = Me.tbxName.Value
            Sheet2.Cells(erow, 2).Value = UserForm2.Tbxstreet.Value
            Sheet2.Cells(erow, 3).Value = UserForm2.tbxTown.Value
            Sheet2.Cells(erow, 4).Value = UserForm2.TbxCounty.Value
            Sheet2.Cells(erow, 6).Value = UserForm2.tbxPostCode.Value
            Sheet2.Cells(erow, 7).Value = UserForm2.tbxEmail.Value
            Sheet2.Cells(erow, 8).Value = UserForm2.tbxContact1.Value
            Sheet2.Cells(erow, 9).Value = UserForm2.tbxContact2.Value
            Sheet2.Cells(erow, 11).Value = UserForm2.CboPriority.Value
            Sheet2.Cells(erow, 12).Value = UserForm2.tbxApptDate.Value
            Sheet2.Cells(erow, 13).Value = UserForm2.cboSessions.Value
            Sheet2.Cells(erow, 14).Value = UserForm2.CboTime.Value
            Sheet2.Cells(erow, 14).Value = UserForm2.cbovenue.Value
            Sheet2.Cells(erow, 14).Value = UserForm2.tbxComments.Value
Unload UserForm2
    UserForm2.Show
    


End Sub

Can yuo help me understand how i intergrate it please, thanks for helping.

Michelle

If you want to populate column B, change this line

erow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1).Row

INTO
erow = Sheet2.Cells(Rows.Count, 2).End(xlUp).Offset(1).Row

Ah!

You'll need to add "+ 1" to the expression in FindEmptyCell.

The expression as it is now actually finds the last used cell. You need to point one past that to get an empty cell.

HTH

Tony

Thanks Tony, where abouts do i show the "+1" in my code?

Function FindEmptyCellInColumn(pColumn As Long) As Long

'   Find the first empty cell in the requested column

FindEmptyCellInColumn = Sheet2.Cells(Sheet2.Rows.Count, pColumn).End(xlUp).Row

End Function



Private Sub BtnBookAppt_Click()

Dim a As Long

a = FindEmptyCellInColumn(2)



            Sheet2.Cells(a, 2).Value = Me.tbxName.Value
            
Unload UserForm2
    UserForm2.Show
    


End Sub

Well, let's think for a minute.

Your function is going to tell you the number of a row. (FindEmptyCellInColumn gives you the row number of an empty cell)

It does this by figuring out the row number (.Row) of the range that comprises the last used cell (Sheet2.Cells(Sheet2.Rows.Count, pColumn).End(xlUp))

So the expression

FindEmptyCellInColumn = Sheet2.Cells(Sheet2.Rows.Count, pColumn).End(xlUp).Row

gives you the row number of the last used cell (ooops - that was my earlier mistake, wasn't it
So, in order to get the row past that, we simnply add 1 to that value. You could do it by having a temporary variable to get the last used row, and then adding one to that, or you could economize by simply adding 1 right onto the end of the expressions:

FindEmptyCellInColumn = Sheet2.Cells(Sheet2.Rows.Count, pColumn).End(xlUp).Row   + 1

At the risk of sounding a little bit stuffy (hey, I'm an Old Fart, I'm allowed to ), your questions indicate that you are flying a little by the seat of your pants when it comes to this coding stuff. May I recommend that you read the erly chapters of wonderful book called Algorithms + Data Structures = Programs

This is by Niklaus Wirth, one of the pioneers of structured programming, and its more important design concept, Stepwise Refinement. Te later chapters are pretty heavy going, but the first section on Fundamental Data Structures is still (after some 40 years in the field) regular bed-time reading - it's that good!

HTH

Tony

Thanks Tony, Yes i am learning as i go along with the help of VBA for dummies. Appreciate your help with this one.