Do..Loop help

[BexleyManor]

Yikes, the following code was all going swimmingly until I added the iii variable then I got some odd reactions!!

Any suggestions kind folks??

Sub Iput2()
Dim Prompt As Variant, Ipt(8) As Variant, i As Integer, ii As Integer, iii As Integer
i = 0
ii = 0
iii = 16
Prompt = Array("Line", "Supplier Code", "Full Description", "Acct Code", "Quantity", "UOM", "Price per UOM", "Delivery Date")
1:
Do Until i = 8
    Ipt(ii) = InputBox("Please enter " & Prompt(i) & Chr(10) & Chr(10) & "Press Spacebar & Ok to leave box blank.")
    If Ipt(ii) = "" Then GoTo 1
        i = i + 1
        ii = ii + 1
Loop
Range("A" & iii).Value = Ipt(0)
Range("B" & iii).Value = Ipt(1)
Range("C" & iii).Value = Ipt(2)
Range("E" & iii).Value = Ipt(3)
Range("F" & iii).Value = Ipt(4)
Range("G" & iii).Value = Ipt(5)
Range("H" & iii).Value = Ipt(6)
Range("I" & iii).Value = Ipt(7)
iii = iii + 1
goto 1
End Sub

[Steiner]

What's the purpose of that code?? To me it looks like you have an infinite loop now (the goto 1-line seem to be executed no matter what, preventing your code from ever finishing).

I'd suggest to avoid goto at all (except for errorhandling, there you don't have another option). Maybe you can replace that part with a For-next loop basing on iii??

[BexleyManor]

Hi Steiner, The code is the data input part of a purchase req spreadsheet.
I have included a copy of the actual spreadsheet for you to view

[Steiner]

Ok, try this one, whether it does what you want, I tried to replace the goto-stuff. But I don't know why you differ i and ii, so I just replaced ii with i. Just try it out whether it goes into the right direction:

Sub Iput2()
Dim Prompt As Variant, Ipt(8) As Variant, i As Integer, ii As Integer, iii As Integer
i = 0
ii = 0
iii = 16
Prompt = Array("Line", "Supplier Code", "Full Description", "Acct Code", "Quantity", "UOM", "Price per UOM", "Delivery Date")
For iii = 16 To 26
    For i = 0 To 7
        Do
            Ipt(i) = InputBox("Please enter " & Prompt(i) & Chr(10) & Chr(10) & "Press Spacebar & Ok to leave box blank.")
        Loop While Ipt(i) = ""
    Next I
    Range("A" & iii).Value = Ipt(0)
    Range("B" & iii).Value = Ipt(1)
    Range("C" & iii).Value = Ipt(2)
    Range("E" & iii).Value = Ipt(3)
    Range("F" & iii).Value = Ipt(4)
    Range("G" & iii).Value = Ipt(5)
    Range("H" & iii).Value = Ipt(6)
    Range("I" & iii).Value = Ipt(7)
    Erase Ipt()
Next iii
End Sub

Greets
Daniel

PS: Have you thought about giving the user the possibility to stop the outer loop without having to fill out all lines? Or must the user fill out all of them?

[johnske]

What's the purpose of that code?? To me it looks like you have an infinite loop now (the goto 1-line seem to be executed no matter what, preventing your code from ever finishing)....

That's exactly right. In fact you have no way of terminating the code if there are no further entries to make. There are many ways to resolve this, such as using a prompt for another entry, but the simplest would probably be just to eliminate the goto, the sub will simply end and you then click the button (or whatever you're using execute the sub in the 1st place) to make another entry.

Alternatively, use the example that steiner gave "For iii = 16 To 26........Next iii" (or 27, 28, or however many consecutive entries are normally made - in fact you could use a prompt to ask how many (N) entries you need to make and end up with something of the form For iii = 16 To N)

N.B. I don't know if it's your intent or not, but this will start from iii = 16 every time the sub is activated and write over whatever was there from any previous entries.

[BexleyManor]

Blimey, lots to take in there but thanks guys. I will have a bash with the suggestions and get back to you all.

UIntil then, many thanks guys!!

[BexleyManor]

Daniel,

Quote " PS: Have you thought about giving the user the possibility to stop the outer loop without having to fill out all lines? Or must the user fill out all of them?"

I hadn't actually thought of this (doh!!) but yes, that would be a great feature.
How would I got about this??

[BexleyManor]

Johnske, Thanks for your input.

I noted You're comments "N.B. I don't know if it's your intent or not, but this will start from iii = 16 every time the sub is activated and write over whatever was there from any previous entries."

This is fine, the worksheet is a purchase requsition form so a new copy will be printed each time.

[johnske]

Daniel,

Quote " PS: Have you thought about giving the user the possibility to stop the outer loop without having to fill out all lines? Or must the user fill out all of them?"

I hadn't actually thought of this (doh!!) but yes, that would be a great feature.
How would I got about this??

Try this for starters...you can refine and change it to whatever you want afterwards

