loop not working properly for adding textbox entries to last columns

  • Hi,


    Hope you can help me!


    I'm trying to find a value in a range that matches combobox selection and then adds the value from a row of 3 textboxes to the last empty columns on that row. When I populate the textboxes (lets say I have two rows of dynamically created textboxes), it only takes the data from the last row I entered


    Here's the code:


  • Insert this line

    Code
    1. Debug.Print p, c.Address(0,0)

    above line beginning lastcol = ...


    And look in the immediate window in VBA editor

    (Display immediate window with {CTRL} g


    Is VBA finding the same cell repeatedly ?


    .Find returns the first value only

    .FindNext is required to return all the other values


    See this link which shows how to use .Find and .FindNext together https://docs.microsoft.com/en-…/api/excel.range.findnext


    Happy to provide further help if you cannot resolve it.

  • Thanks so much for replying Yongle :-)


    I did as you suggested (I hope) but it is still just pulling in the data from the last row of textboxes?


  • Code is easier to read if you click on </> and post your code inside the code window


    My understanding

    strFind is the same value for every required match


    when p = 1

    and strFind is found

    ... the values in TB11, TB21 and TB31 are written to 3 new columns in the found cell's row


    p is now increased to 2

    Am I correct in thinking that you want the code to find the 2nd occurrence of strFind in column A

    and when strFind is found

    ... the values in TB12, TB22 and TB32 are written to 3 new columns in that row


    etc

    If not correct - please clarify what should happen when p = 2


    If my understanding is correct, test this amended code on a copy of your workbook


  • Thanks Yongle


    So to further explain - the strFind is a combobox list of Criteria populated by the user on another userform. It then asks the user to select how may sub-criteria they would like to enter and depending on the number, a row of x number of 3 textboxes are created (sub-criteria title, min marks, max marks) which are to be written to the worksheet adjacent to the chosen criteria. For example:


    CriteriaMin MarksMax MarksSub-CriteriaMin MarksMax MarksSub-CriteriaMin MarksMax Marks
    C1100200SBC150100SBC250100


    What's happening when I run the code is:


    CriteriaMin MakrsMax MarksSub-CriteriaMin MarksMax MarksSub-CriteriaMin MarksMax Marks
    C1100200SBC250100


    I did edited the code to reflect what you had written above but it's the same thing that's happening

  • So - only ONE match , values updated in the SAME row, AND 3 (new) column values added when p increments by one

    (Error handling added to prevent code failing if match for strFind is not found)