CODEWITHSUNDEEP
excelvba
mikesingleton
mikesingleton

Reputation: 27

how can i find the nth occurrence of a value in a column

I have revamped the VBA yet again. Now when I run it I get nothing. It doesn't "crash", but it doesn't give the expected answer.

I copied this from a VBA example on the net, and after the explanation of the function it said:

The custom function/formula can now be used as shown below:
"=Nth_Occurrence($B$1:$B$22,"Harry",3,0,1)" (ignore the "speach marks")

It does not explain what in the code you have to have before the =Nth_Occurrence etc. Should this be a variable (variant or string)? or something else.

It also does not explain how to define "Range_Look" so I'm guessing at: set Range_Look = range($K$4:$K$41) (my range as opposed to the example).

And finally, if I change the Set Range_Look = Range("K4:K34") to Range_Look = ActiveSheet.Range("K4:K31"), I get "Run time error 91: Object variable or With block variable not set", or do I need both?

I know I'm being a real pain in the proverbial, but it's driving me crazy. I also know this is NOT the only way to achieve my end result, but it IS (I think) quite elegant, or it would be if I can get it to work.

Please someone (Sorceri?), put me out of my misery.

Sub Macro2()
'
' Macro2 Macro
'
' Keyboard Shortcut: Ctrl+q

    Dim bankacctA As String                      'Finds the "Check Year" bankacct
    Dim bankacctB As String                      'This will be the "New" generated year

    Input_Year = InputBox(Prompt:="What is the Year you wish to Add - Enter in format yyyy", _
        Title:="ENTER THE YEAR", Default:="No Year Entered")

    If IsNumeric(Input_Year) = False Then
        Exit Sub
    Else
    End If

    Short_Input_Year = Right(Input_Year, 2)      'The last 2 digits of the Input Year
    Check_Year = Input_Year - 1
    Short_Check_Year = Right(Check_Year, 2)      'The last 2 digits of the Check Year
    Next_Year = (Input_Year + 1)

    bankacctA = ("bankacct" & Short_Check_Year) & ".xlsx"                                          'Finds the "Check Year" bankacct

    Dim Range_Look As Range
    Dim a As Variant

    Windows("Savings Details.xlsm").Activate
    Sheets("Table Sort").Select

    Set Range_Look = Range("K4:K34")    'The worksheet has already been selected

    a = Nth_Occurrence(Range_Look, CStr(bankacctA), 2, 1, -1)

    ActiveCell.Offset(0, -1).Value = a
End Sub

Function Nth_Occurrence(Range_Look As Range, Find_It As String, Occurrence As Long, Offset_Row As Long, Offset_Col As Long)
    Dim lCount As Long
    Dim rFound As Range

    Set rFound = Range_Look.Cells(1, 1)
        For lCount = 1 To Occurrence
           Set rFound = Range_Look.Find(Find_It, rFound, xlValues, xlPart)
        Next lCount
     Nth_Occurrence = rFound.Offset(Offset_Row, Offset_Col)
End Function

Upvotes: 0

Views: 1164

Answers (1)

Sorceri
Sorceri

Reputation: 8033

You need to add the Range parameter to your function signature.

Dim Range_Look As Range
Range_Look = ActiveSheet.Range("K4:K31")                    'The worksheet has already been selected

          ActiveCell.Value = Nth_Occurrence(Range_Look, bankacctA, 2, 1)


'Function: Add the Range as a paramter
Public Function Nth_Occurrence(Range_Look As Range, Find_It As String, Occurrence As Long, Offset_Row As Long)

Dim lCount As Long
Dim rFound As Range

    Set rFound = Range_Look(1)'set it to the first cell in the range


        For lCount = 1 To Occurrence

            Set rFound = Range_Look.Find(Find_It, rFound, xlValues, xlWhole)

        Next lCount

    Nth_Occurrence = rFound.Offset(Offset_Row)

End Function

now when you get the by ref error it is because you are passing in a variant where a string is declared for the parameter bankacctA (at least in the provide sample). You need to declare Dim bankacctA as String or wrap in in a cast CStr(bankacctA)

Upvotes: 1

Related Questions