Transferring data to the first blank row in different sheet using VBA

Hi,
I am using excel to create, essentially a transaction system/ database. Every time I buy or sell using the entry page, i press the macro button which i want to send the data I have inputted on the entry page to a database. However, I will be using this allot over the space of a year, so i have attempted to send all the data in the next blank row after each press of the button. So far, it is sending all the correct data over when the correct option box is pressed (either buy or sell on the entry page) however, it is not always putting it in the correct row. It seems to overwrite the row that it previously used sometimes. My code is as follows:

Please Login or Register  to view this content.

I imagine that the problem lies within the RowNumber declaration, but I do not understand why, if someone could help clear this issue up, that'd be great. Thanks

Is there any instance where column A on the Database sheet would be empty when you submit a record?
If so this is what is causing the overwriting.

There are other ways of defining the last row but which would be best for you would very much depend on data layout, and without a sample workbook to look at it's difficult to make further suggestion.

BSB

Originally Posted by BadlySpelledBuoy

Is there any instance where column A on the Database sheet would be empty when you submit a record?
If so this is what is causing the overwriting.

There are other ways of defining the last row but which would be best for you would very much depend on data layout, and without a sample workbook to look at it's difficult to make further suggestion.

BSB

Yes, there is an instance where col A will be empty. I want it to search for the first row that has absolutely nothing in it. There are columns that should never be empty if the entry form has been used. But just in case someone inputs incorrectly i would rather it look for the first row that is completely empty, i.e. none of the columns A-Z for that row have any entrys. Is there a way to code this for a range of columns?

Thanks, I have used this and it worked. However, upon deleting the test data in my 'database' the usedrange is picking up the wrong range. I have read online that is rather unreliable and if you delete data it will not work effectively, I am guessing that is what has happened. I was just wondering if you knew any other way I could get the same result, or perhaps a way to reset the usedrange. Thanks

This used to happen to me back when I was using Excel 2007 but since using 2010 and 2016 it doesn't happen anymore. If I delete data the UsedRange corrects itself.

Without a long winded method of testing each column for a last row and then using the greatest of those for your RowNumber variable, the easiest solution would be to use a column that will ALWAYS be populated via the form. If there isn't one of those, make one. Something like the ID field in an Access table.

BSB

ok, the ID field sounds like a good idea. Thanks for your help

No problem. Happy to help

BSB