VBA Macro - find next Empty Row within Dynamic Range

  • Im having a really hard time with creating a dynamic way of entering data from a userform.

    I have a column ("A") down the left which is reference to the product name (example AA123), there is 78 rows with the name AA123 and they increment by 1 every 78 rows, untill around AA400.

    When the user enters product name into a text box in a userform (for example AA123), and then i want to find the next empty row If column A is equal to the value in the text box (AA123) and add the values from with in Textbox1 offset by 2

    Ive tried a few different methods but nothing seems to be working. It either enters the data into every single cell with in that range. Or it will enter the data into the last empty cell within the whole worksheet.

    This is the code i have written but it adds data to every single row that has the Value "AA123" in Column A. I want it to only be the next empty row

    Any Help would be greatly appreciated.

  • Seems like it should be a simple fix but your description and code is a bit vague on exact where you want the text written. Could you attach a small workbook with before and after worksheets?

    If I've been helpful, let me know. If I haven't, let me know that too.

  • Like dangelor mentioned, it is hard to grasp what you want.

    Where are these empty cells?

    Which is "the other text box"?

    If the empty cells are between cell value changes:

    Change references where required.

    1. Sub Maybe()
    2. Sheets("Sheet1").Cells(Columns(1).Find("TextBox1", , , 1).End(xlDown).Row, 1).Offset(1).Value = TextBox2
    3. End Sub

    If there are no empty cells in Column A, you'll end up below all you data in that column

    The best thing to do is add an attachment with sufficient data and a before and after. No personal data in it though.

  • Loops should be avoided when possible. Find a more efficient way, in this case I would use .Find.

    This code is untested and based ontrying to interpret your code