1

Disclaimer: I'm still new to Excel VBA.

I've searched around a lot but can't seem to find a solution to my problem.

I'm attempting to streamline a data entry process with the use of a couple userforms and macros that will automate a majority of the process. I've gotten very far with a lot of help from just searching this site but I think it's come time to submit a question.

Essentially what I want this piece of code to do is loop through textboxes and check their values in one userform(QAResults) against certain criteria, when the criteria is satisfied I want it to compare that textbox with another textbox in the second userform(NewEntryUserForm) and copy some textbox values from QAResults to NewEntryUserForm. I think the problem lies in how I'm trying to use FOR and NEXT to cycle through the userform textboxes.

I'm not sure if my explanation makes sense but maybe the code will help show what I'm trying to do. I don't get any errors but it's not doing what I think it should be doing. Any suggestions would be appreciated.

Private Sub RetrieveLabComments_Click()

Dim i As Integer
Dim y As Integer
Dim ufm As UserForm
Dim qa As UserForm

For i = 1 To 3

Set qa = QAResults
    If qa.Controls("TC" & i & "_DN_FQA").Value <> "" And _
        qa.Controls("TC" & i & "_RLOG_FQA").Value <> "NA" And _
        qa.Controls("TC" & i & "_RLOG_FQA").Value > qa.Controls("TC" & i & _
              "_327_FQA").Value Then

        For y = 1 To 14
        Set ufm = NewEntryUserForm
        Set qa = QAResults
            If qa.Controls("TC" & i & "_DN_FQA").Value = ufm.Controls("CN" _ 
                & y).Value Then
                    If ufm.Controls("DT" & y).Value = "LD" Then
                        ufm.Controls("QAY" & y & "A").Value = "ILN"
                            If ufm.Controls("DT " & y).Value = "FD" Then
                                ufm.Controls("QAYF" & y & "B").Value = 
                                qa.Controls("TC" & i & "_RLOG_FQA").Value _
                                & ">" & qa.Controls("TC" & i & _
                                "_327_FQA").Value
                            End If
                    End If
           End If
        Next y
    End If


Next i

If MsgBox("Data retrieved!", vbOKOnly, "Incoming Message...") Then
End If

End Sub
finnjitsu
  • 11
  • 3
  • You can `for each` through all the controls instead. https://stackoverflow.com/questions/3344649/how-to-loop-through-all-controls-in-a-form-including-controls-in-a-subform-ac should give you an idea how to do it. – Trevor Jul 05 '17 at 17:35
  • Thanks @TrevorD, I'll check it out. – finnjitsu Jul 05 '17 at 19:12

1 Answers1

0

I looked at using the For Each for this problem but I couldn't figure out how to make it work with the number of textboxes I have in my userform.

After messing more with my code I realized that the error was not in my code but in the contents of the textboxes. In other words, it was a dumb mistake on my part and the code was working just fine. So if someone comes across this in the future, this code is a way to loop through specific textboxes on multiple userforms with nested If Then statements. I'm sure there are better ways to write this code but in the end it worked for me.

finnjitsu
  • 11
  • 3