Range & _Global VBA Issue

[cubsfan05]

So this is probably me missing something stupid, but I'm trying to change the formula in a handful of cells where someone else messed up the formula. I've identified the range of cells that are messed up below.
In the first subroutine I tried to do this using select but was getting an error "method range of object _Global failed"
In the second subroutine I tried to do this using the cells defined as a range but get an error "application-defined or object-defined error"
I don't really care which of the below would work but can't figure out what the issue is with the Range("...")
Thanks in advance for any help!

Sub ReplaceFormulaForSubjectSecurity()

    ' select the cells that need a different formula
    Sheets("Database").Select
    Range("D158:D161,D164,D168:D175,D177:D180,D221:D224,D229:D232,D235,D239:D246,D248:D251,D292:D295,D300:D303,D306,D310:D317,D319:D322,D363:D366,D371:D374,D377,D381:D388,D390:D393,D434:D437,D442:D445,D448,D452:D459,D461:D464,D505:D508,D513:D516,D519,D523:D530,D532:D535,D576:D579,D584:D587,D590,D594:D601,D603:D606,D647:D650,D655:D658,D661,D665:D672,D674:D677,D718:D721,D1342:D1373,D1382:D1389,D1638:D1677,D1989,D1997,D2005,D2013,D2021,D2029,D2037,D2045").Select
    
    'change the formula to match columns based on debt number
    Selection.Formula2R1C1 = _
        "=INDEX('DB Securities'!R5C3:R174C11,MATCH(RIGHT(RC3,LEN(RC3)-4),'DB Securities'!R5C3:R174C3,0),LEFT(RC[-1],1)+1)"
    
    'to help track, set cell color to some light green color
    With Selection.Interior
        .Color = 13434828
    End With

End Sub

Sub SwapToSubjectSecurity()

    'identify the cells that need a different formula
    Dim cellRange As Range
    Set cellRange = ActiveWorkbook.Sheets("Database").Range("D158:D161,D164,D168:D175,D177:D180,D221:D224,D229:D232,D235,D239:D246,D248:D251,D292:D295,D300:D303,D306,D310:D317,D319:D322,D363:D366,D371:D374,D377,D381:D388,D390:D393,D434:D437,D442:D445,D448,D452:D459,D461:D464,D505:D508,D513:D516,D519,D523:D530,D532:D535,D576:D579,D584:D587,D590,D594:D601,D603:D606,D647:D650,D655:D658,D661,D665:D672,D674:D677,D718:D721,D1342:D1373,D1382:D1389,D1638:D1677,D1989,D1997,D2005,D2013,D2021,D2029,D2037,D2045")

    'change the formula to match columns based on debt number & set cell color to light green
    With Selection.Interior
        .Color = 13434828
        .Formula2R1C1 = "=INDEX('DB Securities'!R5C3:R174C11,MATCH(RIGHT(RC[-1],LEN(RC[-1])-4),'DB Securities'!R5C3:R174C3,0),LEFT(RC[-1],1)+1)"
    End With

End Sub

 

[Akuini]

A range address can't have more than 255 character.
Try something like this:

Sub SwapToSubjectSecurity1()

    'identify the cells that need a different formula
    Dim cellRange As Range
    ary = Split("D158:D161,D164,D168:D175,D177:D180,D221:D224,D229:D232,D235,D239:D246,D248:D251,D292:D295,D300:D303,D306,D310:D317,D319:D322,D363:D366,D371:D374,D377,D381:D388,D390:D393,D434:D437,D442:D445,D448,D452:D459,D461:D464,D505:D508,D513:D516,D519,D523:D530,D532:D535,D576:D579,D584:D587,D590,D594:D601,D603:D606,D647:D650,D655:D658,D661,D665:D672,D674:D677,D718:D721,D1342:D1373,D1382:D1389,D1638:D1677,D1989,D1997,D2005,D2013,D2021,D2029,D2037,D2045", ",")
    With ActiveWorkbook.Sheets("Database")
        Set cellRange = .Range(ary(0))
        For i = 1 To UBound(ary)
            Set cellRange = Union(cellRange, .Range(ary(i)))
        Next
    End With
    'change the formula to match columns based on debt number & set cell color to light green
    With cellRange.Interior
        .Color = 13434828
        .Formula2R1C1 = "=INDEX('DB Securities'!R5C3:R174C11,MATCH(RIGHT(RC[-1],LEN(RC[-1])-4),'DB Securities'!R5C3:R174C3,0),LEFT(RC[-1],1)+1)"
    End With

End Sub

 

[DanteAmor]

