loop not working properly for adding textbox entries to last columns

Laney · Jun 11th 2020

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:

Code

Dim c As Range


Dim strFind As String
Dim lastcol As Long
strFind = ComboBox1.Value


Dim p, j As Long


For p = 1 To number
With ThisWorkbook.Worksheets("xx").Range("A1:A100")
Set c = .Find(what:=strFind, After:=.Cells(.Rows.Count, 1), LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)


lastcol = c.Cells(p, Columns.Count).End(xlToLeft).Column


If Not c Is Nothing Then


c.Offset(0, lastcol).Value = Controls("TB1" & p).Text
c.Offset(0, lastcol + 1).Value = Controls("TB2" & p).Text
c.Offset(0, lastcol + 2).Value = Controls("TB3" & p).Text


End If
End With


Next p

Display More

yongle · Jun 11th 2020

Insert this line

Code

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.

Laney · Jun 11th 2020

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

Private Sub CommandButton1_Click()


'Finds criteria selected from Combobox in range(column 1) and populates last 3 empty columns in that row with data from textboxes created
Dim c As Range


Dim strFind As String
Dim lastcol As Long
strFind = ComboBox1.Value
Dim firstAddress As String
Dim p, j As Long


For p = 1 To number
With ThisWorkbook.Worksheets("Award_Criteria_Value").Range("A1:A100")
Set c = .Find(what:=strFind, After:=.Cells(.Rows.Count, 1), LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
Debug.Print p, c.Address(0, 0)
lastcol = c.Cells(p, Columns.Count).End(xlToLeft).Column


If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Offset(0, lastcol).Value = Controls("TB1" & p).Text
c.Offset(0, lastcol + 1).Value = Controls("TB2" & p).Text
c.Offset(0, lastcol + 2).Value = Controls("TB3" & p).Text
Set c = .FindNext(c)
Loop While c.Address <> firstAddress




End If


End With


Next p




'Calls Clear_Form() sub
Clear_Form
End Sub

Display More

yongle · Jun 11th 2020

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

Code

Private Sub CommandButton1_Click()

'Finds criteria selected from Combobox in range(column 1) and populates last 3 empty columns in that row with data from textboxes created
    Dim c As Range, strFind As String, firstAddress As String
    Dim p, j As Long, lastcol As Long
    strFind = ComboBox1.Value
        With ThisWorkbook.Worksheets("Award_Criteria_Value").Range("A1:A100")
            Set c = .Find(what:=strFind, After:=.Cells(.Rows.Count, 1), LookIn:=xlValues, _
                LookAt:=xlPart, SearchOrder:=xlByRows, searchDirection:=xlNext, MatchCase:=False)

            lastcol = c.Cells(p, Columns.Count).End(xlToLeft).Column
            If Not c Is Nothing Then
                firstAddress = c.Address
                Do
                    p = p + 1
                    c.Offset(0, lastcol).Value = Controls("TB1" & p).Text
                    c.Offset(0, lastcol + 1).Value = Controls("TB2" & p).Text
                    c.Offset(0, lastcol + 2).Value = Controls("TB3" & p).Text
                    Set c = .FindNext(c)
                    If p = number Then Exit Do
                Loop While c.Address <> firstAddress
            End If
        End With
'Calls Clear_Form() sub
    Clear_Form
End Sub

Display More

Laney · Jun 12th 2020

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:

Criteria	Min Marks	Max Marks	Sub-Criteria	Min Marks	Max Marks	Sub-Criteria	Min Marks	Max Marks
C1	100	200	SBC1	50	100	SBC2	50	100

What's happening when I run the code is:

Criteria	Min Makrs	Max Marks	Sub-Criteria	Min Marks	Max Marks	Sub-Criteria	Min Marks	Max Marks
C1	100	200	SBC2	50	100

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

yongle · Jun 12th 2020

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)

Code

Private Sub CommandButton1_Click()

'Finds criteria selected from Combobox in range(column 1) and populates last 3 empty columns in that row with data from textboxes created
    Dim c As Range, strFind As String, firstAddress As String
    Dim p, j As Long, lastcol As Long
    strFind = ComboBox1.Value
        With ThisWorkbook.Worksheets("Award_Criteria_Value").Range("A1:A100")
            On Error Resume Next
            Set c = .Find(what:=strFind, After:=.Cells(.Rows.Count, 1), LookIn:=xlValues, _
                LookAt:=xlPart, SearchOrder:=xlByRows, searchDirection:=xlNext, MatchCase:=False)
            On Error GoTo 0
            If Not c Is Nothing Then
                For p = 1 To number
                    lastcol = c.Parent.Cells(c.Row, Columns.Count).End(xlToLeft).Column
                    c.Offset(, lastcol).Value = Controls("TB1" & p).Text
                    c.Offset(, lastcol + 1).Value = Controls("TB2" & p).Text
                    c.Offset(, lastcol + 2).Value = Controls("TB3" & p).Text
                Next p
            End If
        End With
'Calls Clear_Form() sub
    Clear_Form
End Sub

Display More

**royUK** · Jun 12th 2020

Laney, please take the time to read our Forum Rules then you will understand how to use the Forum. I have added Code Tags this time.

Laney · Jun 12th 2020

Sorry Roy, I should have read the rules first!

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

loop not working properly for adding textbox entries to last columns

Participate now!

Share

Tags