Excel Forum

Code: Select all

        If .Range("c8") = "1" Then
                For i = 11 To 30
                    If Sheets("LIST").Range("A" & i) > "" Then
                    For k = 5 To r
                        g = .Range("p" & .Rows.Count).End(xlUp).Offset(1, 0).Row
                        If Sheets("LIST").Range("A" & i) = Sheets("data bom").Range("B" & k) Then
                            .Range("p" & g).Resize(, 17).Value = Sheets("data bom").Range("b" & k, "R" & k).Value
                        End If
                    Next k
                    End If
                Next i
        End If

Code: Select all

Sub bom()
    Dim i As Long
    Dim l As Long
    Dim r As Long
    Dim j As Long
    Dim k As Long
    Dim g As Long
    Dim arra As Variant
    Dim arrb As Variant
    Dim m As Integer
    
    arra = Array("c8", "c11", "f11", "f12", "c14", _
        "f14", "c16", "c17", "f16", "c19", _
        "f19", "c21", "f21", "c23", "c24", _
        "c25", "f23", "f24", "f25", "c27", _
        "c33", "f33", "c35", "c37", "f39")
    arrb = VBA.Split("a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,u,s,t,u,v,w,x,y", ",")
    
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    
    Range("p7:af51").ClearContents
    
    With Sheets("Production Order")
        If .Range("L2") <> "AIR CONTROL" Then
           .Range("L2") = "ELECTRIC CONTROL"
        End If
        l = Sheets("LIST").Range("b" & .Rows.Count).End(xlUp).Offset(1, 0).Row
        r = Sheets("data bom").Range("b" & .Rows.Count).End(xlUp).Offset(1, 0).Row
        For m = 0 To UBound(arra)
            If .Range(arra(m)) = "1" Then
                For i = 11 To 30
                    If Sheets("LIST").Range(arrb(m) & i) > "" Then
                    For k = 5 To r
                        g = .Range("p" & .Rows.Count).End(xlUp).Offset(1, 0).Row
                        If Sheets("LIST").Range("A" & i) = Sheets("data bom").Range("B" & k) Then
                            .Range("p" & g).Resize(, 17).Value = Sheets("data bom").Range("b" & k, "R" & k).Value
                        End If
                    Next k
                    End If
                Next i
            End If
        Next m
    End With
    Application.ScreenUpdating = True
    Application.Calculation = xlAutomatic
End Sub

Code: Select all

 Private Sub Worksheet_SelectionChange(ByVal Target As Range)