How JustAnswer Works:

  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.

Ask R.R. Jha Your Own Question

R.R. Jha
R.R. Jha, Tutor
Category: Homework
Satisfied Customers: 5471
Experience:  B.Tech
20870358
Type Your Homework Question Here...
R.R. Jha is online now
A new question is answered every 9 seconds

i have to write a VBA code to realize the countifs to corresponding

This answer was rated:

i have to write a VBA code to realize the countifs to corresponding resutls.
ColA ColB
1 8
1 3
2 3
2 4
2 8
2 4
3 5
3 6
4 7
4 8
5 8
5 5
5 4
6 5
6 6
6 7
with a result as count only if ColB >5
ColA ColB
1 1
2 1
3 2
4 2
5 2
6 3
Hi,

I'd be glad to help. I'll work it out and get back to you asap.


Thanks
Try this code. Based on output, I assumed you're looking for >=5 rather than >5. It puts result into column D and E.


Sub myCount()
Dim min As Double, max As Double
min = Application.WorksheetFunction.min(Columns(1))
max = Application.WorksheetFunction.max(Columns(1))
For i = min To max
range("D" & (i - min + 1)).Value = i
range("E" & (i - min + 1)).Value = Application.WorksheetFunction.CountIfs(Columns(1), i, Columns(2), ">=5")
Next
End Sub



Thanks
Customer: replied 3 years ago.

Can I put the result in a new workbook?


How to add this code

Yes, we can. I'll modify the code and let you know.
THIS ANSWER IS LOCKED!

You need to spend $3 to view this post. Add Funds to your account and buy credits.
R.R. Jha and 4 other Homework Specialists are ready to help you
Customer: replied 3 years ago.


Sorry but I think I explain it wrong, I need to make the result in another worksheet instead of wookbook.

Ok, I'll modify it.
Customer: replied 3 years ago.


and the result seems to be not correct. When I tried this code, the first column of the result turn to be numbers, but in the real data, the first column of the results should be the names not number. Because the original dataset's first column contains people's name instead of numbers.

Ok, I'll change it to work for strings
Here you go.


Sub myCount()
Set ws1 = ActiveSheet
Set ws2 = Worksheets.Add(After:=ws1)
ws1.range("A1", ws1.range("A65536").End(xlUp)).Copy ws2.range("A1")
ws2.range("A1", ws2.range("A65536").End(xlUp)).RemoveDuplicates 1, xlNo
For Each c In ws2.range("A1", ws2.range("A65532").End(xlUp)).Cells
Count = Application.WorksheetFunction.CountIfs(ws1.Columns(1), c, ws1.Columns(2), ">=5")
ws2.range("B" & c.Row).Value = Count
Next
End Sub
Customer: replied 3 years ago.


Hello, its me agian. I am wondering how to add other countifs accourding to the results that we developed in the new worksheets from other columns in the original worksheets. Like if I have column d, e, f, g as well,


and i want to count the numbers of data in each column according to the Column A in ws2

Just change formula for count.

Count = Application.WorksheetFunction.CountIfs(ws1.Columns(1), c, ws1.Columns(2), ">=5")

In above formula, Columns(2) means it's counting values in column B. For counting values in column C change it to Columns(3), for D change it to Columns(4) and so on.

Also, change the line just below that to put other counts in separate column.

ws2.range("B" & c.Row).Value = Count

If you're counting values in column C, change "B" to "C" and so on.



Thanks