userform to cycle through data

[neilp]

I posted this a few days ago, but I am terrible at explaining myself, so no-one was able to help

I thought i would give it another attempt, simplified and with examples, so sorry for the length of this.

I have a spreadsheet and a User Form (attached)

Copy of shelf work.xls
	A	B	C	D	E
1	Collection Date	Consignment Number	Barcode Number	Code	Count
2	2020-11-02 17:00	ZZ3020010610021313613	1780565246	1243
3	2020-11-02 17:00	ZZ3020010610021313613	1780565247	1243
4	2020-11-02 17:00	ZZ3020010610021313613	1780565248	1243
5	2020-11-02 17:00	ZZ3020010610021313614	1780565256	1358
6	2020-11-02 17:00	ZZ3020010610021313614	1780565257	1358
7	2020-11-02 17:00	ZZ3020010610021313615	1780565267	1232
8	2020-11-02 17:00	ZZ3020010610021313615	1780565268	1232
9	2020-11-02 17:00	ZZ3020010610021313615	1780565269	1232
10	2020-11-02 17:00	ZZ3020010610021313615	1780565270	1232
11	2020-11-02 17:00	ZZ3020010610021313616	1780565275	1321
12	2020-11-02 17:00	ZZ3020010610021313616	1780565276	1321
13	2020-11-02 17:00	ZZ3020010610021313617	1780565286	1203
14	2020-11-02 17:00	ZZ3020010610021313617	1780565287	1203
15	2020-11-02 17:00	ZZ3020010610021313617	1780565288	1203
data

the userform is fairly simple - the textbox in line1 is textbox1, the textboxes in line2 are textbox2 and textbox3, etc, etc.

I need a macro that, when the process button it pressed, excel will search through the data in the spreadsheet looking at the "code" column.

