On Mar 24, 9:23*am, Matthew Dyer wrote:
On Mar 24, 8:57*am, Matthew Dyer wrote:





On Mar 23, 11:31*pm, Javed wrote:

Hope the following will solve the problem

For i = lastrw + 2 To lastrw + 4
* * For ii = lastcol - 37 To lastcol
* * Cells(i, ii).Formula = "=averageif(a1:a" & lastrw & ",b" & i &
",c1:c" & lastcol & ")"
* * Next ii
Next i

Man, I have no idea what you did differently in your formula to make
it work but it works! Had to make one minor tweak though -

Cells(i, ii).Formula = "=averageif(a1:a" & lastrw & ",b" & i & ",c1:c"
& lastRW & ")"

now the only other thing i need help with is getting the C's to
advance with each step of the loop... Thanks for your help Javed!

so browsing the forum, i found a function made by Dana DeLouis that
does exactly what I'm looking for (converting numbers to cooresponding
column letter). But I am getting an error "ByRef argument type
mismatch" at the Number2Letter(ii) portion of the below code.

Sub avg()
Dim LastCol As Integer
Dim LastRw As Long
Dim avgcol As Long

Range("A1:A" & LastRow(ActiveSheet)).AdvancedFilter
Action:=xlFilterCopy, CopyToRange:=Range( _
* * * * "B" & LastRow(ActiveSheet) + 1), Unique:=True

With ActiveSheet
* * LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
* * LastRw = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

For i = LastRw + 2 To LastRw + 4
* * For ii = LastCol - 36 To LastCol

* * * * Cells(i, ii).Formula = "=averageif(a1:a" & LastRw & ",b" & i &
"," & Number2Letter(ii) & "1:" &_
*Number2Letter(ii) & LastRw & ")"
* * Next ii
Next i

End Sub

Function Number2Letter(N As Integer) As String
'// Dana DeLouis
* *If N < 1 Or N 256 Then
* * * Number2Letter = Error(9) ' Subscript out of range
* * * Exit Function
* *End If
* *Number2Letter = split(Cells(N).Address, "$")(1)
End Function- Hide quoted text -

- Show quoted text -

never mind. since i never declared ii as integer that's why the
function wouldn't accept it. did my declaration and now is running
perfectly.