Object variable or with block not set Err 91

[owlnevada]

I've been struggling with this one for some time trying everything I can
think of to no avail. . . Problems only with the last 12 lines or so
following the 2nd Next i. Am trying to get code to delete the strComment if
it contains only one item, if >1 then it is OK and sets it to the value in
the File Properties Comments box. At the 2nd Next i, the String gives a list
of numbers, like 123, 12345, 12346, 12347 etc. that would go in the box.
If the string contained only the "123" then it would be blank. Should be a
simple fix. Other errors i get when switching things around (mystr,
StrComment) are invalid qualifiers or object not defined. . . .


Public Sub FillFilePropsComments() '(Optional control As IRibbonControl)

Dim ws As Worksheet
Dim Strlist() As String ' list for permit numbers
Dim ptr As Integer ' pointer/ counter for strList
Dim PermitNumber As Variant
Dim DupFound As Boolean
Dim StrComment As String
Dim PropAuthor As String
Dim mystr As DocumentProperty

ptr = 0 ' init pointer - currently there are zero permit numbers in list
ReDim Strlist(1 To Sheets.Count) ' init size of list, should not be more
Permits than sheets

For Each ws In Worksheets
PermitNumber = GetPermitNumber(ws) ' get the permit number
DupFound = False ' assume it is not already in list
For i = ptr To 1 Step -1 ' start from back of list, see if it
is a dup
If PermitNumber = Strlist(i) Then
DupFound = True
Exit For
End If
Next i
If Not DupFound Then ' it is not already in list
ptr = ptr + 1 ' make a place for it in the list by pointing to
next empty slot
Strlist(ptr) = PermitNumber ' store new permit number
End If
Next ws

ReDim Preserve Strlist(1 To ptr) ' resize list to number of permits
QuickSort Strlist, LBound(Strlist), UBound(Strlist) ' sort the list

For i = 1 To ptr ' get the list into one long string
StrComment = StrComment & Strlist(i) & ", "
Next i

mystr = StrComment 'err 91 occurs here

mystr = Replace(mystr, ",", " ")
mystr = Application.Trim(mystr)
mystr = Replace(mystr, " ", ", ")

Dim newStr() As New Collection

' reDim mystr As New Collection ? 'not sure if this is right approach
so '

If mystr = (mystr.Count = 1) Then
StrComment = ""
ElseIf mystr > (1) Then
StrComment = mystr

End If

' store the string in the File>Comments Property box
ActiveWorkbook.BuiltinDocumentProperties("Comments").Value = StrComment

End Sub

 

[Joel]

Declare NewStr as String and use len(newstr). You don't have to redim a string

Dim newStr() As String

Select Case len(mystr)
case 0 'Do nothing
case 1
StrComment = ""
case is > 1
StrComment = mystr
end select

 

[owlnevada]

That did it with a little adjustment for the number of characters per item in
the string, being 1-99999, so I made it as below. I was trying to come up
with code that would count the number of items, but the number of characters
will work as follows below.

Thank you so much!


For i = 1 To ptr ' get the list into one long string
StrComment = StrComment & Strlist(i) & ", "
Next i

mystr = StrComment

mystr = Replace(mystr, ",", " ")
mystr = Application.Trim(mystr)
mystr = Replace(mystr, " ", ", ")

Dim newStr() As String ' not sure it needed this new variable

Select Case Len(mystr)
Case 0 'Do nothing
Case 1 To 5
mystr = ""
Case 6 To 800 > 1
StrComment = mystr
End Select

StrComment = mystr

' store the string in the File>Comments Property box
ActiveWorkbook.BuiltinDocumentProperties("Comments").Value = StrComment

End Sub