CODEWITHSUNDEEP
vbaexcel
Mustafa
Mustafa

Reputation: 21

AVERAGEIFS in a loop

I am new at vba, i made a macro for using my daily routines and i need help for AVERAGEIFS.

how can i give a variable for averageifs formula,

what i want, in my loop i want to change " All!I:I " part according to excel.

It will become G:G then M:M then Q:Q ( everytime same amount (for ex: 4 ) of colown will skip...)

any ideas?

thank you so much.

Dim yilne As Long

yilne = MsgBox(ActiveSheet.Name & " Analiz Yili 2018 mi?", vbYesNo)

If yilne = vbYes Then
    yilne = 2018
Else
    yilne = Application.InputBox(prompt:=ActiveSheet.Name & " Analiz Yili Nedir?", Type:=1)
End If

rt = 3
hc = 2
sonay = 7
kacay = cnt
'kacay = Application.InputBox(prompt:=ActiveSheet.Name & " KaçAy var?", Type:=1)

For ilk = 1 To kacay

    Dim lay As Long
    Dim ilktarih As Date
    Dim sontarih As Date

    lay = Cells(rt - 1, hc)
    'lay = Application.InputBox("Enter Month Number 1 to 12")

    ilktarih = DateSerial(yilne, lay, 1)
    sontarih = DateSerial(yilne, lay + 1, 0)

    Cells(rt, hc).Select
    Cells(rt, hc).Formula = "=IFERROR(AVERAGEIFS(All!g:g,All!$B:$B,"">=" & ilktarih & """,All!$B:$B, ""<=" & sontarih & """,All!$A:$A,$A3),"""")"
    hc = hc + 1
Next ilk

Upvotes: 0

Views: 168

Answers (1)

Mustafa
Mustafa

Reputation: 21

i solved it via index formula, my code become;

op = 1
basla = 2

Do Until Cells(1, basla) = ""

  For ilk = 1 To kacay

   Dim lay As Long
   Dim ilktarih As Date
   Dim sontarih As Date

lay = Cells(rt - 1, hc)

ilktarih = DateSerial(yilne, lay, 1)
sontarih = DateSerial(yilne, lay + 1, 0)

Cells(rt, hc).Select


Cells(rt, hc).Formula = "=IFERROR(AVERAGEIFS(INDEX(All!G:AA,0," & op & "),All!$B:$B,"">=" & ilktarih & """,All!$B:$B, ""<=" & sontarih & """,All!$A:$A,$A3),"""")"




hc = hc + 1

Next ilk
basla = basla + kacay
op = op + 4
Loop

Upvotes: 1

Related Questions