Looking for help with Intersect VBA

[CrashDDL]

Hi,

I have this code that I want to use to set every 5th row bottom border medium with. I managed to use it on its own in a test file but when I insert this into my main document, I keep getting: "Run-time error '91: Object variable or With block variable not set"

The first line works and will make the 6th row bottom edge medium at the proper ranges, which in turn means that tableRng is not the problem;
The 2nd row however gives the run-time error, suggesting that (i) is someone responsible. I also tried changing (i) to other letters not used anywhere in the VBA with no luck.
Anyone have any ideas why this might be?

    Dim i As Long
    
    For i = 2 To 47 Step 5


        'Intersect(Rows(6), tableRng).Borders(xlEdgeBottom).Weight = xlMedium
        Intersect(Rows(i), tableRng).Borders(xlEdgeBottom).Weight = xlMedium
        
    Next i

Thank you for looking!

 

[Kenneth Hobson]

Obviously, row 2 does not have any cell that is withing tableRng. When using Intersect() it is best to Set it to a range variable and then check if Not Nothing. I can show you how if you really need that.

 

[MickG]

This worked for me:-

Dim i As Long
  Dim tablerng As Range
   Set tablerng = Range("A1:c47") [B][COLOR=#008000]' Perhaps some problem with the range size and loop size 47[/COLOR][/B]
    For i = 2 To 47 Step 5
         Intersect(Rows(i), tablerng).Borders(xlEdgeBottom).Weight = xlMedium

 

[CrashDDL]

Obviously, row 2 does not have any cell that is withing tableRng. When using Intersect() it is best to Set it to a range variable and then check if Not Nothing. I can show you how if you really need that.

Of course it doesn't... I can't believe I missed that :D

Thank you for the help!

 

[Kenneth Hobson]

Sounds like you got it now. For others that might not, here I skipped row 2 on purpose...

Sub Main()
  Dim i As Long, tableRng, r As Range
    Set tableRng = [A3:A100]
    For i = 2 To 47 Step 5
      Set r = Intersect(Rows(i), tableRng)
      If Not r Is Nothing Then r.Borders(xlEdgeBottom).Weight = xlMedium
    Next i
End Sub

 

[mikerickson]

How about something like

With tableRange
    For i = 1 to .Rows.Count Step 5
        .Rows(i).Borders(xlEdgeBottom).Weight = xlMedium
    Next i
End With