Fastest way to Go Across and Find - VBA Excel

Hello All,

I have a going across issue. In sheet 1 as you can see that each code can have either a space or yes when going across. What I need to function that tells me this:
If I enter 2 in [E2] then I see the number 1 in [H2] ….so basically, it returns the count of the occurrences of the string “YES” for a row that you specified in cell E2

How can I do that in VBA? In the quickest speed way?

Thank you
findOccurence.xlsx
RayneAsked:
Who is Participating?
 
krishnakrkcCommented:
Hi

To make it dynamic, try

=IFERROR(COUNTIF(INDEX(E7:INDEX(J7:J1048576,MATCH("zzzzz",D7:D1048576)),E2,),"Yes"),0)

BTW, Indirect is a volatile function. See:

http://www.decisionmodels.com/calcsecretsi.htm

Kris
0
 
Swapnil NirmalManager, Audit AnalyticsCommented:
Your solution is attached
findOccurence.xlsm
0
 
krishnakrkcCommented:
Hi

Another way..

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Not Application.Intersect(Target, Range("e2")) Is Nothing Then
        Range("h2") = Evaluate("=IFERROR(COUNTIF(INDEX(E7:J9,E2,),""Yes""),0)")
    End If
    
End Sub

Open in new window


Kris
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
mark_harris231Commented:
You don't need VBA for this.  Just use this formula in H2:

=COUNTIF(INDIRECT("E"&MATCH(E2,C:C,0)&":J"&MATCH(E2,C:C,0)),"Yes")

This is close to the "Evaluate" statement in krishnakrkc's post, but isn't dependent on hard-coding the range.  Presumably, your actual list length is larger or will grow over time.  Using the MATCH function in the INDIRECT function means it will look in all of Column C for the first match of the value in E2.  This could be made more efficient by use of a Dynamic Named Range that will only look in those cells in Column C that have a value entered.
0
 
mark_harris231Commented:
Good point on the volatility, Kris.  Thanks for the reminder.

Mark
0
 
RayneAuthor Commented:
First of ALL,

Thank you to ALL of you for assisting me :)

I think its a good idea to just use a dynamic formula in this case that no matter if the range grows it grows with it

Kris - Can you explain whats going on here?
=IFERROR(COUNTIF(INDEX(E7:INDEX(J7:J1048576,MATCH("zzzzz",D7:D1048576)),E2,),"Yes"),0)

Thank you
0
 
mark_harris231Commented:
Let's tear it apart from inside out...

MATCH("zzzzz",D7:D1048576)  :  Beginning in Cell D7, look for a value of "zzzzz" through Cell D1048576. By default and because the 3rd parameter 'match type' wasn't specified, MATCH will try to find this value or lower.  In your example, any text value >= "zzzzz" will be evaluated as TRUE.  It would match each value in column D as it proceeds down.  It stop on cell D9 because this is the last value that evaluates to TRUE.  This is the 3rd row that matches in the range of D7:D1048576.  Consequently, the value "3" is passed to the 2nd INDEX function (discussed next). 1048576 is the maximum number of rows that Excel supports in 2007/2010 versions.

INDEX(J7:J1048576,3) : This is used to generate a cell reference for the top end of the range reference in the 1st INDEX function.  It will pass the value of "$J$9" to the 1st INDEX function.

INDEX(E7:$J$9,E2,)  : This is used to pass a range reference to the COUNTIF function.  The E2 value tells the INDEX function to only look at the 2nd row of the range E7:J9.  It evaluates to a range reference of $E$8:$J$8 (the 2nd row in the range).

COUNTIF($E$8:$J$8,"Yes")  :  This one is relatively straightforward.  Look in the range specified and count the cells that contain the value "Yes".  The result is "1".

The IFERROR function is included to keep from returning an error if the rest of the calculation fails for some reason.

The INDEX functions are a little tricky in that the results depend on how they're used.  If you just entered =INDEX(J7:J1048576,3) in a cell, you'll get a result of "0" instead of the cell reference.  When it's embedded in the other function, it returns the desired reference cell value.

Hope that helps...

Mark
0
 
RayneAuthor Commented:
Perfect Mark :)

That makes it crystal clear. I will redistribute the points...
0
 
mark_harris231Commented:
No need for point redistribution, but thanks!   Kris did the heaviest lifting on the solution.
0
 
RayneAuthor Commented:
Allright then,

Thank you for clarifying :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.