How to use the IF for selection of cells?

Question

I have a pivot and I want to count 6 cells from last row up, however sometime in the pivot there will be less then 6 cells with a value.

In this case how can I use IF the selection of cells is less then 6 cells with value?

How could I say: IF the selection is less then 6 cells with value Then count the total of cells with value.

    Sub Last_6_Months_Frequency()


    Dim lastRow As Long
    Dim Seltall As Long
    Dim Seltallc As Long
    Dim cseleall  As Long


        With Sheet1
        lastRow = .Cells(Rows.count, 4).End(xlUp).row
        .Range("D3").Formula = "=AVERAGE(D" & lastRow - 6 & ":D" & lastRow - 1 & ")"

 if selection  < 6 celss then

        lastRow = .Cells(Rows.count, 4).End(xlUp).Offset(-1, 0).Select


        Seltall = Range(Selection, Selection.End(xlUp)).Select

        Seltallc = Application.sum(Selection)

        lastRow = .Cells(Rows.count, 4).End(xlUp).Offset(-1, 0).Select

        Seltall = Range(Selection, Selection.End(xlUp)).Select

        cseleall = WorksheetFunction.count(Selection)

      .Range("D3").Value = Application.sum(Seltallc) / WorksheetFunction.count(Selection)


      End With

Per @Warcupine answer removing all selections, [How to avoid using Select...](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) is worth reading and understanding for future code. — Samuel Everson, Apr 23 '20 at 14:23

score 3 · Accepted Answer · answered Apr 23 '20 at 14:16

3

You can do this by iterating through the cells and checking for blank values, if you encounter a value increment a variable.

I also removed all your selections.

If there is a possibility that the lastrow - 1 is <= row(6) you should add a check otherwise it will yell about having a row of 0 or negative.

    Dim lastRow As Long
    Dim rangetouse As Range
    With Sheet1
        lastRow = .Cells(Rows.Count, 4).End(xlUp).Offset(-1, 0).Row

        Set rangetouse = .Range(.Cells(lastRow, 4), .Cells(lastRow - 6, 4))

        Dim cell As Variant
        Dim valcount As Long
        valcount = 0
        For Each cell In rangetouse
            If cell.Value <> "" Then
                valcount = valcount + 1
            End If
        Next cell

        If valcount < 6 Then
            .Range("D3").Value = Application.Sum(rangetouse) / Application.Count(rangetouse)
        End If

    End With

answered Apr 23 '20 at 14:16

Warcupine

4,460
3
15
24

Thank you, May do you know if there is a way for this possibly scenario? If the client transacted more than 6 and 12 months, we will use the last 6 and 12 months transacted by the client as: Apr, May, Jun, Jul, Aug, Sep, Dec 2019 and Feb, Mar, Apr 2020 (data in the pivot) _last 6 months = Apr 2020, March 2020, February 2020, December 2019 / 4 months (as we need to divide only by the number of months client was active in the last 6 months)_ – Fah Apr 23 '20 at 16:05
@Fah You could use a combination of ```now(), month(), and year()``` to figure that out along with another counter variable to keep track of how many months they were actually active. – Warcupine Apr 23 '20 at 17:10
and I guess in this case it will need another `IF` if there is less then 6 months active in the semester etc. – Fah Apr 23 '20 at 18:04
@Fah yeah if you need to know that specifically and not just the number to divide by. – Warcupine Apr 23 '20 at 18:29

How to use the IF for selection of cells?

1 Answers1