macro to count and give result

Question

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

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

Has any of the answers answered your question? If so please help other users by marking it as the answer, see tour. — glh
– glh, Commented Apr 11, 2013 at 10:39

Siddharth Rout · Accepted Answer · 2013-04-10 16:08:27Z

2

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

answered Apr 10, 2013 at 16:08

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user2140261 Over a year ago

Is there a reason behind why you set firstrow = 1 when you could just do Set rng = .Range("D1:D" & lastrow) ? Or as the OP seems to want Set rng = .Range("D2:D" & lastrow)

Siddharth Rout Over a year ago

Yes there is a reason :) And that is flexibility to change the row number. I can do it as you suggested but by habit, I always declare my variables in the beginning so that I don't have to change the code every where but just at one location.

Dick Kusleika · Accepted Answer · 2013-04-10 18:52:08Z

1

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

answered Apr 10, 2013 at 18:52

Dick Kusleika

33.3k4 gold badges56 silver badges81 bronze badges

Comments

Our Man in Bananas · Accepted Answer · 2013-04-10 16:10:11Z

0

can't you use a worksheet formula like

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

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

answered Apr 10, 2013 at 16:10

Our Man in Bananas

6,00525 gold badges104 silver badges158 bronze badges

2 Comments

Siddharth Rout Over a year ago

Even I thought of worksheet formula but then this is what OP had to say The amount of data rows will change ;)

user2254486 Over a year ago

Thanks everyone for your effort this helps

SeanC · Accepted Answer · 2013-04-10 16:10:17Z

0

is vba required?

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

answered Apr 10, 2013 at 16:10

SeanC

16k5 gold badges48 silver badges67 bronze badges

Collectives™ on Stack Overflow

macro to count and give result

4 Answers 4

2 Comments

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related