Hi guys,
I am confused about how to fix the code. Every time I click the button and enter the data, the code will start a new row right below the previous one. I know the variable should up to 5 since there are only 5 sets of data but I don't know how to make the variable "i" work with offset, or should I make it like a loop?
Should I use for loop? like i = 0:4 and myrange(i+1) and the header will stay within the cell, and the input data will go to a new row?
I am confused about how to fix the code. Every time I click the button and enter the data, the code will start a new row right below the previous one. I know the variable should up to 5 since there are only 5 sets of data but I don't know how to make the variable "i" work with offset, or should I make it like a loop?
Should I use for loop? like i = 0:4 and myrange(i+1) and the header will stay within the cell, and the input data will go to a new row?
VBA Code:
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 ", vbOKCancel)
Dim i As Integer
Set myrange(i) = Range("I2")
PrintTableHeader myrange
myrange.Offset(i + 1, 0) = lotidentifier
myrange.Offset(i + 1, 1) = Wafers
myrange.Offset(i + 1, 2) = stepid
myrange.Offset(i + 1, 3) = Wafers * Application.WorksheetFunction.VLookup(stepid * 1, Sheet3.Range("A:D"), 2, 0) / 60
myrange.Offset(i + 1, 4) = Wafers * Application.WorksheetFunction.VLookup(stepid * 1, Sheet3.Range("A:D"), 3, 0) / 60
myrange.Offset(i + 1, 5) = myrange.Offset(1, 3) + myrange.Offset(1, 4)
myrange.Offset(i + 1, 0).Resize(1, 6).Borders.LineStyle = xlContinuous
myrange.Offset(i + 1, 3).Resize(1, 3).NumberFormat = "0.00"
brr = myrange.Offset(1, 0).Resize(1, 6).Value
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 | |||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | 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 | 2 | 23 | 1435 | 19.21 | 48.07 | 67.28 | |||||||||||
| 4 | |||||||||||||||||
| 5 | |||||||||||||||||
| 6 | |||||||||||||||||
| 7 | |||||||||||||||||
| 8 | |||||||||||||||||
| 9 | |||||||||||||||||
| 10 | |||||||||||||||||
| 11 | |||||||||||||||||
| 12 | |||||||||||||||||
| 13 | |||||||||||||||||
| 14 | |||||||||||||||||
| 15 | |||||||||||||||||
| 16 | |||||||||||||||||
| 17 | |||||||||||||||||
| 18 | |||||||||||||||||
| 19 | |||||||||||||||||
| 20 | |||||||||||||||||
| 21 | |||||||||||||||||
| 22 | |||||||||||||||||
Report | |||||||||||||||||
Last edited:
