Code Skipping

[Teeks2k]

Hello,

I have this piece of code that I am using to format a worksheet to later be used as a comparison template for another worksheet.

Sub Comparison_Formatting()

Dim LR As Long, _
    i As Long

LR = Sheets(1).Range("A" & Rows.Count).End(xlUp).Row

With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With

 
'// FN file formatting

With Sheets(2)

    .Columns(68).Cut
    .Columns(3).Insert
    
    With Columns("C:D")
        .Copy
        .PasteSpecial xlPasteValues
        .NumberFormat = "0"
        .ColumnWidth = 15
    End With
    
    For i = 1 To LR
        If Range("BR" & i + 1) = "Single-source product" Then
            Range("BR" & i + 1) = "N"
        ElseIf Range("BR" & i + 1) = "Multi-source product" Then
            Range("BR" & i + 1) = "Y"
        ElseIf Range("BR" & i + 1) = "Multi-source, originator product" Then
            Range("BR" & i + 1) = "O"
        ElseIf Range("BR" & i + 1) = "Single-source, colicensed product" Then
            Range("BR" & i + 1) = "M"
        End If
        
        Range("D" & i + 1).Value = Range("D" & i + 1) & " - " & Range("BR" & i + 1)
        
    Next i
    
End With

'// Comparison Formatting

With Sheets(1)
    .Columns("B:C").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
        .Cells(1, 2).Value = "FN NDC"
        .Cells(1, 2).ColumnWidth = 15
        .Cells(1, 3).Value = "NDC Match"
        .Cells(1, 3).ColumnWidth = 15
    .Columns("E:G").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
        .Cells(1, 5).Value = "GPI - MSC"
        .Cells(1, 5).ColumnWidth = 20
        .Cells(1, 6).Value = "FN GPI-MSC"
        .Cells(1, 6).ColumnWidth = 20
        .Cells(1, 7).Value = "GPI Match"
        .Cells(1, 7).ColumnWidth = 20

