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:

    Edited once, last by royUK ().

  • Insert this line

    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?

    Edited once, last by royUK: Add Code Tags ().

  • 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


    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

    What's happening when I run the code is:

    CriteriaMin MakrsMax MarksSub-CriteriaMin MarksMax MarksSub-CriteriaMin MarksMax Marks

    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)

  • Sorry Roy, I should have read the rules first!

    Yongle thanks so much for taking the time with this - it works perfectly now :)

Participate now!

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