Easier Way to Write Nested IFs and Iterating Offsets

VBE313

VBE313

Well-known Member
Joined
Mar 22, 2019
Messages
686
Office Version
  1. 365
Platform
  1. Windows
What is a less rigorous way or writing this?

Code: 
Sub COSTPLANBREAKDOWN()
    Dim rngstart As Range
    Dim tst As Range, lastrow As Long
    Dim cl As Object
    Dim L As Long, strcells As String, lsr As String, toplvl As String, comm As String, flat As String
    Dim CLT As String, PLT As String, CLST As String, pmark As String, pbrk As String, dmnsl As String, final As String
    Dim Subcon1 As String, Subcon2 As String, Subcon3 As String, Subcon4 As String, Subcon5 As String
    Dim SubQty1 As String, SubQty2 As String, SubQty3 As String, SubQty4 As String, SubQty5 As String
    Dim SubComm1 As String, SubComm2 As String, SubComm3 As String, SubComm4 As String, SubComm5 As String
    Dim SubPart1 As String, SubPart2 As String, SubPart3 As String, SubPart4 As String, SubPart5 As String
    Application.ScreenUpdating = False
    Sheets("Export").Range("H6").Select
    lsr = "LSR_01"
    flat = "FLAT_I"
    pmark = "P_MARK"
    pbrk = "P_BRK1"
    dmnsl = "DIM_1"
    final = "FIN_I"
    Set rngstart = ActiveCell
    For Each cl In Sheets("Export").Range("H:H")
        If ActiveCell.Value = "" Then
            Sheets("Planning").Select
            Application.ScreenUpdating = True
        Exit Sub
        Else
            If ActiveCell.Value = "TOP LEVEL" And Subcon1 <> "" Or Subcon2 <> "" Or Subcon3 <> "" Or Subcon4 <> "" Or Subcon5 <> "" Then
                toplvl = ActiveCell.Offset(0, -2).Value
                Sheets("Planning").Select
                lastrow = Cells(Rows.Count, 5).End(xlUp).Row
                Cells(lastrow, 5).Offset(1, 0).Select
                If Subcon1 <> "" Then
                    ActiveCell.Value = SubPart1
                    ActiveCell.Offset(0, 1).Value = 9400
                    ActiveCell.Offset(0, 2).Value = "RWRK"
                    ActiveCell.Offset(0, 3).Value = SubComm1
                    ActiveCell.Offset(0, 4).Value = "0200"
                    ActiveCell.Offset(0, 5).Value = "0.01"
                    ActiveCell.Offset(0, 6).Value = "MN"
                    ActiveCell.Offset(1, 0).Value = SubPart1
                    ActiveCell.Offset(1, 1).Value = 9500
                    ActiveCell.Offset(1, 2).Value = "SUBBFF"
                    ActiveCell.Offset(1, 3).Value = SubComm1
                    ActiveCell.Offset(1, 4).Value = "0200"
                    ActiveCell.Offset(1, 5).Value = ".01"
                    ActiveCell.Offset(1, 6).Value = "MN"
                    ActiveCell.Offset(2, 0).Value = SubPart1
                    ActiveCell.Offset(2, 1).Value = 10
                    ActiveCell.Offset(2, 2).Value = "SUBCON"
                    ActiveCell.Offset(2, 3).Value = SubComm1
                    ActiveCell.Offset(2, 4).Value = 1300
                    ActiveCell.Offset(2, 5).Value = "COST TO PROCESS"
                    ActiveCell.Offset(2, 6).Value = "MN"
                    ActiveCell.Offset(3, 0).Value = SubPart1
                    ActiveCell.Offset(3, 1).Value = 10
                    ActiveCell.Offset(3, 2).Value = "SUBCON"
                    ActiveCell.Offset(3, 3).Value = SubComm1
                    ActiveCell.Offset(3, 4).Value = "0200"
                    ActiveCell.Offset(3, 5).Value = "10"
                    ActiveCell.Offset(3, 6).Value = "DY"
                    ActiveCell.Offset(4, 0).Value = SubPart1
                    ActiveCell.Offset(4, 1).Value = 20
                    ActiveCell.Offset(4, 2).Value = "SHPBFF"
                    ActiveCell.Offset(4, 3).Value = SubComm1
                    ActiveCell.Offset(4, 4).Value = "0300"
                    ActiveCell.Offset(4, 5).Value = "5"
                    ActiveCell.Offset(4, 6).Value = "DY"
                    Subcon1 = ""
                    SubQty1 = ""
                    SubComm1 = ""
                    SubPart1 = ""
                Else
                End If
                    If Subcon2 <> "" Then
                        lastrow = Cells(Rows.Count, 5).End(xlUp).Row
                        Cells(lastrow, 5).Offset(1, 0).Select
                        ActiveCell.Value = SubPart2
                        ActiveCell.Offset(1, 0).Value = 20
                        ActiveCell.Offset(0, 2).Value = "SUBCON"
                        ActiveCell.Offset(0, 3).Value = SubComm2
                        ActiveCell.Offset(0, 4).Value = 1300
                        ActiveCell.Offset(0, 5).Value = "COST TO PROCESS"
                        ActiveCell.Offset(0, 6).Value = "MN"
                        ActiveCell.Offset(1, 0).Value = SubPart2
                        ActiveCell.Offset(1, 1).Value = 10
                        ActiveCell.Offset(1, 2).Value = "SUBCON"
                        ActiveCell.Offset(1, 3).Value = SubComm2
                        ActiveCell.Offset(1, 4).Value = "0200"
                        ActiveCell.Offset(1, 5).Value = "10"
                        ActiveCell.Offset(1, 6).Value = "DY"
                        Subcon2 = ""
                        SubQty2 = ""
                        SubComm2 = ""
                        SubPart2 = ""
                    Else
                    End If
                        If Subcon3 <> "" Then
                            lastrow = Cells(Rows.Count, 5).End(xlUp).Row
                            Cells(lastrow, 5).Offset(1, 0).Select
                            ActiveCell.Value = SubPart3
                            ActiveCell.Offset(1, 0).Value = 30
                            ActiveCell.Offset(0, 2).Value = "SUBCON"
                            ActiveCell.Offset(0, 3).Value = SubComm3
                            ActiveCell.Offset(0, 4).Value = 1300
                            ActiveCell.Offset(0, 5).Value = "COST TO PROCESS"
                            ActiveCell.Offset(0, 6).Value = "MN"
                            ActiveCell.Offset(1, 0).Value = SubPart3
                            ActiveCell.Offset(1, 1).Value = 10
                            ActiveCell.Offset(1, 2).Value = "SUBCON"
                            ActiveCell.Offset(1, 3).Value = SubComm3
                            ActiveCell.Offset(1, 4).Value = "0200"
                            ActiveCell.Offset(1, 5).Value = "10"
                            ActiveCell.Offset(1, 6).Value = "DY"
                            Subcon3 = ""
                            SubQty3 = ""
                            SubComm3 = ""
                            SubPart3 = ""
                        Else
                        End If
                            If Subcon4 <> "" Then
                                lastrow = Cells(Rows.Count, 5).End(xlUp).Row
                                Cells(lastrow, 5).Offset(1, 0).Select
                                ActiveCell.Value = SubPart4
                                ActiveCell.Offset(1, 0).Value = 40
                                ActiveCell.Offset(0, 2).Value = "SUBCON"
                                ActiveCell.Offset(0, 3).Value = SubComm4
                                ActiveCell.Offset(0, 4).Value = 1300
                                ActiveCell.Offset(0, 5).Value = "COST TO PROCESS"
                                ActiveCell.Offset(0, 6).Value = "MN"
                                ActiveCell.Offset(1, 0).Value = SubPart4
                                ActiveCell.Offset(1, 1).Value = 10
                                ActiveCell.Offset(1, 2).Value = "SUBCON"
                                ActiveCell.Offset(1, 3).Value = SubComm4
                                ActiveCell.Offset(1, 4).Value = "0200"
                                ActiveCell.Offset(1, 5).Value = "10"
                                ActiveCell.Offset(1, 6).Value = "DY"
                                Subcon4 = ""
                                SubQty4 = ""
                                SubComm4 = ""
                                SubPart4 = ""
                            Else
                            End If
                                If Subcon5 <> "" Then
                                    lastrow = Cells(Rows.Count, 5).End(xlUp).Row
                                    Cells(lastrow, 5).Offset(1, 0).Select
                                    ActiveCell.Value = SubPart5
                                    ActiveCell.Offset(1, 0).Value = 50
                                    ActiveCell.Offset(0, 2).Value = "SUBCON"
                                    ActiveCell.Offset(0, 3).Value = SubComm5
                                    ActiveCell.Offset(0, 4).Value = 1300
                                    ActiveCell.Offset(0, 5).Value = "COST TO PROCESS"
                                    ActiveCell.Offset(0, 6).Value = "MN"
                                    ActiveCell.Offset(1, 0).Value = SubPart5
                                    ActiveCell.Offset(1, 1).Value = 10
                                    ActiveCell.Offset(1, 2).Value = "SUBCON"
                                    ActiveCell.Offset(1, 3).Value = SubComm5
                                    ActiveCell.Offset(1, 4).Value = "0200"
                                    ActiveCell.Offset(1, 5).Value = "10"
                                    ActiveCell.Offset(1, 6).Value = "DY"
                                    Subcon5 = ""
                                    SubQty5 = ""
                                    SubComm5 = ""
                                    SubPart5 = ""
                                Else
                                End If
                If ActiveSheet.Name <> "Export" Then
                    Sheets("Export").Select
                Else
                End If
            Else
                If ActiveCell.Value = "TOP LEVEL" Then
                    toplvl = ActiveCell.Offset(0, -2).Value
                    Debug.Print toplvl
                Else
                    If ActiveCell.Value = "SUBCON" Then
                        If Subcon1 = "" Then
                            Subcon1 = ActiveCell.Offset(0, -1).Value
                            SubQty1 = ActiveCell.Offset(0, 3).Value
                            SubComm1 = ActiveCell.Offset(0, 19).Value
                            SubPart1 = toplvl
                        Else
                            If Subcon2 = "" Then
                            Subcon2 = ActiveCell.Offset(0, -1).Value
                            SubQty2 = ActiveCell.Offset(0, 3).Value
                            SubComm2 = ActiveCell.Offset(0, 19).Value
                            SubPart2 = toplvl
                            Else
                                If Subcon3 = "" Then
                                    Subcon3 = ActiveCell.Offset(0, -1).Value
                                    SubQty3 = ActiveCell.Offset(0, 3).Value
                                    SubComm3 = ActiveCell.Offset(0, 19).Value
                                    SubPart3 = toplvl
                                Else
                                    If Subcon4 = "" Then
                                        Subcon4 = ActiveCell.Offset(0, -1).Value
                                        SubQty4 = ActiveCell.Offset(0, 3).Value
                                        SubComm4 = ActiveCell.Offset(0, 19).Value
                                        SubPart4 = toplvl
                                    Else
                                        If Subcon5 = "" Then
                                            Subcon5 = ActiveCell.Offset(0, -1).Value
                                            SubQty5 = ActiveCell.Offset(0, 3).Value
                                            SubComm5 = ActiveCell.Offset(0, 19).Value
                                            SubPart5 = toplvl
                                        Else
                                        End If
                                    End If
                                End If
                            End If
                        End If
                    Else
                        If ActiveCell.Value = "LSR_01" Then
                            CLT = ActiveCell.Offset(0, 3).Value
                            PLT = ActiveCell.Offset(0, 6).Value
                            CLST = ActiveCell.Offset(0, 20).Value
                            comm = ActiveCell.Offset(0, 19).Value
                            Sheets("Planning").Activate
                            lastrow = Cells(Rows.Count, 5).End(xlUp).Row
                            Cells(lastrow, 5).Offset(1, 0).Select
                            ActiveCell.Value = toplvl & "A"
                            ActiveCell.Offset(1, 0).Value = toplvl & "A"
                            ActiveCell.Offset(2, 0).Value = toplvl & "A"
                            ActiveCell.Offset(3, 0).Value = toplvl & "A"
                            ActiveCell.Offset(0, 1).Value = "10"
                            ActiveCell.Offset(1, 1).Value = "10"
                            ActiveCell.Offset(2, 1).Value = "10"
                            ActiveCell.Offset(3, 1).Value = "10"
                            ActiveCell.Offset(0, 2).Value = lsr
                            ActiveCell.Offset(1, 2).Value = lsr
                            ActiveCell.Offset(2, 2).Value = lsr
                            ActiveCell.Offset(3, 2).Value = lsr
                            ActiveCell.Offset(0, 3).Value = comm
                            ActiveCell.Offset(1, 3).Value = comm
                            ActiveCell.Offset(2, 3).Value = comm
                            ActiveCell.Offset(3, 3).Value = comm
                            ActiveCell.Offset(0, 4).Value = 1300
                            ActiveCell.Offset(1, 4).Value = "0300"
                            ActiveCell.Offset(2, 4).Value = 1100
                            ActiveCell.Offset(3, 4).Value = "0300"
                            ActiveCell.Offset(0, 5).Value = CLT
                            ActiveCell.Offset(1, 5).Value = PLT
                            ActiveCell.Offset(2, 5).Value = CLST
                            ActiveCell.Offset(3, 5).Value = 5
                            ActiveCell.Offset(0, 6).Value = "MN"
                            ActiveCell.Offset(1, 6).Value = "MN"
                            ActiveCell.Offset(2, 6).Value = "MN"
                            ActiveCell.Offset(3, 6).Value = "DY"
                        Else
                            If ActiveCell.Value = "FLAT_I" Then
                                CLT = ActiveCell.Offset(0, 3).Value
                                PLT = ActiveCell.Offset(0, 6).Value
                                CLST = ActiveCell.Offset(0, 20).Value
                                comm = ActiveCell.Offset(0, 19).Value
                                Sheets("Planning").Activate
                                lastrow = Cells(Rows.Count, 5).End(xlUp).Row
                                Cells(lastrow, 5).Offset(1, 0).Select
                                ActiveCell.Value = toplvl & "A"
                                ActiveCell.Offset(1, 0).Value = toplvl & "A"
                                ActiveCell.Offset(2, 0).Value = toplvl & "A"
                                ActiveCell.Offset(3, 0).Value = toplvl & "A"
                                ActiveCell.Offset(0, 1).Value = "20"
                                ActiveCell.Offset(1, 1).Value = "20"
                                ActiveCell.Offset(2, 1).Value = "20"
                                ActiveCell.Offset(3, 1).Value = "20"
                                ActiveCell.Offset(0, 2).Value = flat
                                ActiveCell.Offset(1, 2).Value = flat
                                ActiveCell.Offset(2, 2).Value = flat
                                ActiveCell.Offset(3, 2).Value = flat
                                ActiveCell.Offset(0, 3).Value = comm
                                ActiveCell.Offset(1, 3).Value = comm
                                ActiveCell.Offset(2, 3).Value = comm
                                ActiveCell.Offset(3, 3).Value = comm
                                ActiveCell.Offset(0, 4).Value = 1300
                                ActiveCell.Offset(1, 4).Value = "0300"
                                ActiveCell.Offset(2, 4).Value = 1100
                                ActiveCell.Offset(3, 4).Value = "0300"
                                ActiveCell.Offset(0, 5).Value = CLT
                                ActiveCell.Offset(1, 5).Value = PLT
                                ActiveCell.Offset(2, 5).Value = CLST
                                ActiveCell.Offset(3, 5).Value = 2
                                ActiveCell.Offset(0, 6).Value = "MN"
                                ActiveCell.Offset(1, 6).Value = "MN"
                                ActiveCell.Offset(2, 6).Value = "MN"
                                ActiveCell.Offset(3, 6).Value = "DY"
                            Else
                                If ActiveCell.Value = "P_MARK" Then
                                    CLT = ActiveCell.Offset(0, 3).Value
                                    PLT = ActiveCell.Offset(0, 6).Value
                                    CLST = ActiveCell.Offset(0, 20).Value
                                    comm = ActiveCell.Offset(0, 19).Value
                                    Sheets("Planning").Activate
                                    lastrow = Cells(Rows.Count, 5).End(xlUp).Row
                                    Cells(lastrow, 5).Offset(1, 0).Select
                                    ActiveCell.Value = toplvl & "A"
                                    ActiveCell.Offset(1, 0).Value = toplvl & "A"
                                    ActiveCell.Offset(2, 0).Value = toplvl & "A"
                                    ActiveCell.Offset(3, 0).Value = toplvl & "A"
                                    ActiveCell.Offset(0, 1).Value = "30"
                                    ActiveCell.Offset(1, 1).Value = "30"
                                    ActiveCell.Offset(2, 1).Value = "30"
                                    ActiveCell.Offset(3, 1).Value = "30"
                                    ActiveCell.Offset(0, 2).Value = pmark
                                    ActiveCell.Offset(1, 2).Value = pmark
                                    ActiveCell.Offset(2, 2).Value = pmark
                                    ActiveCell.Offset(3, 2).Value = pmark
                                    ActiveCell.Offset(0, 3).Value = comm
                                    ActiveCell.Offset(1, 3).Value = comm
                                    ActiveCell.Offset(2, 3).Value = comm
                                    ActiveCell.Offset(3, 3).Value = comm
                                    ActiveCell.Offset(0, 4).Value = 1300
                                    ActiveCell.Offset(1, 4).Value = "0300"
                                    ActiveCell.Offset(2, 4).Value = 1100
                                    ActiveCell.Offset(3, 4).Value = "0300"
                                    ActiveCell.Offset(0, 5).Value = CLT
                                    ActiveCell.Offset(1, 5).Value = PLT
                                    ActiveCell.Offset(2, 5).Value = CLST
                                    ActiveCell.Offset(3, 5).Value = 2
                                    ActiveCell.Offset(0, 6).Value = "MN"
                                    ActiveCell.Offset(1, 6).Value = "MN"
                                    ActiveCell.Offset(2, 6).Value = "MN"
                                    ActiveCell.Offset(3, 6).Value = "DY"
                                Else
                                    If ActiveCell.Value = "P-BRK1" Or ActiveCell.Value = "P_BRK1" Then
                                        CLT = ActiveCell.Offset(0, 3).Value
                                        PLT = ActiveCell.Offset(0, 6).Value
                                        CLST = ActiveCell.Offset(0, 20).Value
                                        comm = ActiveCell.Offset(0, 19).Value
                                        Sheets("Planning").Activate
                                        lastrow = Cells(Rows.Count, 5).End(xlUp).Row
                                        Cells(lastrow, 5).Offset(1, 0).Select
                                        ActiveCell.Value = toplvl & "A"
                                        ActiveCell.Offset(1, 0).Value = toplvl & "A"
                                        ActiveCell.Offset(2, 0).Value = toplvl & "A"
                                        ActiveCell.Offset(3, 0).Value = toplvl & "A"
                                        ActiveCell.Offset(4, 0).Value = toplvl & "A"
                                        ActiveCell.Offset(0, 1).Value = "40"
                                        ActiveCell.Offset(1, 1).Value = "40"
                                        ActiveCell.Offset(2, 1).Value = "40"
                                        ActiveCell.Offset(3, 1).Value = "40"
                                        ActiveCell.Offset(4, 1).Value = "40"
                                        ActiveCell.Offset(0, 2).Value = pbrk
                                        ActiveCell.Offset(1, 2).Value = pbrk
                                        ActiveCell.Offset(2, 2).Value = pbrk
                                        ActiveCell.Offset(3, 2).Value = pbrk
                                        ActiveCell.Offset(4, 2).Value = pbrk
                                        ActiveCell.Offset(0, 3).Value = comm
                                        ActiveCell.Offset(1, 3).Value = comm
                                        ActiveCell.Offset(2, 3).Value = comm
                                        ActiveCell.Offset(3, 3).Value = comm
                                        ActiveCell.Offset(4, 3).Value = comm
                                        ActiveCell.Offset(0, 4).Value = 1300
                                        ActiveCell.Offset(1, 4).Value = "0300"
                                        ActiveCell.Offset(2, 4).Value = 1100
                                        ActiveCell.Offset(3, 4).Value = 100
                                        ActiveCell.Offset(4, 4).Value = "0300"
                                        ActiveCell.Offset(0, 5).Value = CLT
                                        ActiveCell.Offset(1, 5).Value = PLT
                                        ActiveCell.Offset(2, 5).Value = CLST
                                        ActiveCell.Offset(3, 5).Value = CLST
                                        ActiveCell.Offset(4, 5).Value = 2
                                        ActiveCell.Offset(0, 6).Value = "MN"
                                        ActiveCell.Offset(1, 6).Value = "MN"
                                        ActiveCell.Offset(2, 6).Value = "MN"
                                        ActiveCell.Offset(3, 6).Value = "MN"
                                        ActiveCell.Offset(4, 6).Value = "DY"
                                    Else
                                        If ActiveCell.Value = "DIM_1" Then
                                            CLT = ActiveCell.Offset(0, 3).Value
                                            PLT = ActiveCell.Offset(0, 6).Value
                                            CLST = ActiveCell.Offset(0, 20).Value
                                            comm = ActiveCell.Offset(0, 19).Value
                                            Sheets("Planning").Activate
                                            lastrow = Cells(Rows.Count, 5).End(xlUp).Row
                                            Cells(lastrow, 5).Offset(1, 0).Select
                                            ActiveCell.Value = toplvl & "A"
                                            ActiveCell.Offset(1, 0).Value = toplvl & "A"
                                            ActiveCell.Offset(2, 0).Value = toplvl & "A"
                                            ActiveCell.Offset(3, 0).Value = toplvl & "A"
                                            ActiveCell.Offset(0, 1).Value = "50"
                                            ActiveCell.Offset(1, 1).Value = "50"
                                            ActiveCell.Offset(2, 1).Value = "50"
                                            ActiveCell.Offset(3, 1).Value = "50"
                                            ActiveCell.Offset(0, 2).Value = dmnsl
                                            ActiveCell.Offset(1, 2).Value = dmnsl
                                            ActiveCell.Offset(2, 2).Value = dmnsl
                                            ActiveCell.Offset(3, 2).Value = dmnsl
                                            ActiveCell.Offset(0, 3).Value = comm
                                            ActiveCell.Offset(1, 3).Value = comm
                                            ActiveCell.Offset(2, 3).Value = comm
                                            ActiveCell.Offset(3, 3).Value = comm
                                            ActiveCell.Offset(0, 4).Value = 1300
                                            ActiveCell.Offset(1, 4).Value = "0300"
                                            ActiveCell.Offset(2, 4).Value = 1100
                                            ActiveCell.Offset(3, 4).Value = "0300"
                                            ActiveCell.Offset(0, 5).Value = CLT
                                            ActiveCell.Offset(1, 5).Value = PLT
                                            ActiveCell.Offset(2, 5).Value = CLST
                                            ActiveCell.Offset(3, 5).Value = 2
                                            ActiveCell.Offset(0, 6).Value = "MN"
                                            ActiveCell.Offset(1, 6).Value = "MN"
                                            ActiveCell.Offset(2, 6).Value = "MN"
                                            ActiveCell.Offset(3, 6).Value = "DY"
                                        Else
                                            If ActiveCell.Value = "FIN_I" Then
                                                CLT = ActiveCell.Offset(0, 3).Value
                                                PLT = ActiveCell.Offset(0, 6).Value
                                                CLST = ActiveCell.Offset(0, 20).Value
                                                comm = ActiveCell.Offset(0, 19).Value
                                                Sheets("Planning").Activate
                                                lastrow = Cells(Rows.Count, 5).End(xlUp).Row
                                                Cells(lastrow, 5).Offset(1, 0).Select
                                                ActiveCell.Value = toplvl & "A"
                                                ActiveCell.Offset(1, 0).Value = toplvl & "A"
                                                ActiveCell.Offset(2, 0).Value = toplvl & "A"
                                                ActiveCell.Offset(3, 0).Value = toplvl & "A"
                                                ActiveCell.Offset(0, 1).Value = "60"
                                                ActiveCell.Offset(1, 1).Value = "60"
                                                ActiveCell.Offset(2, 1).Value = "60"
                                                ActiveCell.Offset(3, 1).Value = "60"
                                                ActiveCell.Offset(0, 2).Value = final
                                                ActiveCell.Offset(1, 2).Value = final
                                                ActiveCell.Offset(2, 2).Value = final
                                                ActiveCell.Offset(3, 2).Value = final
                                                ActiveCell.Offset(0, 3).Value = comm
                                                ActiveCell.Offset(1, 3).Value = comm
                                                ActiveCell.Offset(2, 3).Value = comm
                                                ActiveCell.Offset(3, 3).Value = comm
                                                ActiveCell.Offset(0, 4).Value = 1300
                                                ActiveCell.Offset(1, 4).Value = "0300"
                                                ActiveCell.Offset(2, 4).Value = 1100
                                                ActiveCell.Offset(3, 4).Value = "0300"
                                                ActiveCell.Offset(0, 5).Value = CLT
                                                ActiveCell.Offset(1, 5).Value = PLT
                                                ActiveCell.Offset(2, 5).Value = CLST
                                                ActiveCell.Offset(3, 5).Value = 2
                                                ActiveCell.Offset(0, 6).Value = "MN"
                                                ActiveCell.Offset(1, 6).Value = "MN"
                                                ActiveCell.Offset(2, 6).Value = "MN"
                                                ActiveCell.Offset(3, 6).Value = "DY"
                                                
                                            Else
                                                If ActiveSheet.Name <> "Export" Then
                                                    Sheets("Export").Select
                                                Else
                                                End If
                                            End If
                                        End If
                                    End If
                                End If
                            End If
                        End If
                    End If
                End If
            End If
        End If
            If ActiveSheet.Name <> "Export" Then
                Sheets("Export").Select
            Else
            End If
        ActiveCell.Offset(1, 0).Select
    Next cl
End Sub
 
Last edited by a moderator:

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Sort by date Sort by votes
I changed the If for Select Case, and added a couple of subroutines to make the code more compact.


Code: 
Sub COSTPLANBREAKDOWN()
    Dim rngstart As Range
    Dim tst As Range, lastrow As Long
    Dim cl As Object
    Dim L As Long, strcells As String, lsr As String, toplvl As String, comm As String, flat As String
    Dim CLT As String, PLT As String, CLST As String, pmark As String, pbrk As String, dmnsl As String, final As String
    Dim Subcon1 As String, Subcon2 As String, Subcon3 As String, Subcon4 As String, Subcon5 As String
    Dim SubQty1 As String, SubQty2 As String, SubQty3 As String, SubQty4 As String, SubQty5 As String
    Dim SubComm1 As String, SubComm2 As String, SubComm3 As String, SubComm4 As String, SubComm5 As String
    Dim SubPart1 As String, SubPart2 As String, SubPart3 As String, SubPart4 As String, SubPart5 As String
    '
    Application.ScreenUpdating = False
    Sheets("Export").Range("H6").Select
    lsr = "LSR_01"
    flat = "FLAT_I"
    pmark = "P_MARK"
    pbrk = "P_BRK1"
    dmnsl = "DIM_1"
    final = "FIN_I"
    Set rngstart = ActiveCell
    For Each cl In Sheets("Export").Range("H:H")
        If ActiveCell.Value = "" Then
            Sheets("Planning").Select
            Application.ScreenUpdating = True
            Exit Sub
        Else
            If ActiveCell.Value = "TOP LEVEL" And Subcon1 <> "" Or Subcon2 <> "" Or Subcon3 <> "" Or Subcon4 <> "" Or Subcon5 <> "" Then
                toplvl = ActiveCell.Offset(0, -2).Value
                Sheets("Planning").Select
                lastrow = Cells(Rows.Count, 5).End(xlUp).Row
                Cells(lastrow, 5).Offset(1, 0).Select
                If Subcon1 <> "" Then
                    ActiveCell.Value = SubPart1
                    ActiveCell.Offset(0, 1).Value = 9400
                    ActiveCell.Offset(0, 2).Value = "RWRK"
                    ActiveCell.Offset(0, 3).Value = SubComm1
                    ActiveCell.Offset(0, 4).Value = "0200"
                    ActiveCell.Offset(0, 5).Value = "0.01"
                    ActiveCell.Offset(0, 6).Value = "MN"
                    ActiveCell.Offset(1, 0).Value = SubPart1
                    ActiveCell.Offset(1, 1).Value = 9500
                    ActiveCell.Offset(1, 2).Value = "SUBBFF"
                    ActiveCell.Offset(1, 3).Value = SubComm1
                    ActiveCell.Offset(1, 4).Value = "0200"
                    ActiveCell.Offset(1, 5).Value = ".01"
                    ActiveCell.Offset(1, 6).Value = "MN"
                    ActiveCell.Offset(2, 0).Value = SubPart1
                    ActiveCell.Offset(2, 1).Value = 10
                    ActiveCell.Offset(2, 2).Value = "SUBCON"
                    ActiveCell.Offset(2, 3).Value = SubComm1
                    ActiveCell.Offset(2, 4).Value = 1300
                    ActiveCell.Offset(2, 5).Value = "COST TO PROCESS"
                    ActiveCell.Offset(2, 6).Value = "MN"
                    ActiveCell.Offset(3, 0).Value = SubPart1
                    ActiveCell.Offset(3, 1).Value = 10
                    ActiveCell.Offset(3, 2).Value = "SUBCON"
                    ActiveCell.Offset(3, 3).Value = SubComm1
                    ActiveCell.Offset(3, 4).Value = "0200"
                    ActiveCell.Offset(3, 5).Value = "10"
                    ActiveCell.Offset(3, 6).Value = "DY"
                    ActiveCell.Offset(4, 0).Value = SubPart1
                    ActiveCell.Offset(4, 1).Value = 20
                    ActiveCell.Offset(4, 2).Value = "SHPBFF"
                    ActiveCell.Offset(4, 3).Value = SubComm1
                    ActiveCell.Offset(4, 4).Value = "0300"
                    ActiveCell.Offset(4, 5).Value = "5"
                    ActiveCell.Offset(4, 6).Value = "DY"
                    Subcon1 = ""
                    SubQty1 = ""
                    SubComm1 = ""
                    SubPart1 = ""
                End If
                If Subcon2 <> "" Then
                    Call AssignValues(20, SubComm2, SubPart2)
                    Subcon2 = ""
                    SubQty2 = ""
                    SubComm2 = ""
                    SubPart2 = ""
                End If
                If Subcon3 <> "" Then
                    Call AssignValues(30, SubComm3, SubPart3)
                    Subcon3 = ""
                    SubQty3 = ""
                    SubComm3 = ""
                    SubPart3 = ""
                End If
                If Subcon4 <> "" Then
                    Call AssignValues(40, SubComm4, SubPart4)
                    Subcon4 = ""
                    SubQty4 = ""
                    SubComm4 = ""
                    SubPart4 = ""
                End If
                If Subcon5 <> "" Then
                    Call AssignValues(50, SubComm5, SubPart5)
                    Subcon5 = ""
                    SubQty5 = ""
                    SubComm5 = ""
                    SubPart5 = ""
                End If
                If ActiveSheet.Name <> "Export" Then
                    Sheets("Export").Select
                End If
            Else
                If ActiveCell.Value = "TOP LEVEL" Then
                    toplvl = ActiveCell.Offset(0, -2).Value
                    Debug.Print toplvl
                Else
                    If ActiveCell.Value = "SUBCON" Then
                      Select Case True
                        Case Subcon1 = ""
                            Subcon1 = ActiveCell.Offset(0, -1).Value
                            SubQty1 = ActiveCell.Offset(0, 3).Value
                            SubComm1 = ActiveCell.Offset(0, 19).Value
                            SubPart1 = toplvl
                        Case Subcon2 = ""
                            Subcon2 = ActiveCell.Offset(0, -1).Value
                            SubQty2 = ActiveCell.Offset(0, 3).Value
                            SubComm2 = ActiveCell.Offset(0, 19).Value
                            SubPart2 = toplvl
                        Case Subcon3 = ""
                            Subcon3 = ActiveCell.Offset(0, -1).Value
                            SubQty3 = ActiveCell.Offset(0, 3).Value
                            SubComm3 = ActiveCell.Offset(0, 19).Value
                            SubPart3 = toplvl
                        Case Subcon4 = ""
                            Subcon4 = ActiveCell.Offset(0, -1).Value
                            SubQty4 = ActiveCell.Offset(0, 3).Value
                            SubComm4 = ActiveCell.Offset(0, 19).Value
                            SubPart4 = toplvl
                        Case Subcon5 = ""
                            Subcon5 = ActiveCell.Offset(0, -1).Value
                            SubQty5 = ActiveCell.Offset(0, 3).Value
                            SubComm5 = ActiveCell.Offset(0, 19).Value
                            SubPart5 = toplvl
                        End Select
                    Else
                      Select Case ActiveCell.Value
                        Case "LSR_01":  Call Put_Values("10", lsr, 5, toplvl)
                        Case "FLAT_I":  Call Put_Values("20", flat, 2, toplvl)
                        Case "P_MARK":  Call Put_Values("30", pmark, 2, toplvl)
                        Case "P-BRK1":  Call Put_Values("40", pbrk, 2, toplvl)
                        Case "DIM_1":   Call Put_Values("50", dmnsl, 2, toplvl)
                        Case "FIN_I":   Call Put_Values("60", final, 2, toplvl)
                      End Select
                      If ActiveSheet.Name <> "Export" Then
                          Sheets("Export").Select
                      End If
                    End If
                End If
            End If
        End If
            If ActiveSheet.Name <> "Export" Then
                Sheets("Export").Select
            Else
            End If
        ActiveCell.Offset(1, 0).Select
    Next cl
