none
Object variable or with block not set. Code errors out. RRS feed

  • Question

  • I'm getting the following error.

    Here's the script that

    Function NthInstance(F As String, R As Range, N As Long) As String Dim vA As Variant vA = R.Value For i = LBound(vA, 1) To UBound(vA, 1) If InStr(1, vA(i, 1), F) > 0 Then ct = ct + 1 If ct = N Then NthInstance = R.Cells(i, 1).Address(1, 1) Exit Function End If End If Next i If ct < N Then NthInstance = "Only " & ct & " instances found" End Function

    I'm calling it from a Sub that loops through different sheets and performs some operations on each sheet.  I tried With . . .End With, but I keep getting that error.

    I am caling the Function, from the Sub, like this.

    therange = NthInstance("Grand Total", ActiveSheet.Range("A:A"), 2)

    The code came form this URL.

    http://www.mrexcel.com/forum/excel-questions/678792-find-nth-occurrence-column.html


    MY BOOK

    Tuesday, August 30, 2016 11:35 PM

Answers

  • Hi ryguy72,

    According to your description, I have made a sample and try to reproduce this issue, unfortunately, I am not able. Could you help us figure out what "therange" is? How you declare "therange"? Based on "NthInstance" functio, it return string value, so "therange" need to declare as string.

    For more information, click here to refer to Object variable or With block variable not set (Error 91)

    • Marked as answer by ryguy72 Wednesday, August 31, 2016 1:45 PM
    Wednesday, August 31, 2016 3:20 AM

All replies

  • Hi ryguy72,

    According to your description, I have made a sample and try to reproduce this issue, unfortunately, I am not able. Could you help us figure out what "therange" is? How you declare "therange"? Based on "NthInstance" functio, it return string value, so "therange" need to declare as string.

    For more information, click here to refer to Object variable or With block variable not set (Error 91)

    • Marked as answer by ryguy72 Wednesday, August 31, 2016 1:45 PM
    Wednesday, August 31, 2016 3:20 AM
  • Oh, you are right! I thought it was a Range, but it's actually a String!!

    Thanks!!


    MY BOOK

    Wednesday, August 31, 2016 1:45 PM