Excel 2010 run time error 91': Object variable or with block ...

[Sully1440]

Hi All,
I'm getting an error 91 when I try to run this code. I'm missing something simple, but I can't figure out what it is?

Sub FindText2()

Dim rngX As Range

inarr = Range("A1:A50")
For i = 3 To 50
If inarr(i, 1) <> "" Then
Set rngX = Worksheets("PERT CHART (3)").Range("H1:BM150").Find(Cells(i, 1), lookat:=xlPart)
Cells(i, 4) = rngX.Offset(0, 3).Address
End If
Next i

inarr2 = Range("A1:A50")
For i = 3 To 50
If inarr2(i, 1) <> "" Then
Set rngX = Worksheets("PERT CHART (3)").Range("H1:BM150").Find(Cells(i, 5), lookat:=xlPart)
Cells(i, 6) = rngX.Offset(0, 0).Address
End If
Next i

End Sub

[Sully1440]

I tried to name more variables at the start but I'm still getting the same run-time error 91. Anyone out there that can see what I'm doing wrong??

Sub FindText2()

Dim rngX As Range
Dim inarr As Range
Dim i As Integer

Set rngX = Worksheets("PERT CHART (3)").Range("H1:BM150")
Set inarr = Range("A1:A50")

For i = 3 To 50
If inarr(i, 1) <> "" Then
Set rngX = Worksheets("PERT CHART (3)").Range("H1:BM150").find(Cells(i, 1), lookat:=xlPart)
Cells(i, 4) = rngX.Offset(0, 3).Address
End If
Next i

inarr2 = Range("A1:A50")
For i = 3 To 50
If inarr2(i, 1) <> "" Then
Set rngX2 = Worksheets("PERT CHART (3)").Range("H1:BM150").find(Cells(i, 5), lookat:=xlPart)
Cells(i, 6) = rngX.Offset(0, 0).Address
End If
Next i

End Sub

[Paul_Hossler]

Post a small workbook with enough data and the macro(s) that show the issue

[Sully1440]

Hi Paul,
Thanks for helping me. You recently helped me with the CPM file
What I'm trying to do is draw a chart in excel based on information in the first tab called CPM.
I'm trying to connect arrows between nodes that the user sets up (tab called PERT CHART; a flow chart with nodes). So far I have a few macros to do the following:
1.) A macro to add arrows between nodes (based on cell address). I think I got this working...
2.) A macro to list the nodes "From" and "To" cell addresses so that the "Add Arrows" macro works. My plan is to take the addresses from these cells such that the arrows can be applied. (This is where I'm struggling).
Also, the cells have multiple addresses. For example: A06, A07, A08..... so I started writing a "Split" macro to move these to separate rows for everything to work. I'm starting to get overwhelmed with this one but I sure want it to work.

So, when I started to get run-time error 91, I decided to ask for help.
I have splits, addresses, and arrows going on here. I'm getting closer but really need some help.
Thanks,
Jim

File Attached

[Sully1440]

While stripping out sensitive info, I renamed the tab called "PERT CHART (3)" to PERT CHART but didn't fix the code. Use this file attached....

[Paul_Hossler]

A07 doesn't exist in Worksheets("PERT CHART").Range("H1:BM150") so rngX is set to Nothing

Capture.JPG

That causes

Cells(i, 4) = rngX.Offset(0, 3).Address

to fail

This skips that line if rngX is Nothing (which works), but you may need to investigate why A07 is not in H1:BM150

Option Explicit
Sub FindText2()
     
    Dim rngX As Range
    Dim rngX2 As Range
    Dim inarr As Range
    Dim inarr2 As Range
    Dim i As Integer
    
    Set rngX = Worksheets("PERT CHART").Range("H1:BM150")
    Set rngX2 = Worksheets("PERT CHART").Range("H1:BM150")
    Set inarr = Range("A1:A50")
    Set inarr2 = Range("A1:A50")
    
    For i = 3 To 50
        If inarr(i, 1) <> "" Then
            Set rngX = Worksheets("PERT CHART").Range("H1:BM150").find(Cells(i, 1), lookat:=xlPart)
            If Not rngX Is Nothing Then
                Cells(i, 4) = rngX.Offset(0, 3).Address
            End If
        End If
    Next I
        
    inarr2 = Range("A1:A50")
    For i = 3 To 50
    If inarr2(i, 1) <> "" Then
    Set rngX2 = Worksheets("PERT CHART").Range("H1:BM150").find(Cells(i, 5), lookat:=xlPart)
    Cells(i, 6) = rngX.Offset(0, 0).Address
    End If
    Next I
                    
End Sub

[Sully1440]

It works
Thank you

Question: Should I start a new thread for the split problem I'm having or should I continue here and explain my problem with it.

Again thank you..

[Paul_Hossler]

I'd mark this one [Solved] and start a new thread

More visibility that way

[Sully1440]

Ok, thx