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, Tutor

Category: Homework

Satisfied Customers: 5471

Experience: B.Tech

20870358

Type Your Homework Question Here...

R.R. Jha is online now

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

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

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.

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

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

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.