(Have just changed steiners' code slightly to read "For iii = 16 To 100" and added a message box)

Sub Iput2()
Dim Prompt As Variant, Ipt(8) As Variant, i As Integer, ii As Integer, iii As Integer
Dim Msg, Style, Title, Help, Ctxt, Response, MyString
i = 0
ii = 0
iii = 16
Range("A16:I100") = ""
Prompt = Array("Line", "Supplier Code", "Full Description", "Acct Code", "Quantity", "UOM", "Price per UOM", "Delivery Date")
For iii = 16 To 100
    For i = 0 To 7
        Do
            Ipt(i) = InputBox("Please enter " & Prompt(i) & Chr(10) & Chr(10) & "Press Spacebar & Ok to leave box blank.")
        Loop While Ipt(i) = ""
    Next I
    Range("A" & iii).Value = Ipt(0)
    Range("B" & iii).Value = Ipt(1)
    Range("C" & iii).Value = Ipt(2)
    Range("E" & iii).Value = Ipt(3)
    Range("F" & iii).Value = Ipt(4)
    Range("G" & iii).Value = Ipt(5)
    Range("H" & iii).Value = Ipt(6)
    Range("I" & iii).Value = Ipt(7)
    Msg = "Any More Entries ?"   ' Define message.
    Style = vbYesNo + vbCritical + vbDefaultButton2 ' Define buttons.
    Title = "MsgBox Demonstration"  ' Define title.
    Help = "DEMO.HLP"   ' Define Help file.
    Ctxt = 1000
    Response = MsgBox(Msg, Style, Title, Help, Ctxt)
    If Response = vbYes Then    ' User chose Yes.
        MyString = "Yes"    ' Perform some action.
    Else: MyString = "No": GoTo line100 ' Perform some action.
    End If
    Erase Ipt()
Next iii
line100: 'Next step is End Sub :bink: 'leave the smilies outta the code tho :cool: 
End Sub

[johnske]

PS...I put the dummy line 100 in deliberately, if - as you say - you're going to print this, you could simply insert a print procedure here (but then you may want another message asking if that's actually what you want to do at this point).

Oh, I also put in a line near the start to clear all previous entries, you may want to put this after the print procedure instead

[Steiner]

Ok, I modified the code. I just changed the loop with the inputbox. If the user enters nothing and presses Ok, the inputbox will come back right again. But if he presses cancel, the whole thing will stop at once.

Sub Iput2()
    Dim Prompt As Variant, Ipt(8) As Variant, i As Integer, ii As Integer, iii As Integer
    Dim temp$
    i = 0
    ii = 0
    iii = 16
    Prompt = Array("Line", "Supplier Code", "Full Description", "Acct Code", "Quantity", "UOM", "Price per UOM", "Delivery Date")
    For iii = 16 To 26
        For i = 0 To 7
            Do
                temp = InputBox("Please enter " & Prompt(i) & Chr(10) & Chr(10) & "Press Spacebar & Ok to leave box blank.")
                If StrPtr(temp) = 0 Then Exit Sub
                Ipt(i) = temp
            Loop While Ipt(i) = ""
        Next i
        Range("A" & iii).Value = Ipt(0)
        Range("B" & iii).Value = Ipt(1)
        Range("C" & iii).Value = Ipt(2)
        Range("E" & iii).Value = Ipt(3)
        Range("F" & iii).Value = Ipt(4)
        Range("G" & iii).Value = Ipt(5)
        Range("H" & iii).Value = Ipt(6)
        Range("I" & iii).Value = Ipt(7)
        Erase Ipt()
    Next iii
End Sub

[BexleyManor]

Steiner/Johnske,

Can't thank you guys enough. I'm a relative VBA newbie and had the task dumped on my desk with the request to come up with results ASAP. Without your help I would have gone home, kicked the cat, growled at the wife then spent the evening pulling hair!!

MUCHOS THANK U, FROM MYSELF, THE WIFE & CAT!!!

[johnske]

My pleasure, mate. These should give you something to get you headed in the right direction, all you have to do is copy/paste/run them, then modify or blend the ideas to best suit yourself...Just leave the cat alone eh? (cats are my frendz)

Regards,
johnske

[johnske]

Hi Chris,

Seems I can?t send attachments on a PM so will post this into a thread instead.

I see you have a solution to your latest dilemma (using TextBoxes to input code for the next part of your project) but here's an alternative solution I did - it runs fine on '97 so should run on later XL versions. (I just couldnt resist doing something entirely different)?It uses one small macro, and a single UserForm and TextBox to input all your data as follows:

Public m As Integer, n As Integer

Sub useform()
Range("A22:K26") = "": n = 0
For m = 0 To 2
    Do
        UserForm1.Show: n = n + 1
    Loop Until n = 5
    n = 0 
Next m
End Sub

Private Sub UserForm_Initialize()
Label1.Caption = "Description"
End Sub

Private Sub CommandButton1_Click()
TextBox1.SetFocus
Range("A22").Offset(m, n) = TextBox1
TextBox1 = ""
If n = 0 Then Label1.Caption = "QUANTITY"
If n = 1 Then Label1.Caption = "UOM"
If n = 2 Then Label1.Caption = "PUOM"
If n = 3 Then Label1.Caption = "DELDATE"
If n = 4 Then Label1.Caption = "Description"
UserForm1.Hide
End Sub

Private Sub CommandButton2_Click()
End
End Sub

Have a look in the work book, there are plenty of comments.

Cheers,
John