No problems! Glad it helped
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.
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,
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:Code
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?Code
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?