CODEWITHSUNDEEP
excelvba
temesgen-g
temesgen-g

Reputation: 31

While loop to input from multiple texbox

I have 20 textBoxes on a vba userform these textBoxes are supposed to take their values from a barcode reader and i created a while loop to take the values from those textboxes and input them on the next empty row , but when i check the results i get 2 problems 

J = 0
While J < 20
LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row + 1 
ws.Range("A" & LastRow).Value = Now()
ws.Range("B" & LastRow).Value = Me.Controls("TextBox" & J + 1).Value 
ws.Range("D" & LastRow).Value = Me.Controls("TextBox" & J + 2).Value 
ws.Range("I" & LastRow).Value = TextBox21.Value
J = J + 1
Wend
  1. The Quantity inserted on column D is Repeated on the Next Row Column B
  2. Even If the TextBoxes are Empty It is still placing data As you can see on the highlighted in Red

Upvotes: 0

Views: 97

Answers (1)

Cyril
Cyril

Reputation: 6829

Will post as an answer so it can be marked as such, though I listed this in a comment:

J = 0
While J < 20
    LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row + 1 
    ws.Range("A" & LastRow).Value = Now()
    ws.Range("B" & LastRow).Value = Me.Controls("TextBox" & J*2 + 1).Value 
    ws.Range("D" & LastRow).Value = Me.Controls("TextBox" & J*2 + 2).Value 
    ws.Range("I" & LastRow).Value = TextBox21.Value
    J = J + 1
Wend

May want to check your J max after this...

row 1 uses J = 0, so textbox 1, textbox 2

row 2 uses j = 1, so textbox 1*2+1 (3) and textbox 1*2+2 (4)

row 3 uses j =2, so textbox 2*2+1 (5) and textbox 2*2+2 (6)

etc.

Upvotes: 1

Related Questions