code works with code window open but not with it closed???

[ajm]

Hi all, I have an interesting issue with a macro that I have written. The macro is the fourth in a sequence of 5. I'm using Microsoft 365 MSO (Version 2208 Build 16.0.15601.20204) 64-bit .

so, this macro is used to copy data from two tabs in the workbook to a front tab. I have bolded the problematic section below which is supposed to insert a formula into any blank cell in the selected range. the problem is that if the code window is not open, this section of the macro does not appear to work. if the code window is open, it works as it should. any ideas?

With Worksheets("Forward Plan")
   
    x = Range("b36").End(xlDown).Row
    
   If x > 1 Then
   On Error Resume Next
        With Range("AF36:AF" & x)

            .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=IF([@[Contract Value/Risk Matrix - Code]]="""",""10%""*1,VLOOKUP([@[Contract Value/Risk Matrix - Code]],ProjectVRM,3,0))"
            .NumberFormat = "0.00%"
        End With
    On Error GoTo 0
    End If
    End With

Sub Forward_Plan()

Dim FPlanNext As Long, x As Long

With Application
    .ScreenUpdating = False ' stop screen flashing as macro runs
    .DisplayAlerts = False ' stop alert messages
    .EnableEvents = False ' disable events running
End With

'check for filter, turn on if none exists
    With Worksheets("Combined")
        If Not .AutoFilterMode Then
            .Range("A33").AutoFilter
        End If
    End With
    
    With Worksheets("Forward Plan")
    
    'clear existing records from FwdPlan tab.
  'can't use delete as that destroys the links on the final tab
  
    .Range("a36:ah" & .Rows.Count).ClearContents
  
        Worksheets("Combined").Range("RngCombined").Copy ' copy used range from Combined
        .Range("A36").PasteSpecial xlPasteValues  ' paste range to A34 (the row below the header row)
    
    FPlanNext = .Range("b35").End(xlDown).Row + 1
    'End With
    
     'copy Other Activities to FPlan.
    'check for filter, turn on if none exists
    With Worksheets("Other Activities")
        If Not .AutoFilterMode Then
            .Range("A3").AutoFilter
        End If
        
        .Range("RngOthActsAD").SpecialCells(xlCellTypeVisible).Copy
        
    End With
    
        .Range("a" & FPlanNext).PasteSpecial xlPasteValues
    
    End With
    
    Worksheets("Other Activities").Range("RngOthActsEF").SpecialCells(xlCellTypeVisible).Copy
    Worksheets("Forward Plan").Range("a" & FPlanNext).Offset(0, 13).PasteSpecial xlPasteValues
    
     Worksheets("Other Activities").Range("RngOthActsGH").SpecialCells(xlCellTypeVisible).Copy
    Worksheets("Forward Plan").Range("a" & FPlanNext).Offset(0, 16).PasteSpecial xlPasteValues
    
    Worksheets("Other Activities").Range("RngOthActsIJ").SpecialCells(xlCellTypeVisible).Copy
    Worksheets("Forward Plan").Range("a" & FPlanNext).Offset(0, 26).PasteSpecial xlPasteValues
    
    Worksheets("Other Activities").Range("RngOthActsK").SpecialCells(xlCellTypeVisible).Copy
    Worksheets("Forward Plan").Range("a" & FPlanNext).Offset(0, 29).PasteSpecial xlPasteValues
    
    Worksheets("Other Activities").Range("RngOthActsLM").SpecialCells(xlCellTypeVisible).Copy
    Worksheets("Forward Plan").Range("a" & FPlanNext).Offset(0, 22).PasteSpecial xlPasteValues
    
    Worksheets("Other Activities").Range("RngOthActsNO").SpecialCells(xlCellTypeVisible).Copy
    Worksheets("Forward Plan").Range("a" & FPlanNext).Offset(0, 32).PasteSpecial xlPasteValues
   
   With Worksheets("Forward Plan")
   
    x = Range("b36").End(xlDown).Row
    
   If x > 1 Then
   On Error Resume Next
        With Range("AF36:AF" & x)

            .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=IF([@[Contract Value/Risk Matrix - Code]]="""",""10%""*1,VLOOKUP([@[Contract Value/Risk Matrix - Code]],ProjectVRM,3,0))"
            .NumberFormat = "0.00%"
        End With
    On Error GoTo 0
    End If
    End With
    
With Application
    .Run "timeStamp"
    'turn each back on
    .ScreenUpdating = True
    .DisplayAlerts = True
    .EnableEvents = True
    .Goto Reference:=Sheets("Forward Plan").Range("a1"), Scroll:=True
End With



End Sub

 

[Peter_SSs]

I have bolded the problematic section below

For the future, if you want to do that, use the RICH code tags, not the VBA tags.

 

[ajm]

thank you Peter.

With Worksheets("Forward Plan")
   
    x = Range("b36").End(xlDown).Row
    
   If x > 1 Then
   On Error Resume Next
        With Range("AF36:AF" & x)

            .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=IF([@[Contract Value/Risk Matrix - Code]]="""",""10%""*1,VLOOKUP([@[Contract Value/Risk Matrix - Code]],ProjectVRM,3,0))"
            .NumberFormat = "0.00%"
        End With
    On Error GoTo 0
    End If
    End With

 

[Alex Blakenburg]

Try putting the “period” ( . ) before .Range("AF36:AF" & x)
And also in
x = .Range("b36").End(xlDown).Row

 

[ajm]

Try putting the “period” ( . ) before .Range("AF36:AF" & x)
And also in
x = .Range("b36").End(xlDown).Row

Thank you Alex.

 

[Alex Blakenburg]

You're welcome. Glad I could help.