If, as in this example, it find 3 lines that all have the same code (1243), then i would like it to populate column "count" with whatever is in the textboxes next to line 3 on the userform (the first textbox would go in the first in the first row that code appears, the second in the second row and the third textbox in the third row.
it would then do a similar thing if it found 2 lines with the same code (1358), but would use the textboxes from "line2" on the userform etc

so the following would be the output from the "Userform example"

Copy of shelf work.xls
	A	B	C	D	E
1	Collection Date	Consignment Number	Barcode Number	Code	Count
2	2020-11-02 17:00	ZZ3020010610021313613	1780565246	1243	3
3	2020-11-02 17:00	ZZ3020010610021313613	1780565247	1243	1
4	2020-11-02 17:00	ZZ3020010610021313613	1780565248	1243	2
5	2020-11-02 17:00	ZZ3020010610021313614	1780565256	1358	7
6	2020-11-02 17:00	ZZ3020010610021313614	1780565257	1358	5
7	2020-11-02 17:00	ZZ3020010610021313615	1780565267	1232	3
8	2020-11-02 17:00	ZZ3020010610021313615	1780565268	1232	3
9	2020-11-02 17:00	ZZ3020010610021313615	1780565269	1232	1
10	2020-11-02 17:00	ZZ3020010610021313615	1780565270	1232	6
11	2020-11-02 17:00	ZZ3020010610021313616	1780565275	1321	7
12	2020-11-02 17:00	ZZ3020010610021313616	1780565276	1321	5
13	2020-11-02 17:00	ZZ3020010610021313617	1780565286	1203	3
14	2020-11-02 17:00	ZZ3020010610021313617	1780565287	1203	1
15	2020-11-02 17:00	ZZ3020010610021313617	1780565288	1203	2
data

has this helped anyone decifer my brain?

thanks for sticking with this to the bottom

Neil

 

[smozgur]

@neilp - Please do not post the same question as a new thread. If you will provide more details, then please do that in the same thread instead creating a new one. Otherwise the helpers who are already trying to understand your question will never know that you posted a duplicate thread.
Please read the Message Board Rules carefully - Rule #12.

Now, about your question.

Create the text boxes by using a matrix naming pattern.

• Line 1: TextBox11
• Line 2: TextBox21 - TextBox22
• Line 3: TextBox31 - TextBox32 - TextBox33
• ....
• Line X: TextBoxX1 - ... - TextBoxXX

Create a command button named as btnProcess, and copy and paste the following event procedure for this command button in the Userform module.

Private Sub btnProcess_Click()
Dim sht As Worksheet
Dim rng As Range
Dim firstMatch As Range
Dim lastMatch As Range
Dim matchRange As Range

    ' Define the worksheet
    Set sht = ActiveWorkbook.Worksheets("data")

    ' Set the data range
    ' Using the existing range for this sample code (skipping the header row)
    ' Adjust it for your real data range
    Set rng = sht.Range("A2:E15")
    
    Set firstMatch = rng.Rows(1).Cells(4)
    Set lastMatch = firstMatch
    
    ' Loop through the rows in the range until the current code cell value is empty
    Do Until IsEmpty(firstMatch.Value)
        DoEvents
        ' Loop for each matching value in the Code column cells
        Do
            Set lastMatch = lastMatch.Offset(1)
        Loop Until firstMatch.Value <> lastMatch.Offset(1).Value
        Set matchRange = sht.Range(firstMatch, lastMatch)
        
        ' This is where the Count column cells are filled with the
        ' corresponding text box values.
        ' That's why we used the matrix pattern to name the text boxes
        For i = 1 To matchRange.Rows.Count
            matchRange.Rows(i).Offset(, 1).Value = Me.Controls("TextBox" & matchRange.Rows.Count & i).Value
        Next i
        
        Set firstMatch = lastMatch.Offset(1)
    Loop
End Sub

There might be alternative solutions. This one I wrote is looping through the rows, detect the same codes, define a range contains the same code values, and pick the corresponding text box values to fill in the Count cells.

I hope this gives the idea.

 

[neilp]

@neilp - Please do not post the same question as a new thread. If you will provide more details, then please do that in the same thread instead creating a new one. Otherwise the helpers who are already trying to understand your question will never know that you posted a duplicate thread.
Please read the Message Board Rules carefully - Rule #12.

Now, about your question.

Create the text boxes by using a matrix naming pattern.

• Line 1: TextBox11
• Line 2: TextBox21 - TextBox22
• Line 3: TextBox31 - TextBox32 - TextBox33
• ....
• Line X: TextBoxX1 - ... - TextBoxXX

Create a command button named as btnProcess, and copy and paste the following event procedure for this command button in the Userform module.

Private Sub btnProcess_Click()
Dim sht As Worksheet
Dim rng As Range
Dim firstMatch As Range
Dim lastMatch As Range
Dim matchRange As Range

    ' Define the worksheet
    Set sht = ActiveWorkbook.Worksheets("data")

    ' Set the data range
    ' Using the existing range for this sample code (skipping the header row)
    ' Adjust it for your real data range
    Set rng = sht.Range("A2:E15")
   
    Set firstMatch = rng.Rows(1).Cells(4)
    Set lastMatch = firstMatch
   
    ' Loop through the rows in the range until the current code cell value is empty
    Do Until IsEmpty(firstMatch.Value)
        DoEvents
        ' Loop for each matching value in the Code column cells
        Do
            Set lastMatch = lastMatch.Offset(1)
        Loop Until firstMatch.Value <> lastMatch.Offset(1).Value
        Set matchRange = sht.Range(firstMatch, lastMatch)
       
        ' This is where the Count column cells are filled with the
        ' corresponding text box values.
        ' That's why we used the matrix pattern to name the text boxes
        For i = 1 To matchRange.Rows.Count
            matchRange.Rows(i).Offset(, 1).Value = Me.Controls("TextBox" & matchRange.Rows.Count & i).Value
        Next i
       
        Set firstMatch = lastMatch.Offset(1)
    Loop
End Sub

There might be alternative solutions. This one I wrote is looping through the rows, detect the same codes, define a range contains the same code values, and pick the corresponding text box values to fill in the Count cells.

I hope this gives the idea.

Hi @smozgur.

Apologies for contravening the forum rules, and thank you for not only still allowing the post, but also answering it with what I can only describe as a piece of genius.

Not only have you managed to understand my mind, but you have solved the problem.

Brilliant.

Thanks so Much

Neil

 

[smozgur]

No need to apologize. It is just the best way to keep posting in the same question to receive an answer.

You're welcome. Glad to hear it helps!