For loop

[ShaunJ]

Greetings,
I think I got stuck at the for loop. I think I made some mistakes about for loop. The first time I put in the data will stay in the first row. The second time I put in the data, that would start a new row. Instead of repeat like this five times.

Sub AddLot()
    
    Dim Wafers As Integer, stepid, lotidentifier, ss, t, myrange
100:
    
    On Error Resume Next
    Wafers = InputBox("How many Wafers will be processed:", "Inputs", "1 to 25", vbOKCancel)
    
    If Err.Number >= 1 Then Exit Sub
    
    If Val(Wafers) > 25 Or Val(Wafers) < 1 Then
        
        MsgBox "Please enter a valid number within 1 to 25"
        
        GoTo 100
    End If
    
200:
    stepid = InputBox("Input your step ID", "ID Number", vbOKCancel)
    If Err.Number >= 1 Then Exit Sub
    t = checkStep(stepid)
    If t = True Then
    Else
        MsgBox "Please enter a valid step ID."
        GoTo 200
    End If
    
    lotidentifier = InputBox("input your lotidentifier ", "Number", vbOKCancel)
    
    Dim i As Integer
    
    For i = 1 To 5
    
    Set myrange = Range("I2")
    PrintTableHeader myrange
    myrange(i).Offset(1, 0) = lotidentifier
    myrange(i).Offset(1, 1) = Wafers
    myrange(i).Offset(1, 2) = stepid
    myrange(i).Offset(1, 3) = Wafers * Application.WorksheetFunction.VLookup(stepid * 1, Sheet3.Range("A:D"), 2, 0) / 60
    myrange(i).Offset(1, 4) = Wafers * Application.WorksheetFunction.VLookup(stepid * 1, Sheet3.Range("A:D"), 3, 0) / 60
    myrange(i).Offset(1, 5) = myrange.Offset(1, 3) + myrange.Offset(1, 4)
    myrange(i).Offset(1, 0).Resize(1, 6).Borders.LineStyle = xlContinuous
    myrange(i).Offset(1, 3).Resize(1, 3).NumberFormat = "0.00"
    brr = myrange.Offset(i, 0).Resize(1, 6).Value
    
    Next i
    
    If [I4] = "" Then ActiveSheet.Shapes("Button 1").Visible = False Else ActiveSheet.Shapes("Button 1").Visible = True
    
    
End Sub
Function checkStep(s)
    Dim rownumber
    rownumber = Application.Match(s * 1, Sheet3.Range("A:A"), 0)
    If IsError(rownumber) = True Then
        checkStep = False
    Else
        checkStep = True
    End If
End Function
Sub PrintTableHeader(myrange)
    Dim arr
    arr = Array("Lot identifer", "# of Wafers in Lot", "Processing Step", "Inspect Time (Minutes)", "Re-Inspect Time (Minuts)", "Expected Time (Minutes)")
    myrange.Resize(1, UBound(arr) + 1).Value = arr
    myrange.Resize(1, UBound(arr) + 1).Borders.LineStyle = xlContinuous
    myrange.Resize(1, UBound(arr) + 1).Interior.ColorIndex = 37
    myrange.Resize(1, UBound(arr) + 1).Columns.AutoFit
End Sub

Exmaple.xlsm
	H	I	J	K	L	M	N	O
1
2		Lot identifer	# of Wafers in Lot	Processing Step	Inspect Time (Minutes)	Re-Inspect Time (Minuts)	Expected Time (Minutes)
3		1	23	1435	19.21	48.07	67.28
4		1	23	1435	19.21	48.07	67.28
5		1	23	1435	19.21	48.07	67.28
6		1	23	1435	19.21	48.07	67.28
7		1	23	1435	19.21	48.07	67.28
8
9
Report

 

[rlv01]