End Sub


Sub Put_Values(a, b, c, toplvl)
  Dim CLT, PLT, CLST, comm, lastrow
  CLT = ActiveCell.Offset(0, 3).Value
  PLT = ActiveCell.Offset(0, 6).Value
  CLST = ActiveCell.Offset(0, 20).Value
  comm = ActiveCell.Offset(0, 19).Value
  Sheets("Planning").Activate
  lastrow = Cells(Rows.Count, 5).End(xlUp).Row
  Cells(lastrow, 5).Offset(1, 0).Select
  ActiveCell.Value = toplvl & "A"
  ActiveCell.Offset(1, 0).Value = toplvl & "A"
  ActiveCell.Offset(2, 0).Value = toplvl & "A"
  ActiveCell.Offset(3, 0).Value = toplvl & "A"
  ActiveCell.Offset(0, 1).Value = a
  ActiveCell.Offset(1, 1).Value = a
  ActiveCell.Offset(2, 1).Value = a
  ActiveCell.Offset(3, 1).Value = a
  ActiveCell.Offset(0, 2).Value = b
  ActiveCell.Offset(1, 2).Value = b
  ActiveCell.Offset(2, 2).Value = b
  ActiveCell.Offset(3, 2).Value = b
  ActiveCell.Offset(0, 3).Value = comm
  ActiveCell.Offset(1, 3).Value = comm
  ActiveCell.Offset(2, 3).Value = comm
  ActiveCell.Offset(3, 3).Value = comm
  ActiveCell.Offset(0, 4).Value = 1300
  ActiveCell.Offset(1, 4).Value = "0300"
  ActiveCell.Offset(2, 4).Value = 1100
  ActiveCell.Offset(3, 4).Value = "0300"
  ActiveCell.Offset(0, 5).Value = CLT
  ActiveCell.Offset(1, 5).Value = PLT
  ActiveCell.Offset(2, 5).Value = CLST
  ActiveCell.Offset(3, 5).Value = c
  ActiveCell.Offset(0, 6).Value = "MN"
  ActiveCell.Offset(1, 6).Value = "MN"
  ActiveCell.Offset(2, 6).Value = "MN"
  ActiveCell.Offset(3, 6).Value = "DY"
