AverageIFS statement in VBA and question with dropbox selection

[konficus]

Hi guys,

I need a correction for my formula, I want to calculate averages for given dates,

    Dim FirstDate As Date
    Dim LastDate As Date


    FirstDate= Application.InputBox("First Date")
    LastDate = Application.InputBox("Last Date")


    Range(Cells(3, anay), Cells(3, anay)).Formula = "=IFERROR(AVERAGEIFS(All!I:I,All!B:B,>=" & FirstDate& ",All!B:B,<=" & LastDate & ",All!A:A,A3),"")"

gives error.

and ccording to top question is it possible to not giving first and last date with manual, can be choosen from a dropbox,for example,

months from 4 to 10, 4 = April, 10 = October,
If choose 5 from drop box, first date will become 01/05/2017 last date become 31/05/2017?

thank you for your helps.

 

[konficus]

any help is much appreciated

 

[FormR]

Hi, the formula I think should be:

Cells(3, anay).Formula = "=IFERROR(AVERAGEIFS(All!I:I,All!B:B,"">=" & FirstDate & """,All!B:B, ""<=" & LastDate & """,All!A:A,A3),"""")"

To only have to enter a month number you could try something like:

Dim lMonth As Long
Dim FirstDate As Date
Dim LastDate As Date

lMonth = Application.InputBox("Enter Month Number 1 to 12")
FirstDate = DateSerial(Year(Date), lMonth, 1)
LastDate = DateSerial(Year(Date), lMonth + 1, 0)

Cells(3, anay).Formula = "=IFERROR(AVERAGEIFS(All!I:I,All!B:B,"">=" & FirstDate & """,All!B:B, ""<=" & LastDate & """,All!A:A,A3),"""")"

To have the month number be selected from a combo box you'll need to create a userform.

 

[konficus]

Dear FormR,

thank you, that is super, and possible a little additional, it is working first date month and last date month but it took this year, but now i am analysing 2018, is it possible to make year 17 or 18 like year selection?

 

[FormR]

is it possible to make year 17 or 18 like year selection?

Hi, something like this?

Dim lMonth As Long
Dim lYear As Long
Dim FirstDate As Date
Dim LastDate As Date

lMonth = Application.InputBox("Enter Month Number 1 to 12")
lYear = Application.InputBox("Enter the Year")

FirstDate = DateSerial(lYear, lMonth, 1)
LastDate = DateSerial(lYear, lMonth + 1, 0)

Cells(3, 3).Formula = "=IFERROR(AVERAGEIFS(All!I:I,All!B:B,"">=" & FirstDate & """,All!B:B, ""<=" & LastDate & """,All!A:A,A3),"""")"

 

[konficus]

Thanks worked. Perfect.

 

[konficus]

And i need help again, 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.

Cells(3, 3).Formula = "=IFERROR(AVERAGEIFS(All!I:I,All!B:B,"">=" & FirstDate & """,All!B:B, ""<=" & LastDate & """,All!A:A,A3),"""")"

It will become I:I then M:M then Q:Q ( everytime same amount of colown will skip...

any ideas?

thank you so much.

 

[FormR]

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

Hi, can you show us your loop code?

 

[konficus]

actually didnt create a loop code yet, first iwould like to figure out how to change colowns,

below code is makes same formula for each cells for example from 1 to 5...

Dim yilne As Long yilne = MsgBox(ActiveSheet.Name & " Analiz Yılı 2018 mi?", vbYesNo)
If yilne = vbYes Then
yilne = 2018
Else
yilne = Application.InputBox(prompt:=ActiveSheet.Name & " Analiz Yılı 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

 

[konficus]

I guess,i found it with a little help, 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)
'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(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