Checking your cells I found that you use the following ranges:

  s1 = "D158:D161,D164,D168:D175,D177:D180"
  
  s2 = "D221:D224,D229:D232,D235,D239:D246,D248:D251"
  s3 = "D292:D295,D300:D303,D306,D310:D317,D319:D322"
  s4 = "D363:D366,D371:D374,D377,D381:D388,D390:D393"
  s5 = "D434:D437,D442:D445,D448,D452:D459,D461:D464"
  s6 = "D505:D508,D513:D516,D519,D523:D530,D532:D535"
  s7 = "D576:D579,D584:D587,D590,D594:D601,D603:D606"
  s8 = "D647:D650,D655:D658,D661,D665:D672,D674:D677"
  
  s9 = "D718:D721,D1342:D1373,D1382:D1389,D1638:D1677"
  sa = "D1989,D1997,D2005,D2013,D2021,D2029,D2037,D2045"

And I found that the ranges from s2 to s8 use the same pattern:

Then we could use that so you don't have to write such a long string of cell ranges.

Sub ReplaceFormulaForSubjectSecurity()
  Dim s1 As String, s2 As String, s9 As String, sa As String
  Dim i As Long, n As Long
  Dim sh As Worksheet
  
  Set sh = Sheets("Database")
  s1 = "D158:D161,D164,D168:D175,D177:D180"
  s9 = "D718:D721,D1342:D1373,D1382:D1389,D1638:D1677"
  sa = "D1989,D1997,D2005,D2013,D2021,D2029,D2037,D2045"
  Call PutFormula(sh.Range(s1))
  Call PutFormula(sh.Range(s9))
  Call PutFormula(sh.Range(sa))
  
  s2 = "D221:D224,D229:D232,D235,D239:D246,D248:D251"
  For i = 2 To 8
    Call PutFormula(sh.Range(s2).Offset(n, 0))
    n = n + 71
  Next
End Sub

Sub PutFormula(rng As Range)
  rng.Interior.Color = 13434828
  rng.FormulaR1C1 = "=INDEX('DB Securities'!R5C3:R174C11,MATCH(RIGHT(RC[-1],LEN(RC[-1])-4),'DB Securities'!R5C3:R174C3,0),LEFT(RC[-1],1)+1)"
End Sub

 

[DanteAmor]

Knowing the ranges, then the code can be simplified as follows. I even found the formula to be simpler if you use VLOOKUP.

Sub ReplaceFormulaForSubjectSecurity()
  Dim s1 As Variant
  Dim i As Long, n As Long
  Dim sh As Worksheet
  
  Set sh = Sheets("Database")
  For Each s1 In Array("D158:D161,D164,D168:D175,D177:D180", _
                       "D718:D721,D1342:D1373,D1382:D1389,D1638:D1677", _
                       "D1989,D1997,D2005,D2013,D2021,D2029,D2037,D2045")
    Call PutFormula(sh.Range(s1))
  Next
  For i = 2 To 8
    Call PutFormula(sh.Range("D221:D224,D229:D232,D235,D239:D246,D248:D251").Offset(n, 0))
    n = n + 71
  Next
End Sub

Sub PutFormula(rng As Range)
  rng.Interior.Color = 13434828
  rng.FormulaR1C1 = "=VLOOKUP(RIGHT(RC[-1],LEN(RC[-1])-4),'DB Securities'!R5C3:R174C11,LEFT(RC[-1])+1,0)"
End Sub

 

[cubsfan05]

A range address can't have more than 255 character.
Try something like this:

Sub SwapToSubjectSecurity1()

    'identify the cells that need a different formula
    Dim cellRange As Range
    ary = Split("D158:D161,D164,D168:D175,D177:D180,D221:D224,D229:D232,D235,D239:D246,D248:D251,D292:D295,D300:D303,D306,D310:D317,D319:D322,D363:D366,D371:D374,D377,D381:D388,D390:D393,D434:D437,D442:D445,D448,D452:D459,D461:D464,D505:D508,D513:D516,D519,D523:D530,D532:D535,D576:D579,D584:D587,D590,D594:D601,D603:D606,D647:D650,D655:D658,D661,D665:D672,D674:D677,D718:D721,D1342:D1373,D1382:D1389,D1638:D1677,D1989,D1997,D2005,D2013,D2021,D2029,D2037,D2045", ",")
    With ActiveWorkbook.Sheets("Database")
        Set cellRange = .Range(ary(0))
        For i = 1 To UBound(ary)
            Set cellRange = Union(cellRange, .Range(ary(i)))
        Next
    End With
    'change the formula to match columns based on debt number & set cell color to light green
    With cellRange.Interior
        .Color = 13434828
        .Formula2R1C1 = "=INDEX('DB Securities'!R5C3:R174C11,MATCH(RIGHT(RC[-1],LEN(RC[-1])-4),'DB Securities'!R5C3:R174C3,0),LEFT(RC[-1],1)+1)"
    End With

End Sub

Thanks, i tweaked the with at the bottom ever so slightly due to syntax but it works great.
If I have option explicit on, what would you dim ary as?

 

[Akuini]

f I have option explicit on, what would you dim ary as?

Dim ary As Variant