Code VBA average function when adding data with userform

[tjdickinson]

I have a userform that takes input from the user and adds it to the first five rows in the next available column of the worksheet.

In row 6, I want a formula like:

=IFERROR(AVERAGE(D7:D32),"")

but the cell reference, of course, has to be for the current column (which changes with each instance of the userform).

I'm making a gradebook, and the row in question is giving the class average result of the given assignment. Thus, in (ex.) D7:D32, the user adds the student's scores, and D6 calculates the average of those scores. The cells in the range are completely empty (including no functions/formulas), so blank cells should be ignored. (Of course, zeros should not be ignored.) The class average cell (in row 6) should appear blank until the user adds values in the range below it.

I currently have this code:

Private Sub Add_Eval_Add_Click()
Dim iCol As Long
Dim ws As Worksheet
Set ws = Worksheets("Gradebook")

'find first empty column in database
iCol = ws.Cells.Find(What:="*", SearchOrder:=xlColumns, _
    SearchDirection:=xlPrevious, LookIn:=xlValues).Column + 1

'irrelevant code removed for brevity

With ws
'  .Unprotect Password:="password"
  .Cells(1, iCol).Value = Me.Eval_Title.Value
  .Cells(2, iCol).Value = Me.Eval_Cat.Value
  .Cells(3, iCol).Value = Me.Eval_Date.Value
  .Cells(3, iCol).NumberFormat = "dd/mm"
  .Cells(4, iCol).Value = Me.Eval_Due_Date.Value
  .Cells(4, iCol).NumberFormat = "dd/mm"
  .Cells(5, iCol).Value = Me.Eval_Points.Value
  .Cells(5, iCol).NumberFormat = "##0.0"
' Here's where I want to add the average function
  .Cells(6, iCol).NumberFormat = "##0.0"
'  .Protect Password:="password"
End With
End Sub

So far, I've tried the following code in the indicated position:

.Cells(6, iCol).Value = Application.Average(Range((7, iCol), (32, iCol)))

.Cells(6, iCol).Formula = "=IFERROR(AVERAGE(D7:D32),"")"

.Cells(6, iCol).Value = "=IFERROR(AVERAGE(D7:D32),"")"

but none of them work.

Any ideas, suggestions, or solutions very much welcome.

 

[offthelip]

try this which will work up to column AZ:

If iCol < 27 Then
lcol = Chr(64 + iCol)
Else
lcol = "A" & Chr(64 + iCol - 26)
End If
'.Cells(6, iCol).Formula = "=IFERROR(AVERAGE("D7:D32),"")"
Cells(6, iCol).Formula = "=IFERROR(AVERAGE(" & lcol & "7:" & lcol & "32)," & Chr(34) & Chr(34) & ")"

 

[offthelip]

correction , I left the dot out:

.Cells(6, iCol).Formula = "=IFERROR(AVERAGE(" & lcol & "7:" & lcol & "32)," & Chr(34) & Chr(34) & ")"

 

[tjdickinson]

try this which will work up to column AZ:

If iCol < 27 Then
lcol = Chr(64 + iCol)
Else
lcol = "A" & Chr(64 + iCol - 26)
End If
'.Cells(6, iCol).Formula = "=IFERROR(AVERAGE("D7:D32),"")"
Cells(6, iCol).Formula = "=IFERROR(AVERAGE(" & lcol & "7:" & lcol & "32)," & Chr(34) & Chr(34) & ")"

Thanks, offthelip! This works for the class average. But, as you say, the limit is that it will only work through column AZ, and I'm afraid I'll certainly need more than 52 evaluations over the course of the year. Do you think the following code would work?

' This part sets a new variable mCol which is used to assign the last value of the column ID. If the column ends A-Y, then the Else function stores the value 
' as 1-25. If the column ends in Z, then the Mod operation returns 0, so the stored value is 26.
If iCol Mod 26 = 0 Then
mCol = 26
Else
mCol = iCol Mod 26
End If

