+ Reply to Thread
Results 1 to 5 of 5

Public VBA Function that returns the occurrences of a search value in a column or row

  1. #1
    Registered User
    Join Date
    05-16-2021
    Location
    Lebanon
    MS-Off Ver
    Office 360
    Posts
    2

    Public VBA Function that returns the occurrences of a search value in a column or row

    Please need your urgent assistance!

    Kindly find below the code that I have written in VBA to return the occurrences of a certain search variable within a column array or row array..

    ===========================================================================

    Public Function CheckOccurrences(MyRange As Range, SearchValue As Variant) As Integer()

    Dim iii As Integer, Counter As Integer, ResultList As Range

    Counter = 1

    For iii = 1 To WorksheetFunction.CountA(MyRange)

    If MyRange.Cells(iii, 1) = SearchValue Then

    ResultList(Counter) = iii

    Counter = Counter + 1

    End If

    Next iii

    CheckOccurrences = ResultList

    End Function

    ===================================================================

    When applying into Excel cell, it is returning an error #Value!


    Could you please advise?!

    Thank you!
    Attached Files Attached Files
    Last edited by Antonio777; 05-16-2021 at 04:42 PM. Reason: Example File Attached

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Public VBA Function that returns the occurrences of a search value in a column or row

    Hi, and welcome to the forum.

    Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

    Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

    To upload a file click the Go Advanced button at the foot of your post, look underneath the post area for the Manage Attachments section and take it from there.

    That said your UDF appears to be simply counting stuff using Excel's COUNTA() function so it's not clear why you want to reinvent the wheel and write your own VBA function which merely includes a standard Excel function.

    Please clarify what you are trying to achieve.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    05-16-2021
    Location
    Lebanon
    MS-Off Ver
    Office 360
    Posts
    2

    Re: Public VBA Function that returns the occurrences of a search value in a column or row

    Hello Richard,

    Thank you so much for your reply..

    In fact, I am trying to learn VBA .. it is almost my 3rd day in watching tutorials about this topic..

    On the other hand, the point that I am raising is been solved through a subroutine on VBA ; However, I wanted to create a function that can be easily used in excel for the same purpose..

    The function that am trying to write is intended to imitate the (Vlookup + Offset) in order to show all the occurrences of a certain variable (value to find) within an array..

    Hope I can find a support from your end..

    Bundle of Thanks!

  4. #4
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Public VBA Function that returns the occurrences of a search value in a column or row

    You can just use a formula...
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Good Luck
    I don't presume to know what I am doing, however, just like you, I too started somewhere...
    One-day, One-problem at a time!!!
    If you feel I have helped, please click on the star to left of post [Add Reputation]
    Also....add a comment if you like!!!!
    And remember...Mark Thread as Solved.
    Excel Forum Rocks!!!

  5. #5
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Public VBA Function that returns the occurrences of a search value in a column or row

    Antonio777,
    1) Amend like this
    Please Login or Register  to view this content.
    2) this is equivalent to your function
    Please Login or Register  to view this content.
    3) built in formula
    =FILTER(ROW(A1:A8),A1:A8="d")
    Last edited by jindon; 05-17-2021 at 07:22 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Counting occurrences if less than a function for a updating column
    By acwood in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-12-2021, 06:06 PM
  2. [SOLVED] Search function returns #VALUE! error
    By mikehk in forum Excel General
    Replies: 9
    Last Post: 07-20-2020, 07:54 PM
  3. [SOLVED] INDEX MATCH SMALL likely function to find nth occurrences in a column
    By ollyhughes1982 in forum Excel General
    Replies: 2
    Last Post: 04-11-2020, 10:25 AM
  4. [SOLVED] Search returns column header or error if value not found
    By leovfx in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 04-30-2017, 04:25 PM
  5. Replies: 7
    Last Post: 04-01-2016, 03:27 PM
  6. Search function, that also returns near values
    By aaron_burr in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-19-2014, 05:34 AM
  7. Excel search function - That alo returns near value
    By aaron_burr in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-18-2014, 04:42 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1