End Sub
'
Sub AssignValues(a, SubCommN, SubPartN)
  Dim lastrow As Long
  lastrow = Cells(Rows.Count, 5).End(xlUp).Row
  Cells(lastrow, 5).Offset(1, 0).Select
  ActiveCell.Value = SubPartN
  ActiveCell.Offset(1, 0).Value = a
  ActiveCell.Offset(0, 2).Value = "SUBCON"
  ActiveCell.Offset(0, 3).Value = SubCommN
  ActiveCell.Offset(0, 4).Value = 1300
  ActiveCell.Offset(0, 5).Value = "COST TO PROCESS"
  ActiveCell.Offset(0, 6).Value = "MN"
  ActiveCell.Offset(1, 0).Value = SubPartN
  ActiveCell.Offset(1, 1).Value = 10
  ActiveCell.Offset(1, 2).Value = "SUBCON"
  ActiveCell.Offset(1, 3).Value = SubCommN
  ActiveCell.Offset(1, 4).Value = "0200"
  ActiveCell.Offset(1, 5).Value = "10"
  ActiveCell.Offset(1, 6).Value = "DY"
End Sub
 
Upvote 0
I'm glad to help you. Thanks for the feedback.
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
Tab Selection from Userform based on Date range
Replies
5
Views
134
campbell1093
C
D
  • Question Question
VBA code works when called directly, but not from another routhine
Replies
7
Views
951
davidepstein22
D
C
  • Question Question
Adding a hyperlink to an automated email
Replies
1
Views
607
Micron
M
U
  • Solved
Fix Required - Find VBA - Specific Value
Replies
4
Views
713
Jimmypop
Jimmypop
I
  • Question Question
Advice to remove old code for a new code
Replies
2
Views
202
ipbr21054
I

Forum statistics

Threads
1,223,889
Messages
6,175,224
Members
452,620
Latest member
dsubash

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top