' This is a modified version of the code you wrote. The Then function is unchanged.
' In the Else function, I replaced "A" with an operation that should return the whole number quotient of the column less one over 26, rounded down. Thus, 
' if the column is AA, then iCol = 27, and 27-1/26 is 1 (= A). If the column is BN, then iCol = 66, and 66-1 = 65/26 = 2.5, rounded down to 2 (=B).
' For the second column ID, I just replaced iCol - 26 with the new variable mCol, defined above.
If iCol < 27 Then
lCol = Chr(64 + iCol)
Else
lCol = Chr(64 + RoundDown((iCol - 1) / 26, 0) & Chr(64 + mCol )
End If

I don't understand the syntax of the AVERAGE() operation in your code. I mean, it works, and I get what each part is doing, but I don't understand when to use " and & and what they do in the code. (I feel like that's an important thing for writing similar code in the future.)

Thanks again!

 

[offthelip]

I think your code will work fine for columns up to ZZ, you could even expand if you need more columns than that!!
One of the problems when writing formula using vba is they are basically text strings and VBa uses the " (double quote) to signify the start and end of text strings, so the problem arises when you want to include the double quote in the actual formula. Excel does allow you double quotes including double quotes to signify you want it in the formula. I find this very difficult to read , so I use the ascii character function chr(34) to signify a double quote. So in my code a double quote is the start or the end of a text string that I want included in the formula. I then concatenate each bit of the string together using the ampersand character "&" which signify concatenation in vba. So just taking the end of the equation:

Chr(34) & Chr(34) & ")"

this saying: double quotes character concatenated to double quote character concatenated to close bracket character

 

[offthelip]

One slight error, rounddown doesn't exist in vba so you need to call the excel function like this:

lcol = Chr(64 + Application.RoundDown((iCol - 1) / 26, 0)) & Chr(64 + mCol)

 

[tjdickinson]

I think your code will work fine for columns up to ZZ, you could even expand if you need more columns than that!!
One of the problems when writing formula using vba is they are basically text strings and VBa uses the " (double quote) to signify the start and end of text strings, so the problem arises when you want to include the double quote in the actual formula. Excel does allow you double quotes including double quotes to signify you want it in the formula. I find this very difficult to read , so I use the ascii character function chr(34) to signify a double quote. So in my code a double quote is the start or the end of a text string that I want included in the formula. I then concatenate each bit of the string together using the ampersand character "&" which signify concatenation in vba. So just taking the end of the equation:

Chr(34) & Chr(34) & ")"

this saying: double quotes character concatenated to double quote character concatenated to close bracket character

Thanks for the very helpful explanation!

I'm hoping ZZ is sufficient...if a student gets more than 673 evaluations in one year, there might be different problems to sort out. haha

Okay--I see now what you mean about the text strings. I was confused because I hadn't noticed the " before the = in "=IFERROR(AVERAGE(" , so for the rest of it the strings seemed inverted. Makes perfect sense now.

I'm not sure how to correctly 'mark as solution' in this case...I want to give you the credit for it, but the final code I need is in my reply. Could you reply to my post with the code in it, and then I'll mark it as the solution?

 

[offthelip]

the solution:

' This part sets a new variable mCol which is used to assign the last value of the column ID. If the column ends A-Y, then the Else function stores the value 
' as 1-25. If the column ends in Z, then the Mod operation returns 0, so the stored value is 26.
If iCol Mod 26 = 0 Then
mCol = 26
Else
mCol = iCol Mod 26
End If

' This is a modified version of the code you wrote. The Then function is unchanged.
' In the Else function, I replaced "A" with an operation that should return the whole number quotient of the column less one over 26, rounded down. Thus, 
' if the column is AA, then iCol = 27, and 27-1/26 is 1 (= A). If the column is BN, then iCol = 66, and 66-1 = 65/26 = 2.5, rounded down to 2 (=B).
' For the second column ID, I just replaced iCol - 26 with the new variable mCol, defined above.
If iCol < 27 Then
lCol = Chr(64 + iCol)
Else
lCol = Chr(64 + Application.RoundDown((iCol - 1) / 26, 0) & Chr(64 + mCol )
End If