Posts by baddyswing

No problems! Glad it helped

Hi DrRandyman,

One way could be to create a reference table where every possible letter grade has a corresponding integer value (i.e. score). You can then use VLOOKUP formula to retrieve the score for each grade in S2:S23 and then average them. Since the average result could be non-integer, you would need to round it. Final step is to take the rounded score and reference the matching letter grade (using INDEX and MATCH formula).

I have attached an example of how this could be done.

In the case that every grade has equal weighting as each other (e.g. getting grade B is mildly worse than grade A and getting grade C is also equally mildly worse than grade B), then for a scale containing a total of 14 letter grades, the numerical values would be 1 to 14.

Hope this sounds clear.

Best regards,

Roy

When you say "remaining amount of quizzes", do you mean those quizzes in columns J-O that have grades that are not the lowest grade? So in the case of row 4, there would be 5 remaining quizzes? I'm guessing when you want to get the average, you don't mean average quiz grade per student?

Did you get any kind of error message popping up? If so, can you please post up a screenshot of the error message? Or was it something like #N/A in the cell where the function is called from? To help identify the source of the issue, need to first narrow down which line in the code is failing. For example, it could be failing on line 3, 5 or 8 (or on multiple lines). Or it could be having troubles with the argument "day".

Where you have,

desWS.Cells(rngList(Val), 9) = v2(i, 2)

Is it possible to firstly read all the contents from column 9 in desWS worksheet into a variable, and then write into this new variable and finally write back out to the desWS worksheet at the end after the second loop? Something like this:

Sub STEP9()

Dim Val As String, wb1 As Workbook, wb2 As Workbook, srcWS As Worksheet, desWS As Worksheet
Dim i As Long, v1 As Variant, v2 As Variant, rngList As Object
Dim lastRow As Long
' create v3 for reading in column 9 of desWS
Dim v3 as Variant

Application.ScreenUpdating = False

Set wb1 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\HotStocks\1.xls")
Set wb2 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\HotStocks\AlertCodes.xlsx")
Set desWS = wb1.Worksheets(1)
Set srcWS = wb2.Worksheets(1)

lastRow = desWS.Range("B" & desWS.Rows.Count).End(xlUp).Row
If lastRow = 2 Then
    ReDim v1(1 To 1, 1 To 1)
    v1(1, 1) = desWS.Range("B2").Value
Else
    v1 = desWS.Range("B2:B" & lastRow).Value
End If

v2 = srcWS.Range("A2", srcWS.Range("A" & srcWS.Rows.Count).End(xlUp)).Resize(, 2).Value

v3 = desWS.Range(desWS.cells(1, 9), desWS.cells(lastRow, 9)).Value

Set rngList = CreateObject("Scripting.Dictionary")
For i = 1 To UBound(v1, 1)
    Val = v1(i, 1)
    If Not rngList.Exists(Val) Then
        rngList.Add Key:=Val, Item:=i + 1
    End If
Next i
For i = 1 To UBound(v2, 1)
    Val = v2(i, 1)
    If rngList.Exists(Val) Then
        ' write into v3 first
        v3(rngList(Val), 1) = v2(i, 2)
    End If
Next i

' write v3 out to worksheet once
desWS.Range(desWS.cells(1, 9), desWS.cells(lastRow, 9)) = v3

Application.ScreenUpdating = True

wb1.Save
wb2.Save
wb1.Close
wb2.Close

End Sub

I made an assumption that the number of rows for column 9 in desWS is the same as column B.

Is this what you mean?

ReDim Array1 (1 to 11, 1 to 15)
ReDim Array2 (1 to 11, 1 to 20)
ReDim SumArray (1 to 11, 1)

For row = 1 To 11 
    For array1_col = 1 to 15
        SumArray(row, 1) = SumArray(row, 1) + Array1(row, array1_col)
    Next array1_col

    For array2_col = 1 to 20
        SumArray(row, 1) = SumArray(row, 1) + Array2(row, array2_col)
    Next array2_col
Next row

I'm posting this on a computer that doesn't have Excel so I haven't test this code yet.

Can you also post a sample workbook?