TextBox looping help required in VBA

[dlee83]

Good evening,

Thank you for any help in advance

I have a userforn with 17 textboxes, (the 17th is the date, the other 16 are currency values) currently with a loop coded to run through all 16 to assign data to offset cells as you will see, which works.
Although I am able to assign a for loop to declare the offset cells - (For x = 1 to 16 for example, then cell.offset(x+1, 0)) , how can I do this for the 16 textboxes so I don't need to repeat the loop in the loop? Offset cell 2, 0 corresponds to textbox1, offset cell 3, 0 corresponds to textbox2, offset cell 4, 0 corresponds to textbox3 etc...



Private Sub CommandButton1_Click()

Unload Me

Dim dat As String
Dim r As Range
Dim rval As String
Dim cell As Range

Set r = Range("g3:ec3")
dat = TextBox17.Value

For Each cell In r
rval = cell.Value
If rval = dat Then
cell.Select
cell.Offset(2, 0).Select
If TextBox1.Value <> "" Then
ActiveCell.Value = CDec(TextBox1.Value)
ElseIf TextBox1.Value = "" And ActiveCell.Value <> "" Then
ActiveCell.Value = ""
End If

cell.Offset(3, 0).Select
If TextBox2.Value <> "" Then
ActiveCell.Value = CDec(TextBox2.Value)
ElseIf TextBox2.Value = "" And ActiveCell.Value <> "" Then
ActiveCell.Value = ""
End If

cell.Offset(4, 0).Select
If TextBox3.Value <> "" Then
ActiveCell.Value = CDec(TextBox3.Value)
ElseIf TextBox3.Value = "" And ActiveCell.Value <> "" Then
ActiveCell.Value = ""
End If

cell.Offset(5, 0).Select
If TextBox4.Value <> "" Then
ActiveCell.Value = CDec(TextBox4.Value)
ElseIf TextBox4.Value = "" And ActiveCell.Value <> "" Then
ActiveCell.Value = ""
End If

cell.Offset(6, 0).Select
If TextBox5.Value <> "" Then
ActiveCell.Value = CDec(TextBox5.Value)
ElseIf TextBox5.Value = "" And ActiveCell.Value <> "" Then
ActiveCell.Value = ""
End If

cell.Offset(7, 0).Select
If TextBox6.Value <> "" Then
ActiveCell.Value = CDec(TextBox6.Value)
ElseIf TextBox6.Value = "" And ActiveCell.Value <> "" Then
ActiveCell.Value = ""
End If

cell.Offset(8, 0).Select
If TextBox7.Value <> "" Then
ActiveCell.Value = CDec(TextBox7.Value)
ElseIf TextBox7.Value = "" And ActiveCell.Value <> "" Then
ActiveCell.Value = ""
End If

cell.Offset(9, 0).Select
If TextBox8.Value <> "" Then
ActiveCell.Value = CDec(TextBox8.Value)
ElseIf TextBox8.Value = "" And ActiveCell.Value <> "" Then
ActiveCell.Value = ""
End If

cell.Offset(10, 0).Select
If TextBox9.Value <> "" Then
ActiveCell.Value = CDec(TextBox9.Value)
ElseIf TextBox9.Value = "" And ActiveCell.Value <> "" Then
ActiveCell.Value = ""
End If

cell.Offset(11, 0).Select
If TextBox10.Value <> "" Then
ActiveCell.Value = CDec(TextBox10.Value)
ElseIf TextBox10.Value = "" And ActiveCell.Value <> "" Then
ActiveCell.Value = ""
End If

cell.Offset(12, 0).Select
If TextBox11.Value <> "" Then
ActiveCell.Value = CDec(TextBox11.Value)
ElseIf TextBox11.Value = "" And ActiveCell.Value <> "" Then
ActiveCell.Value = ""
End If

cell.Offset(13, 0).Select
If TextBox12.Value <> "" Then
ActiveCell.Value = CDec(TextBox12.Value)
ElseIf TextBox12.Value = "" And ActiveCell.Value <> "" Then
ActiveCell.Value = ""
End If

