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)

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

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

Michelle

If you want to populate column B, change this line

Please Login or Register  to view this content.

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?

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

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.