No Value found in range go to Next i in Loop

[CdL10]

Hi All,

I have created the following:

Sub CodeChange()
'
' CodeChange Macro
'

  For i = [Run_Start] To [Run_End]
 [Number] = i

 
CodetoChange = [ code ]
NewValueReplace = [NewValue]
Dim ws As Worksheet
Dim RA As Range

        Set RA = Cells.Find(What:=[ code ], After:=ActiveCell, LookIn:=xlFormulas2, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)
     
 
        RA.Activate

 
    
         With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 15773696
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With

    ActiveCell.Offset(0, 13).Select
    ActiveCell.Value = [NewValue]
With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 15773696
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
 
 Next i
 


End Sub

But if the [ code ] value is not found in the range it does not want to do to the next item in the loop.

Kindly assist

EDIT: Moderator added spaces to beginning and end of [ code ] variable, as it was conflicting with code tags used to post the code.

 

[6StringJazzer]

It would help to know what you want your code to do.

This code will raise an error on this line of code if the value is not found.

        RA.Activate

Are you getting an error?

I would do this (with enhanced indentation):

Sub CodeChange()
'
' CodeChange Macro
'

   For i = [Run_Start] To [Run_End]
 
      [Number] = i
   
      CodetoChange = [ code ]
      NewValueReplace = [NewValue]
      Dim ws As Worksheet
      Dim RA As Range
   
      Set RA = Cells.Find(What:=[ code ], _
                          After:=ActiveCell, _
                          LookIn:=xlFormulas2, _
                          LookAt:=xlPart, _
                          SearchOrder:=xlByRows, _
                          SearchDirection:=xlNext, _
                          MatchCase:=False, _
                          SearchFormat:=False)
   
      If Not RA Is Nothing Then
   
         RA.Activate
      
         With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .Color = 15773696
            .TintAndShade = 0
            .PatternTintAndShade = 0
         End With
      
         ActiveCell.Offset(0, 13).Select
         ActiveCell.Value = [NewValue]
      
         With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .Color = 15773696
            .TintAndShade = 0
            .PatternTintAndShade = 0
         End With
   
      End If
 
   Next i

End Sub

 

[6StringJazzer]

EDIT: Moderator added spaces to beginning and end of [ code ] variable, as it was conflicting with code tags used to post the code.

I believe that if you use VBA code tags instead of vanilla CODE tags this problem won't occur. Took me a while to figure it out.

      [Number] = i
   
      CodetoChange = [code]
      NewValueReplace = [NewValue]

 

[DanteAmor]

Hi and welcome to MrExcel.

Try the following code a bit improved:

Sub CodeChange()
  Dim RA As Range
  Dim i As Long
  
  Application.ScreenUpdating = False
  For i = [Run_Start] To [Run_End]
    [Number] = i
    
    Set RA = Cells.Find([ code ], , xlFormulas, xlPart, xlByRows, xlNext, False)
    If Not RA Is Nothing Then
      With Union(RA, RA.Offset(0, 13)).Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 15773696
        .TintAndShade = 0
        .PatternTintAndShade = 0
      End With
      RA.Offset(0, 13).Value = [NewValue]
    End If
  Next i
  Application.ScreenUpdating = True
End Sub

 

[CdL10]

Thank you!