CODEWITHSUNDEEP
excelvba
Fah
Fah

Reputation: 203

How to use the IF for selection of cells?

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

Upvotes: 0

Views: 53

Answers (1)

Warcupine
Warcupine

Reputation: 4640

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

Upvotes: 3

Related Questions