For Next loop to skip formulas that contain specific string - Runtime Error 424

[marimar02]

Hello,

can you please help me clean up below code. I'm getting a RunTime error 424 when it hits "Set Dpndnt" code

Sub Calculate_Actuals()

Dim i As Integer
Dim rng As Range
Dim Dpndnt As Range
Dim DeptNo As String
    
    For i = 145 To 161


    Set rng = Range("L" & i)
    Set Dpndnt = Left(Range("L" & i), 4)
        
    If Dpndnt.Value = "=Sum" Then
    
    GoTo CellSkip
    
    Else
    
    rng.FormulaR1C1 = "omitted intentionally "
    
'    rng.Value = rng.Value


    End If
    
CellSkip:
    
    Next i
    
End Sub

Thanks much...

 

[JonXL]

You can't set a range variable to a string.

What are you trying to set it to?

 

[marimar02]

Basically, I'm trying to apply a formula to all cells in column L unless it detects "=Sum" formula. Then the For/Next should skip.

 

[JonXL]

Dim Dpndnt as a String. Then set its value using:

 Dpndnt = Left(Range("L" & i).Formula, 4)

 

[marimar02]

That worked great. Here is the code that works:

Sub Calculate_Actuals()

Dim i As Integer
Dim rng As Range
Dim Dpndnt As String
Dim DeptNo As String
    
    For i = 145 To 161


    Set rng = Range("L" & i)
    Dpndnt = Left(Range("L" & i).Formula, 4)
    
    If Dpndnt = "=SUM" Then
    
    GoTo CellSkip
    
    Else
    
    rng.FormulaR1C1 = "omitted intentionally"
    
'    rng.Value = rng.Value


    End If
    
CellSkip:
    
    Next i
    
End Sub

THANK YOU very much...