Stops at certain row with Error 91

[David Sisson]

OK this one is strange.

Used range is 831 rows.

The following code runs fine until it reaches row 804. When it errors
with Object variable or With Block Variable not set.

The row above (803) has data in it just like 804.

If I use the other method to determine rows, it only reports 803 rows.

Thanks in advance!
David

Dim MyCell As String
Dim ASheet As Worksheet
Dim Counter As Long
Dim TopRow As Range
Dim BotRow As Range
Dim NumOfRows As Long
Dim EleNum As Long
Dim A As Long

Set ASheet = Worksheets("Main Data Sheet")

'Set topCel = ASheet.Range("D6")
'Set bottomCel = ASheet.Range("D65536").End(xlUp)
'Set SourceRange = ASheet.Range(topCel, bottomCel)
'NumOfRows = SourceRange.Rows.Count + 1
NumOfRows = ASheet.UsedRange.Rows.Count
AC_ID 'assign Array to AACData

For A = 6 To NumOfRows
MyCell = Range("D" & CStr(A)).Value
If Left(MyCell, 2) = "TT" Then
EleNum = TTNum_Search_2(MyCell)'Returns element# of array.
If EleNum <> 0 Then
ASheet.Range("D" & CStr(A)).Comment.Text _
(AACData(EleNum)(1))
ASheet.Range("D" & _
CStr(A)).Comment.Shape.TextFrame._
Characters.Font.Size = 16
End If
End If
Next

 

[David Sisson]

Oops, sorry.

It stops on this line or the next line.
ASheet.Range("D" & CStr(A)).Comment.Text _
(AACData(EleNum)(1))

Thanks,

 

[Tom Ogilvy]

Use code like this to see what might be going wrong

Dim cmt as Comment
Dim rng as Range
For A = 6 To NumOfRows
MyCell = Range("D" & CStr(A)).Value
If Left(MyCell, 2) = "TT" Then
EleNum = TTNum_Search_2(MyCell)'Returns element# of array.
If EleNum <> 0 Then
On Error Resume Next
set rng = Nothing
set cmt = Nothing
set rng = Asheet.Range("D" & cStr(A))
if rng is nothing then
msgbox "Problem with range reference"
exit sub
end if
set cmt = rng.Comment
if cmt is nothing then
msgbox rng.Address & " doesn't have a comment"
exit sub
end if
On Error goto 0

' don't enclose AACData in parens

ASheet.Range("D" & CStr(A)).Comment.Text _
AACData(EleNum)(1)
ASheet.Range("D" & _
CStr(A)).Comment.Shape.TextFrame._
Characters.Font.Size = 16
End If
End If
Next

 

[David Sisson]

ARGGGHH!

I failed to mention that this was the first time these cells had
Comments added. Or so I thought.

I assumed (that always gets ya!) that Comment.Text String would SET a
Comment if not there. Nope, ya gotta use the AddComment!

After some rereading in help file, I realized my mistake.

So here's the working code segment.

Thanks, Tom!

Set Cmt = Rng.Comment

If Cmt Is Nothing Then
ASheet.Range("D" & CStr(A)).AddComment.Text
AACData(EleNum)(1)
ASheet.Range("D" &
CStr(A)).Comment.Shape.TextFrame.Characters.Font.Size = 16

Else

ASheet.Range("D" & CStr(A)).Comment.Text AACData(EleNum)(1)
ASheet.Range("D" &
CStr(A)).Comment.Shape.TextFrame.Characters.Font.Size = 16
End If