'// O,P,S,R to RX/OTC

    .Columns("N").Replace _
        What:="O", Replacement:="OTC", _
        SearchOrder:=xlByColumns, MatchCase:=True
    .Columns("N").Replace _
        What:="P", Replacement:="OTC", _
        SearchOrder:=xlByColumns, MatchCase:=True
    .Columns("N").Replace _
        What:="R", Replacement:="RX", _
        SearchOrder:=xlByColumns, MatchCase:=True
    .Columns("N").Replace _
        What:="S", Replacement:="RX", _
        SearchOrder:=xlByColumns, MatchCase:=True
    .Range("N1") = "Rx Indicator"
    .Columns("S:T").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
        .Cells(1, 19).Value = "FN PA"
        .Cells(1, 20).Value = "PA Match"
        .Cells(1, 20).ColumnWidth = 20
    .Columns("V:W").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
        .Cells(1, 22).Value = "FN ST"
        .Cells(1, 23).Value = "ST Match"
        .Cells(1, 23).ColumnWidth = 20
    .Columns("Y:Z").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
        .Cells(1, 25).Value = "FN AL"
        .Cells(1, 26).Value = "AL Match"
        .Cells(1, 26).ColumnWidth = 20
    .Columns("AC:AD").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
        .Cells(1, 29).Value = "FN QL"
        .Cells(1, 30).Value = "QL Match"
        .Cells(1, 30).ColumnWidth = 20
 
    With Range("A1:BZ1")
        .Interior.Color = RGB(51, 153, 255)
        .Font.Color = RGB(0, 0, 0)
        .Cells(1, 2).Interior.Color = RGB(255, 192, 0)
        .Cells(1, 5).Interior.Color = RGB(255, 192, 0)
        .Cells(1, 19).Interior.Color = RGB(255, 192, 0)
        .Cells(1, 22).Interior.Color = RGB(255, 192, 0)
        .Cells(1, 25).Interior.Color = RGB(255, 192, 0)
        .Cells(1, 29).Interior.Color = RGB(255, 192, 0)
        .Cells(1, 3).Interior.Color = RGB(218, 245, 250)
        .Cells(1, 6).Interior.Color = RGB(255, 192, 0)
        .Cells(1, 20).Interior.Color = RGB(218, 245, 250)
        .Cells(1, 23).Interior.Color = RGB(218, 245, 250)
        .Cells(1, 26).Interior.Color = RGB(218, 245, 250)
        .Cells(1, 30).Interior.Color = RGB(218, 245, 250)
        .Cells(1, 7).Interior.Color = RGB(218, 245, 250)
    End With
    With Cells(1, 79)
        .Value = "On EXDS List"
        .Interior.Color = RGB(255, 255, 0)
        .ColumnWidth = 15
    End With
    With Cells(1, 81)
        .Value = "Pass/Fail"
        .Interior.Color = RGB(0, 153, 0)
        .ColumnWidth = 20
    End With
    With Cells(1, 82)
        .Value = "FC Notes"
        .Interior.Color = RGB(255, 255, 102)
        .ColumnWidth = 20
    End With
    With Cells(1, 83)
        .Value = "RPh Review Comments"
        .Interior.Color = RGB(204, 102, 0)
        .ColumnWidth = 30
    End With
    With Cells(1, 84)
        .Value = "Add to CVS Tracker (Yes/No)"
        .Interior.Color = RGB(224, 224, 224)
        .Font.Color = RGB(0, 102, 204)
        .ColumnWidth = 15
    End With
    With Cells(1, 85)
        .Value = "Extract Changes PA-ST-AL-QL"
        .Interior.Color = RGB(224, 224, 224)
        .Font.Color = RGB(255, 128, 0)
    End With
    With Cells(1, 86)
        .Value = "RPh Sign-off"
        .Interior.Color = RGB(255, 51, 153)
        .ColumnWidth = 15
    End With
    
    With Cells(1, 80)
        .Value = "On Vaccine List"
        .Interior.Color = RGB(255, 255, 0)
        .ColumnWidth = 15
    End With
    Columns("O:Q").Group
    Columns("AE:AK").Group
    Columns("AO:BZ").Group
    .Outline.ShowLevels RowLevels:=1, ColumnLevels:=1
        With Range("A1:CH1").Borders(xlEdgeBottom)
            .LineStyle = x1Continuous
            .Weight = xlThick
            .ColorIndex = 0
            Range("A1:CH1").AutoFilter
        End With
        
    For i = 1 To LR
        
        Range("E" & i + 1).Value = Range("D" & i + 1) & " - " & Range("M" & i + 1)
        
    Next i
End With
With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
End With
        
End Sub

Everything is working fine except the snippet of code highlighted red, and VBA for some reason skips this piece of code. When I run the code that I've assigned for Sheets(2) only, it works fine...but when I run the whole thing, it skips this.

Why does this happen? Is there something I can do to the code to force it to run here?

Thanks

 

[mrhstn]

Did you want to run this only on Sheet(2)? If so you need to precede the ranges with a full stop/period .

Example...

 With .Columns("C:D")
        .Copy
        .PasteSpecial xlPasteValues
        .NumberFormat = "0"
        .ColumnWidth = 15
    End With

 

[Teeks2k]

will I need to do that with the IF statement too?

 

[Norie]

Wherever you have Cells/Columns without the preceding . then they will refer to the active sheet rather than the sheet in your With statement.

 

[mrhstn]

Looks like it

Say you wanted to do something, just a single action you'd maybe type

Sheet1.Range("A1").Interior.Color = vbRed

So if you then wanted to do other stuff you need to keep to the same split in methods etc.

With Sheet1
.Range("A1").Value = 1
.Range("B2").Value = 3
End With

Or

With Sheet1.Range("A1")
.Interior.Color = vbYellow
.Value = 22
.Font.Bold = True
End With

If you aren't specific it'll just generically run the code on the current sheet, which may or may not be what you intend.

 

[Teeks2k]

Thanks gentlemen, this fixed my problem!