[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 564
  • Last Modified:

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
0
Rayne
Asked:
Rayne
  • 4
  • 3
  • 2
  • +1
3 Solutions
 
Swapnil NirmalCommented:
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
 
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now