CODEWITHSUNDEEP
excelexcel-formulavba
user2254486
user2254486

Reputation: 37

macro to count and give result

Can anyone help me. I want to count how many of the numbers are > 45 and put the result 3 rows below the last data cell. Lets give it a name - call it result. Then to the left of result I would like to put the words Number > 45. The amount of data rows will change, so when I run the macro on column D it will find the last data point and do the calculation. Some of the rows will be empty. Thanks for the help

Its should like that this

     50          
     20

        100
    120
     45
     30
     30

Return >45= 4 

Sub enter()
    Dim result As Integer
    Dim firstrow As Integer
    Dim lastwow As Integer
    Firstrow = d2 
    Result = ‘ Value of count
    Worksheets("sheet1").Range("c?").Value = "Total>45"
    Range("d100000").End(xlUp).Select
End Sub

Upvotes: 0

Views: 11111

Answers (4)

Dick Kusleika
Dick Kusleika

Reputation: 33175

Here's one that will let you pass in any number, not just 45

Sub MakeCount(lGreaterThan As Long)

    Dim lLast As Long

    With Sheet1
        lLast = .Cells(.Rows.Count, 4).End(xlUp).Row
        .Cells(lLast + 3, 4).FormulaR1C1 = "=COUNTIF(R[-" & lLast + 1 & "]C:R[-3]C,"">""&RC[-1])"
        .Cells(lLast + 3, 3).Value = lGreaterThan
        .Cells(lLast + 3, 3).NumberFormat = """Number>""#"
    End With

End Sub

Upvotes: 1

SeanC
SeanC

Reputation: 15923

is vba required?

if not, the function =COUNTIF(C:C,">45") will give you the answer you want.

Upvotes: 0

Our Man in Bananas
Our Man in Bananas

Reputation: 5977

can't you use a worksheet formula like

=COUNTIF(A2:A7,">45")

alternatively, in VBA as Mr Siddharth Rout suggests in his answer

Upvotes: 0

Siddharth Rout
Siddharth Rout

Reputation: 149325

Try this

Sub Sample()
    Dim result As Long, firstrow As Long, lastrow As Long
    Dim ws As Worksheet
    Dim rng As Range

    '~~> Set this to the relevant worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")

    With ws
        '~~> Find Lastrow in Col D
        lastrow = .Range("D" & .Rows.Count).End(xlUp).Row
        '~~> Set First row
        firstrow = 1

        '~~> Set your range
        Set rng = .Range("D" & firstrow & ":D" & lastrow)

        '~~> Put relevant values
        .Range("C" & lastrow + 3).Value = "Total>45"
        .Range("D" & lastrow + 3).Value = _
        Application.WorksheetFunction.CountIf(rng, ">45")

    End With
End Sub

Screenshot

enter image description here

Upvotes: 2

Related Questions