2 dimensional loop, averageif formula

[Matthew Dyer]

Hey guys, you've been extremely helpful in the recent past and
hopefully your genuis will shine again.

I am trying to run a 2 dimensional loop. starting cell ("lastrw
+2","lastcol-37"), i want to go cell by cell till "lastcol" and fill
each cell with an averageif formula. once 'lastcol' is reached i want
to start over again at 'lastrw+3' and loop again, then the same for
'lastrw+4'. I think i've got they syntax of the loop correct but i'm
struggling with the averageif formula. help? Here's what i've got so
far. I know... it must look a mess.

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

 

[joeu2004]

i'm struggling with the averageif formula. help? [....]
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

I find it helpful to write a prototype of the formula in Excel and
perhaps even test it in Excel first.

Also, when you ask a syntax question, it is essential that you copy-
and-paste from Excel or VBA into your posting. Apparently you did
not, since VBA does like &i& [sic]; that is, ampersand without
surrounding whitespace.

Or that the problem you want to solve? You never tell us what it is.

It also seems odd to me that the only thing that will change in your
AVERAGEIF is i. My guess is that you also want to "b" to vary with
ii.

Is that another part of the probem you want to solve? Again, you
never really explain what you want AVERAGEIF to look like in each
going across as well as down.

 

[Matthew Dyer]

i'm struggling with the averageif formula. help? [....]
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

I find it helpful to write a prototype of the formula in Excel and
perhaps even test it in Excel first.

Also, when you ask a syntax question, it is essential that you copy-
and-paste from Excel or VBA into your posting.  Apparently you did
not, since VBA does like &i& [sic]; that is, ampersand without
surrounding whitespace.

Or that the problem you want to solve?  You never tell us what it is.

It also seems odd to me that the only thing that will change in your
AVERAGEIF is i.  My guess is that you also want to "b" to vary with
ii.

Is that another part of the probem you want to solve?  Again, you
never really explain what you want AVERAGEIF to look like in each
going across as well as down.

Sorry about the confusion in my description... The syntax of the loop
is correct. It is the averageif formula i'm having difficulty with.
for the first instance of the formula being placed the very first cell
of the loop, here is what I want it to read:
=averageif(a1:a15, b17, c1:c15)

if I were to use auto-fill to drag the formula to every cell i wanted
it in, it would look like this:
=averageif($a$1:$a:$15, $b17, c$1:c$15)
but, since i want to automate it, i'm trying to figure out how to use
the loop variables to fill in the formula as it goes from cell to
cell. does this help?

 

[joeu2004]

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

[....]
It also seems odd to me that the only thing that will change
in your AVERAGEIF is i.  My guess is that you also want to
"b" to vary with ii.

Probably a bad guess. Let me rephrase....

I seems odd to me that you want to fill each row with 38 formulas all
of which look the same in that row, to wit for the kth row:

=AVERAGEIF(A1:An,Bk,C1:Cm)

where n is invariant (lastrow), m is invariant (lastcol) and k is
invariant within the row (k = lastrow+2, lastrow+3 or lastrow+4).

 

[joeu2004]

The syntax of the loop is correct. It is the averageif
formula i'm having difficulty with.

Then what is the difficulty? If you are getting a VBA runtime error,
what is it? If you are getting the wrong formula in the cells, what
do they look like?

for the first instance of the formula being placed the
very first cell of the loop, here is what I want it to
read:
=averageif(a1:a15, b17, c1:c15)

if I were to use auto-fill to drag the formula to every
cell i wanted it in, it would look like this:
=averageif($a$1:$a:$15, $b17, c$1:c$15)

But the third parameter is formed by "c:"&lastcol. And since ii
starts at lastcol-37, lastcol must be 38 or more. Also, since you are
storing into cell(i,ii), lastcol must be 41 or more to avoid circular
reference when ii=3 if lastcol were 38 or 39.

So the third parameter cannot be c1:c15. It must be c1:c41 or more.

With those considerations in mind and assuming "the syntax is
correct" (i.e. putting spaces around all ampersands), your VBA
function performs without error -- albeit perhaps nonsensically, as I
noted in my 2nd follow-up. (Our postings crossed on the ether.)

To be specific, the following code snippet:

Sub doit()
Const lastrw = 15
Const lastcol = 41
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
End Sub

produces the following in D1719:

=averageif(A1:A15,B17,C1:C41)
=averageif(A1:A15,B18,C1:C41)
=averageif(A1:A15,B19,C1:C41)

What does not make sense to me is: those exact 3 formulas are
replicated in rows 17 through 19 of columns E through AO ("ay oh").

So I repeat....

What really do you want the result to be? Explain by example that
includes 3 rows and at least 2 columns.

And where exactly should those formula go? Give the cell names of the
3-row/2-column example.

And what "difficulty" are you having? VBA error? If so, what? Excel
error? If so, what? Wrong formulas? How so?

 

[Javed]

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

 

[Matthew Dyer]

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!

 

[Matthew Dyer]

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

so here's what i've got to get "c" to advance, but as you may
understand ii is returning an integer instead of a text charachter
(i.e 3 instead of c, 4 instead of d, etc.)

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

i've declared a new variable avgcol to be put in place of ii as
follows:

Cells(i, ii).Formula = "=averageif(a1:a" & LastRw & ",b" & i &
"," & avgcol & "1:" & avgcol & LastRw & ")"

but how do i turn 3 into c, 4 into d.... 27 into aa... etc?

 

[Matthew Dyer]

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

 

[Matthew Dyer]

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.