Hard to know from your very brief problem description, but I think you may need to modify your for-next loop code in this way

    For i = 1 To 5

        Set myrange = Range("I2")
        PrintTableHeader myrange
        myrange.Offset(i, 0) = lotidentifier
        myrange.Offset(i, 1) = Wafers
        myrange.Offset(i, 2) = stepid
        myrange.Offset(i, 3) = Wafers * Application.WorksheetFunction.VLookup(stepid * 1, Sheet3.Range("A:D"), 2, 0) / 60
        myrange.Offset(i, 4) = Wafers * Application.WorksheetFunction.VLookup(stepid * 1, Sheet3.Range("A:D"), 3, 0) / 60
        myrange.Offset(i, 5) = myrange.Offset(i, 3) + myrange.Offset(i, 4)
        myrange.Offset(i, 0).Resize(1, 6).Borders.LineStyle = xlContinuous
        myrange.Offset(i, 3).Resize(1, 3).NumberFormat = "0.00"
        brr = myrange.Offset(i, 0).Resize(1, 6).Value
    Next i

 

[ShaunJ]

Hard to know from your very brief problem description, but I think you may need to modify your for-next loop code in this way

    For i = 1 To 5

        Set myrange = Range("I2")
        PrintTableHeader myrange
        myrange.Offset(i, 0) = lotidentifier
        myrange.Offset(i, 1) = Wafers
        myrange.Offset(i, 2) = stepid
        myrange.Offset(i, 3) = Wafers * Application.WorksheetFunction.VLookup(stepid * 1, Sheet3.Range("A:D"), 2, 0) / 60
        myrange.Offset(i, 4) = Wafers * Application.WorksheetFunction.VLookup(stepid * 1, Sheet3.Range("A:D"), 3, 0) / 60
        myrange.Offset(i, 5) = myrange.Offset(i, 3) + myrange.Offset(i, 4)
        myrange.Offset(i, 0).Resize(1, 6).Borders.LineStyle = xlContinuous
        myrange.Offset(i, 3).Resize(1, 3).NumberFormat = "0.00"
        brr = myrange.Offset(i, 0).Resize(1, 6).Value
    Next i

I am so sorry for the poor explanation. What I mean is if I type 23 then search patch name and data (ex. 1435) which contains in the next spreadsheet, then the order number. The table would like (I manually type this ) when every time I run the Add lot sub

Exmaple.xlsm
	I	J	K	L	M	N
2	Lot identifer	# of Wafers in Lot	Processing Step	Inspect Time (Minutes)	Re-Inspect Time (Minuts)	Expected Time (Minutes)
3	1	25	1435	20.88	52.25	73.13
4	2	23	1436	21.88	53.25	3.13
5	3	24	1435	20.11	121.00	1212.00
Report

I just cannot figure out where I should put the i. and offset(i,0) still look like this

Exmaple.xlsm
	H	I	J	K	L	M	N	O
1
2		Lot identifer	# of Wafers in Lot	Processing Step	Inspect Time (Minutes)	Re-Inspect Time (Minuts)	Expected Time (Minutes)
3		1	25	1435	20.88	52.25	73.13
4		1	25	1435	20.88	52.25	73.13
5		1	25	1435	20.88	52.25	73.13
6		1	25	1435	20.88	52.25	73.13
7		1	25	1435	20.88	52.25	73.13
8
Report

 

[rlv01]

You are looping, but your variables do not change so you get the same result. Example:

 Sub UselessLoop()
 Dim I, myrange
    For I = 1 To 5
        Set myrange = Range("I2")
        PrintTableHeader myrange
        myrange.Offset(I, 0) = "A"                                                                                                       '
        myrange.Offset(I, 1) = "B"                                                                                                       '
        myrange.Offset(I, 2) = "C"                                                                                                       '
        myrange.Offset(I, 3) = "D"                                                                                                       '
        myrange.Offset(I, 4) = "E"                                                                                                       '
        myrange.Offset(I, 5) = "F"                                                                                                       '
    Next I                                                                                                  '
 End Sub

Will produce this:

Lot identifer	# of Wafers in Lot	Processing Step	Inspect Time (Minutes)	Re-Inspect Time (Minuts)	Expected Time (Minutes)
A	B	C	D	E	F
A	B	C	D	E	F
A	B	C	D	E	F
A	B	C	D	E	F
A	B	C	D	E	F

Which is what your code is doing with your variables Wafers, stepid, & lotidentifier