cell.Offset(14, 0).Select
If TextBox13.Value <> "" Then
ActiveCell.Value = CDec(TextBox13.Value)
ElseIf TextBox13.Value = "" And ActiveCell.Value <> "" Then
ActiveCell.Value = ""
End If

cell.Offset(15, 0).Select
If TextBox14.Value <> "" Then
ActiveCell.Value = CDec(TextBox14.Value)
ElseIf TextBox14.Value = "" And ActiveCell.Value <> "" Then
ActiveCell.Value = ""
End If

cell.Offset(16, 0).Select
If TextBox15.Value <> "" Then
ActiveCell.Value = CDec(TextBox15.Value)
ElseIf TextBox15.Value = "" And ActiveCell.Value <> "" Then
ActiveCell.Value = ""
End If

cell.Offset(17, 0).Select
If TextBox16.Value <> "" Then
ActiveCell.Value = CDec(TextBox16.Value)
ElseIf TextBox16.Value = "" And ActiveCell.Value <> "" Then
ActiveCell.Value = ""
End If
Exit For
End If
Next

 

[DanteAmor]

I show you 2 options.

The first is to search for the date cell by cell.

Private Sub CommandButton1_Click()
  Unload Me
  Dim cell As Range
  Dim i As Long
  
  For Each cell In Range("G3:EC3")
    If cell.Value = TextBox17.Value Then
      For i = 1 To 16
        If Controls("TextBox" & i).Value <> "" Then
          cell.Offset(i + 1).Value = CDec(Controls("TextBox" & i).Value)
        ElseIf Controls("TextBox" & i).Value = "" And cell.Offset(i + 1).Value <> "" Then
          cell.Offset(i + 1).Value = ""
        End If
      Next
      Exit For
    End If
  Next
End Sub

---------------------
The second is to find the date with the Find method. And reviewing the logic to update the cell, it can be simplified like this:

Private Sub CommandButton1_Click()
  Unload Me
  Dim f As Range
  Dim i As Long
  Set f = Range("G3:EC3").Find(TextBox17.Value, , xlValues, xlWhole)
  If Not f Is Nothing Then
    For i = 1 To 16
      f.Offset(i + 1).Value = Controls("TextBox" & i)
    Next
  End If
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​

 

[dlee83]

Thank you Dante, very helpful,

I tried the first option, but I found i needed to adjust it slightly, declaring the date as a variable first and activating the offset cells, see below. This now works great.
Thanks again

Private Sub CommandButton1_Click()

Unload Me

Dim cell As Range
Dim i As Long
Dim dat As String

dat = TextBox17.Value

For Each cell In Range("G3:EC3")
If cell.Value = dat Then
For i = 1 To 16
cell.Offset(i + 1, 0).Select
If Controls("TextBox" & i).Value <> "" Then
ActiveCell.Value = CDec(Controls("TextBox" & i).Value)
ElseIf Controls("TextBox" & i).Value = "" And ActiveCell.Value <> "" Then
ActiveCell.Value = ""
End If
Next
Exit For
End If
Next

End Sub

 

[DanteAmor]

declaring the date as a variable first and activating the offset cells

It is not necessary to declare the variable, since the data in the textbox is already a string.
It is also not necessary to select the cell, it is not a good practice.
Ideally, reference the cell and update it.
You can even be on another sheet and you can update the cell of any other sheet.

The If Elseif can be simplified as follows:

Private Sub CommandButton1_Click()
  Unload Me
  Dim cell As Range
  Dim i As Long
  Dim dat As String
  
  dat = TextBox17.Value
  For Each cell In Range("G3:EC3")
    If cell.Value = dat Then
      For i = 1 To 16
        If Controls("TextBox" & i).Value <> "" Then
          cell.Offset(i + 1).Value = CDec(Controls("TextBox" & i).Value)
        Else
          cell.Offset(i + 1).Value = ""
        End If
      Next
      Exit For
    End If
  Next
End Sub