search a number and insert row

  • I have a table with three columns. In the first column (A), there is always a number for each row (+300).

    The number go from 1001 to 5999.

    The numbers represent categories:

    1000 = Cat A

    2000 = Cat B

    and so on.

    I want to search in column A where the last number, starting with "1", is situated (rownumber). Then I want to insert a row with a new number starting with "1"

    So, in my column the numbers go like:





    So I want to search for the position between 1079 and 2000 to insert a new row with number "1080" in column "A"

    How can I achieve that with VBA by clicking on a commandbutton on a form?

    Thx in advance

    • Best Answer

    Try this

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Works just fine! Thanks a lot!

    Next to column "A", I have column "B" and "C"

    When a Row is inserted, the number comes automaticly (last number starting with digit "1" +1) but data is also going to column "B" and "C".

    So, I adapted your code where "testdepartement" and "testarticle" will be changed to values from textboxes.

    It works fantastic.

    Thanks again!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!