Array Sum question

[ShaunJ]

Hi guys,
Here is another question about the excel passing array. Whenever the data was put into the range L to M, the sum of three values will appear in P3. I cannot figure out the offset and loop stuff. CalculateTotal sub would be the part i would liko figure out.

XJProject.xlsm
	H	I	J	K	L	M	N	O	P
1
2
3
4
5
6
Report

Here comes my 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)
    
   
    Set myrange = Application.InputBox(prompt:="Select a cell to print header", Type:=8)
    PrintTableHeader myrange
    myrange.Offset(1, 0) = lotidentifier
    myrange.Offset(1, 1) = Wafers
    myrange.Offset(1, 2) = stepid
    myrange.Offset(1, 3) = Wafers * Application.WorksheetFunction.VLookup(stepid * 1, Sheet3.Range("A:D"), 2, 0) / 60
    myrange.Offset(1, 4) = Wafers * Application.WorksheetFunction.VLookup(stepid * 1, Sheet3.Range("A:D"), 3, 0) / 60
    myrange.Offset(1, 5) = myrange.Offset(1, 3) + myrange.Offset(1, 4)
    myrange.Offset(1, 0).Resize(1, 6).Borders.LineStyle = xlContinuous
    myrange.Offset(1, 3).Resize(1, 3).NumberFormat = "0.00"
     brr = myrange.Offset(1, 0).Resize(1, 6).Value
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 CalculateTotal()

Dim CalTotal As Range

[P3].Value = CalTotal

 

End Sub
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
Sub Format()
    Range("L3").Select
    Selection.NumberFormat = "0.000"
    Selection.NumberFormat = "0.00"
    Selection.NumberFormat = "0.0"
    Selection.NumberFormat = "0.00"
    Selection.NumberFormat = "0.000"
End Sub
Sub Font()
    '
    Selection.Font.Bold = True
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent5
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent1
        .TintAndShade = 0.399975585192419
        .PatternTintAndShade = 0
    End With
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent5
        .TintAndShade = 0.399975585192419
        .PatternTintAndShade = 0
    End With
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent5
        .TintAndShade = -0.249977111117893
        .PatternTintAndShade = 0
    End With
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent5
        .TintAndShade = 0.399975585192419
        .PatternTintAndShade = 0
    End With
End Sub

 

[StephenCrump]

It's only a guess, but it sounds you're asking for:

Range("P3").Value = Application.Sum(Range("L3:N3"))

It's not clear what this means?

I cannot figure out the offset and loop stuff

 

[ShaunJ]

It's only a guess, but it sounds you're asking for:

Range("P3").Value = Application.Sum(Range("L3:N3"))

It's not clear what this means?

Here is the description

The time of the lots entered by the user must be calculated using the information stored in the global array(s) during the AddLot() sub procedure. The total time must be calculated using all the values entered by the user for the lots and the method described at the end of the document for lots. How you implement this method is up to you.

Once the data was entered and there is a cell that will store the total time. When the data was entered into the second row, once I press the calculate button, the total time of second row would go to the second cell below the first cell.

 

[StephenCrump]

Here is the description

The time of the lots entered by the user must be calculated using the information stored in the global array(s) during the AddLot() sub procedure. The total time must be calculated using all the values entered by the user for the lots and the method described at the end of the document for lots. How you implement this method is up to you.

We may be able to help if you have a specific VBA question that you are stuck on. But at the moment, it's far from clear what your question is.

We can't tell you how to do your homework.