title: how can i find the nth occurrence of a value in a column
body: <p>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.</p>
  
  <p>I copied this from a VBA example on the net, and after the explanation of the function it said:</p>
  
  <blockquote>
    <p>The custom function/formula can now be used as shown below:<br>
    "=Nth_Occurrence($B$1:$B$22,"Harry",3,0,1)" (ignore the "speach marks")</p>
  </blockquote>
  
  <p>It does not explain what in the code you have to have before the <code>=Nth_Occurrence</code> etc. Should this be a variable (variant or string)? or something else.</p>
  
  <p>It also does not explain how to define "Range_Look" so I'm guessing at: <code>set Range_Look = range($K$4:$K$41)</code> (my range as opposed to the example).</p>
  
  <p>And finally, if I change the <code>Set Range_Look = Range("K4:K34")</code> to <code>Range_Look = ActiveSheet.Range("K4:K31")</code>, I get "Run time error 91: Object variable or With block variable not set", or do I need both?</p>
  
  <p>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.</p>
  
  <p>Please someone (Sorceri?), put me out of my misery.</p>
  
  <pre class="lang-vb prettyprint-override"><code>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" &amp; Short_Check_Year) &amp; ".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
  </code></pre>