CODEWITHSUNDEEP
excelvbafunction
g0dsreaper
g0dsreaper

Reputation: 35

AverageIf formula through VBA macro returns "False" instead of a value

Im trying to use the AverageIf function in my code that uses a cell as criteria. This cell is also in the same macro. When I do this I get "False" returned instead of a value. This is what I have. {Sub make_new_entry()

Dim Layer_Increment, i As Integer
 Set Layer_Increment = Cells(7, 2)
 

y = 10       'Set this to the first row number that gets a time entered.
i = 0

Do While i = 0
    If Cells(y, 1) = "" Then
        add_row y
        
        Cells(y, 1) = Format(Now(), "m/d/yy")
        Cells(y, 2) = Format(Now(), "h:mm")
        Cells(y, 3) = Cells(y - 1, 2) - ((Cells(y - 1, 2) - Layer_Increment * (y - 10)))
        Cells(y, 8).Formula = "=($H$7*(C" & y & "))+((0.5*'Data Reference'!F2)+(0.4*'Data Reference'!F3)+(0.3*'Data Reference'!F4)+(0.2*'Data Reference'!F5)+(0.1*'Data Reference'!F6)+(0.05*'Data Reference'!F7)+(0.01*'Data Reference'!F8)+(0.001*'Data Reference'!F9))+ $H$10+$N$7"
        Cells(y, 9).Formula = "=$I$10-(0.05*(C" & y & "-$C$10))"
        Cells(y, 10).Formula = "=((((A" & y & ") + (B" & y & "))-((A" & y - 1 & ")+(B" & y - 1 & ")))*1440)/((C" & y & ") - (C" & y - 1 & "))"
        Cells(y, 11).Formula = "=((B" & y & ") + (A" & y & ")) - ($B$10+$A$10)"
        Cells(7, 6).Formula = "=((130-(H" & y & "))/$H$7)-('Build Information'!$F$6-(C" & y & "))"
        Cells(7, 4).Formula = "=Max(C:C)"
        Cells(1, 15).Formula = "=(J" & y - 1 & ")+1.5"
        Cells(7, 10).Formula = "=AverageIf(J11:(J" & y & ")," < " &O1)"
        i = 1
   Else
        y = y + 1
    End If
Loop

End Sub}
Can anyone help me figure out what Im doing wrong.

Upvotes: 0

Views: 66

Answers (1)

BigBen
BigBen

Reputation: 49998

You need to double up the quotes around the < to actually include it in the string (it is currently outside the quotes and thus a comparison operator). Then, your parentheses are off:

Cells(7, 10).Formula = "=AverageIf(J11:J" & y & ",""<""&O1)"

Upvotes: 1

Related Questions