String running first time but when looping back it isnt placing correctly.

D

DanSMT

Board Regular
Joined
Sep 13, 2019
Messages
203
Office Version
  1. 2013
Platform
  1. Windows
I've come along way with this code to get the first pass to work, however when it has multiple textboxes filled, the code goes off on a tangent and places data oddly.

VBA Code: 
'exit sub if textbox is empty
If var = "" Then
GoTo done2:
End If

SQL = "SELECT * FROM RIDatabase WHERE [Part Number] = '" & var & "'"

'Create the ADODB recordset object.
Set rs = New ADODB.Recordset 'assign memory to the recordset

rs.Open SQL, cnn

'Check if the recordset is empty.
If rs.EOF And rs.BOF Then
'Close the recordset and the connection.
rs.Close
cnn.Close
'clear memory
Set rs = Nothing
Set cnn = Nothing
'Enable the screen.
Application.ScreenUpdating = True
'In case of an empty recordset display an error.
MsgBox "There are no records in the recordset!", vbCritical, "No Records"

Exit Sub
End If

Worksheets("Data").AutoFilterMode = False
Worksheets("Data").range("A2:ZZ10000").ClearContents

'Write the recordset values in the sheet.
Worksheets("Data").range("A2").CopyFromRecordset rs


'transfer from data tab to fai log
Worksheets("data").Activate

'Dim cell As Range
Dim rng As range
Dim shtSrc As Worksheet, shtDest As Worksheet
Dim ite
Dim myDate As Date

myDate = Application.Max(Columns(1))
ActiveSheet.AutoFilterMode = False
Columns(1).AutoFilter Field:=1, Criteria1:=Format(myDate, [A2].NumberFormat)
Set shtSrc = Worksheets("Data") ' Sets "Sheet1" sheet as source sheet
Set shtDest = Worksheets("FAI") 'Sets "Sheet2." sheet as destination sheet
Set rng = range("B2", range("B65536").End(xlUp)).SpecialCells(xlCellTypeVisible)

ite = Me("Part" & poi)

        Sheets("FAI").Copy After:=Sheets("Capability Study")
        ActiveSheet.Name = (ite & " - " & poi)

For Each cell In rng

    If cell = ite Then
    
        With Worksheets("data")
        
            'CHECK VAL
            If Sheets(ite & " - " & poi).range("C4").Value <> "" Then
            Exit For
            End If
            'Add Item
            With .range("B2", .Cells(.Rows.Count, 2).End(xlUp)).SpecialCells(xlCellTypeVisible)
                Sheets(ite & " - " & poi).range("C4").Value = .Value
            End With
            With .range("C2", .Cells(.Rows.Count, 3).End(xlUp)).SpecialCells(xlCellTypeVisible)
                Sheets(ite & " - " & poi).range("C3").Value = .Value
            End With
            With .range("D2", .Cells(.Rows.Count, 4).End(xlUp)).SpecialCells(xlCellTypeVisible)
                Sheets(ite & " - " & poi).range("K7").Value = .Value
            End With
            With .range("Z1", .Cells(.Rows.Count, 26).End(xlUp)).SpecialCells(xlCellTypeVisible)
                Sheets(ite & " - " & poi).range("A9").Value = .Value
            End With
            With .range("AA1", .Cells(.Rows.Count, 27).End(xlUp)).SpecialCells(xlCellTypeVisible)
                Sheets(ite & " - " & poi).range("A10").Value = .Value
            End With
            With .range("AB1", .Cells(.Rows.Count, 28).End(xlUp)).SpecialCells(xlCellTypeVisible)
                Sheets(ite & " - " & poi).range("A11").Value = .Value
            End With
            With .range("AC1", .Cells(.Rows.Count, 29).End(xlUp)).SpecialCells(xlCellTypeVisible)
                Sheets(ite & " - " & poi).range("A12").Value = .Value
            End With
            With .range("AD1", .Cells(.Rows.Count, 30).End(xlUp)).SpecialCells(xlCellTypeVisible)
                Sheets(ite & " - " & poi).range("A13").Value = .Value
            End With
            With .range("AE1", .Cells(.Rows.Count, 31).End(xlUp)).SpecialCells(xlCellTypeVisible)
                Sheets(ite & " - " & poi).range("A14").Value = .Value
            End With
            With .range("AF1", .Cells(.Rows.Count, 32).End(xlUp)).SpecialCells(xlCellTypeVisible)
                Sheets(ite & " - " & poi).range("A15").Value = .Value
            End With
            With .range("AG1", .Cells(.Rows.Count, 33).End(xlUp)).SpecialCells(xlCellTypeVisible)
                Sheets(ite & " - " & poi).range("A16").Value = .Value
            End With
            With .range("AH1", .Cells(.Rows.Count, 34).End(xlUp)).SpecialCells(xlCellTypeVisible)
                Sheets(ite & " - " & poi).range("A17").Value = .Value
            End With
            With .range("AI1", .Cells(.Rows.Count, 35).End(xlUp)).SpecialCells(xlCellTypeVisible)
                Sheets(ite & " - " & poi).range("A18").Value = .Value
            End With
            With .range("AJ1", .Cells(.Rows.Count, 36).End(xlUp)).SpecialCells(xlCellTypeVisible)
                Sheets(ite & " - " & poi).range("A19").Value = .Value
            End With
            With .range("AK1", .Cells(.Rows.Count, 37).End(xlUp)).SpecialCells(xlCellTypeVisible)
                Sheets(ite & " - " & poi).range("A20").Value = .Value
            End With
            With .range("AL1", .Cells(.Rows.Count, 38).End(xlUp)).SpecialCells(xlCellTypeVisible)
                Sheets(ite & " - " & poi).range("A21").Value = .Value
            End With
            With .range("AM1", .Cells(.Rows.Count, 39).End(xlUp)).SpecialCells(xlCellTypeVisible)
                Sheets(ite & " - " & poi).range("A22").Value = .Value
            End With
            With .range("AN1", .Cells(.Rows.Count, 40).End(xlUp)).SpecialCells(xlCellTypeVisible)
                Sheets(ite & " - " & poi).range("A23").Value = .Value
            End With
            With .range("AO1", .Cells(.Rows.Count, 41).End(xlUp)).SpecialCells(xlCellTypeVisible)
                Sheets(ite & " - " & poi).range("A24").Value = .Value
            End With
            With .range("AP1", .Cells(.Rows.Count, 42).End(xlUp)).SpecialCells(xlCellTypeVisible)
                Sheets(ite & " - " & poi).range("A25").Value = .Value
            End With
            With .range("AQ1", .Cells(.Rows.Count, 43).End(xlUp)).SpecialCells(xlCellTypeVisible)
                Sheets(ite & " - " & poi).range("A26").Value = .Value
            End With
            
            'Add Nominal
            With .range("CL2", .Cells(.Rows.Count, 90).End(xlUp)).SpecialCells(xlCellTypeVisible)
                Sheets(ite & " - " & poi).range("B9").Value = .Value
            End With
            With .range("CO2", .Cells(.Rows.Count, 93).End(xlUp)).SpecialCells(xlCellTypeVisible)
                Sheets(ite & " - " & poi).range("B10").Value = .Value
            End With
            With .range("CR2", .Cells(.Rows.Count, 96).End(xlUp)).SpecialCells(xlCellTypeVisible)
                Sheets(ite & " - " & poi).range("B11").Value = .Value
            End With
            With .range("CU2", .Cells(.Rows.Count, 99).End(xlUp)).SpecialCells(xlCellTypeVisible)
                Sheets(ite & " - " & poi).range("B12").Value = .Value
            End With
            With .range("CX2", .Cells(.Rows.Count, 102).End(xlUp)).SpecialCells(xlCellTypeVisible)
                Sheets(ite & " - " & poi).range("B13").Value = .Value
            End With
            With .range("DA2", .Cells(.Rows.Count, 105).End(xlUp)).SpecialCells(xlCellTypeVisible)
                Sheets(ite & " - " & poi).range("B14").Value = .Value
            End With
            With .range("DD2", .Cells(.Rows.Count, 108).End(xlUp)).SpecialCells(xlCellTypeVisible)
                Sheets(ite & " - " & poi).range("B15").Value = .Value
            End With
            With .range("DG2", .Cells(.Rows.Count, 111).End(xlUp)).SpecialCells(xlCellTypeVisible)
                Sheets(ite & " - " & poi).range("B16").Value = .Value
            End With
            With .range("DJ2", .Cells(.Rows.Count, 114).End(xlUp)).SpecialCells(xlCellTypeVisible)
                Sheets(ite & " - " & poi).range("B17").Value = .Value
            End With
            With .range("DM2", .Cells(.Rows.Count, 117).End(xlUp)).SpecialCells(xlCellTypeVisible)
                Sheets(ite & " - " & poi).range("B18").Value = .Value
            End With
            With .range("DP2", .Cells(.Rows.Count, 120).End(xlUp)).SpecialCells(xlCellTypeVisible)
                Sheets(ite & " - " & poi).range("B19").Value = .Value
            End With
            With .range("DS2", .Cells(.Rows.Count, 123).End(xlUp)).SpecialCells(xlCellTypeVisible)
                Sheets(ite & " - " & poi).range("B20").Value = .Value
            End With
            With .range("DV2", .Cells(.Rows.Count, 126).End(xlUp)).SpecialCells(xlCellTypeVisible)
                Sheets(ite & " - " & poi).range("B21").Value = .Value
            End With
            With .range("DY2", .Cells(.Rows.Count, 129).End(xlUp)).SpecialCells(xlCellTypeVisible)
                Sheets(ite & " - " & poi).range("B22").Value = .Value
            End With
            With .range("EB2", .Cells(.Rows.Count, 132).End(xlUp)).SpecialCells(xlCellTypeVisible)
                Sheets(ite & " - " & poi).range("B23").Value = .Value
            End With
            With .range("EE2", .Cells(.Rows.Count, 135).End(xlUp)).SpecialCells(xlCellTypeVisible)
                Sheets(ite & " - " & poi).range("B24").Value = .Value
            End With
            With .range("EH2", .Cells(.Rows.Count, 138).End(xlUp)).SpecialCells(xlCellTypeVisible)
                Sheets(ite & " - " & poi).range("B25").Value = .Value
            End With
            With .range("EK2", .Cells(.Rows.Count, 141).End(xlUp)).SpecialCells(xlCellTypeVisible)
                Sheets(ite & " - " & poi).range("B26").Value = .Value
            End With
            
            'Add Upper Limit
            With .range("CM2", .Cells(.Rows.Count, 91).End(xlUp)).SpecialCells(xlCellTypeVisible)
                Sheets(ite & " - " & poi).range("C9").Value = .Value
            End With
            With .range("CP2", .Cells(.Rows.Count, 94).End(xlUp)).SpecialCells(xlCellTypeVisible)
                Sheets(ite & " - " & poi).range("C10").Value = .Value
            End With
            With .range("CS2", .Cells(.Rows.Count, 97).End(xlUp)).SpecialCells(xlCellTypeVisible)
                Sheets(ite & " - " & poi).range("C11").Value = .Value
            End With
            With .range("CV2", .Cells(.Rows.Count, 100).End(xlUp)).SpecialCells(xlCellTypeVisible)
                Sheets(ite & " - " & poi).range("C12").Value = .Value
            End With
            With .range("CY2", .Cells(.Rows.Count, 103).End(xlUp)).SpecialCells(xlCellTypeVisible)
                Sheets(ite & " - " & poi).range("C13").Value = .Value
            End With
            With .range("DB2", .Cells(.Rows.Count, 106).End(xlUp)).SpecialCells(xlCellTypeVisible)
                Sheets(ite & " - " & poi).range("C14").Value = .Value
            End With
            With .range("DE2", .Cells(.Rows.Count, 109).End(xlUp)).SpecialCells(xlCellTypeVisible)
                Sheets(ite & " - " & poi).range("C15").Value = .Value
            End With
            With .range("DH2", .Cells(.Rows.Count, 112).End(xlUp)).SpecialCells(xlCellTypeVisible)
                Sheets(ite & " - " & poi).range("C16").Value = .Value
            End With
            With .range("DK2", .Cells(.Rows.Count, 115).End(xlUp)).SpecialCells(xlCellTypeVisible)
                Sheets(ite & " - " & poi).range("C17").Value = .Value
            End With
            With .range("DN2", .Cells(.Rows.Count, 118).End(xlUp)).SpecialCells(xlCellTypeVisible)
                Sheets(ite & " - " & poi).range("C18").Value = .Value
            End With
            With .range("DQ2", .Cells(.Rows.Count, 121).End(xlUp)).SpecialCells(xlCellTypeVisible)
                Sheets(ite & " - " & poi).range("C19").Value = .Value
            End With
            With .range("DT2", .Cells(.Rows.Count, 124).End(xlUp)).SpecialCells(xlCellTypeVisible)
                Sheets(ite & " - " & poi).range("C20").Value = .Value
            End With
            With .range("DW2", .Cells(.Rows.Count, 127).End(xlUp)).SpecialCells(xlCellTypeVisible)
                Sheets(ite & " - " & poi).range("C21").Value = .Value
            End With
            With .range("DZ2", .Cells(.Rows.Count, 130).End(xlUp)).SpecialCells(xlCellTypeVisible)
                Sheets(ite & " - " & poi).range("C22").Value = .Value
            End With
            With .range("EC2", .Cells(.Rows.Count, 133).End(xlUp)).SpecialCells(xlCellTypeVisible)
                Sheets(ite & " - " & poi).range("C23").Value = .Value
            End With
            With .range("EF2", .Cells(.Rows.Count, 136).End(xlUp)).SpecialCells(xlCellTypeVisible)
                Sheets(ite & " - " & poi).range("C24").Value = .Value
            End With
            With .range("EI2", .Cells(.Rows.Count, 139).End(xlUp)).SpecialCells(xlCellTypeVisible)
                Sheets(ite & " - " & poi).range("C25").Value = .Value
            End With
            With .range("EL2", .Cells(.Rows.Count, 142).End(xlUp)).SpecialCells(xlCellTypeVisible)
                Sheets(ite & " - " & poi).range("C26").Value = .Value
            End With
            
            'Add Lower Limit
            With .range("CK2", .Cells(.Rows.Count, 89).End(xlUp)).SpecialCells(xlCellTypeVisible)
                Sheets(ite & " - " & poi).range("D9").Value = .Value
            End With
            With .range("CN2", .Cells(.Rows.Count, 92).End(xlUp)).SpecialCells(xlCellTypeVisible)
                Sheets(ite & " - " & poi).range("D10").Value = .Value
            End With
            With .range("CQ2", .Cells(.Rows.Count, 95).End(xlUp)).SpecialCells(xlCellTypeVisible)
                Sheets(ite & " - " & poi).range("D11").Value = .Value
            End With
            With .range("CT2", .Cells(.Rows.Count, 98).End(xlUp)).SpecialCells(xlCellTypeVisible)
                Sheets(ite & " - " & poi).range("D12").Value = .Value
            End With
            With .range("CW2", .Cells(.Rows.Count, 101).End(xlUp)).SpecialCells(xlCellTypeVisible)
                Sheets(ite & " - " & poi).range("D13").Value = .Value
            End With
            With .range("CZ2", .Cells(.Rows.Count, 104).End(xlUp)).SpecialCells(xlCellTypeVisible)
                Sheets(ite & " - " & poi).range("D14").Value = .Value
            End With
            With .range("DC2", .Cells(.Rows.Count, 107).End(xlUp)).SpecialCells(xlCellTypeVisible)
                Sheets(ite & " - " & poi).range("D15").Value = .Value
            End With
            With .range("DF2", .Cells(.Rows.Count, 110).End(xlUp)).SpecialCells(xlCellTypeVisible)
                Sheets(ite & " - " & poi).range("D16").Value = .Value
            End With
            With .range("DI2", .Cells(.Rows.Count, 113).End(xlUp)).SpecialCells(xlCellTypeVisible)
                Sheets(ite & " - " & poi).range("D17").Value = .Value
            End With
            With .range("DL2", .Cells(.Rows.Count, 116).End(xlUp)).SpecialCells(xlCellTypeVisible)
                Sheets(ite & " - " & poi).range("D18").Value = .Value
            End With
            With .range("DO2", .Cells(.Rows.Count, 119).End(xlUp)).SpecialCells(xlCellTypeVisible)
                Sheets(ite & " - " & poi).range("D19").Value = .Value
            End With
            With .range("DR2", .Cells(.Rows.Count, 122).End(xlUp)).SpecialCells(xlCellTypeVisible)
                Sheets(ite & " - " & poi).range("D20").Value = .Value
            End With
            With .range("DU2", .Cells(.Rows.Count, 125).End(xlUp)).SpecialCells(xlCellTypeVisible)
                Sheets(ite & " - " & poi).range("D21").Value = .Value
            End With
            With .range("DX2", .Cells(.Rows.Count, 128).End(xlUp)).SpecialCells(xlCellTypeVisible)
                Sheets(ite & " - " & poi).range("D22").Value = .Value
            End With
            With .range("EA2", .Cells(.Rows.Count, 131).End(xlUp)).SpecialCells(xlCellTypeVisible)
                Sheets(ite & " - " & poi).range("D23").Value = .Value
            End With
            With .range("ED2", .Cells(.Rows.Count, 134).End(xlUp)).SpecialCells(xlCellTypeVisible)
                Sheets(ite & " - " & poi).range("D24").Value = .Value
            End With
            With .range("EG2", .Cells(.Rows.Count, 137).End(xlUp)).SpecialCells(xlCellTypeVisible)
                Sheets(ite & " - " & poi).range("D25").Value = .Value
            End With
            With .range("EJ2", .Cells(.Rows.Count, 140).End(xlUp)).SpecialCells(xlCellTypeVisible)
                Sheets(ite & " - " & poi).range("D26").Value = .Value
            End With
            

        End With
        
    End If

Next cell
            
            
For Each cell In rng

    If cell = ite Then
    
On Error GoTo Catch1
Sheets(ite & " - " & poi).Select
GoTo Finally1

Catch1:
        Sheets("FAI").Copy After:=Sheets("Capability Study")
        ActiveSheet.Name = (ite & " - " & poi)

Finally1:

        
        Dim K As Integer
        Dim LRo As Long

        With Worksheets("data")
        
            If Sheets(ite & " - " & poi).range("F9").Value <> "" Then
            Exit For
            End If
        
            Application.ScreenUpdating = False
            'Find last row with data
            LRo = .Cells(Rows.Count, "A").End(xlUp).Row
            
            'Loop through all columns (Z - AK)
            For K = 26 To 62
                'Check to see if anything in cell
                If .Cells(2, K) <> "" Then
                    'Copy/transpose column to row on output tab
                    .range(.Cells(2, K), .Cells(LRo, K)).Copy
                    Sheets(ite & " - " & poi).Cells(K - 17, "F").PasteSpecial Paste:=xlPasteValues, SkipBlanks:= _
                        False, Transpose:=True
                    Application.CutCopyMode = False
                End If
            Next K
            
        End With
        
        With range("F9:O26")
        .NumberFormat = "General"
        .Value = .Value
        End With
                
    End If

Next cell

'Close the recordset and the connection.
rs.Close
cnn.Close
'clear memory
Set rs = Nothing
Set cnn = Nothing


'loop
Next poi

'Close the recordset and the connection.
rs.Close
cnn.Close
'clear memory
Set rs = Nothing
Set cnn = Nothing


done2:
'Enable the screen.
Application.ScreenUpdating = True

'Inform the user that the macro was executed successfully.
MsgBox "Congratulation the data has been successfully Imported", vbInformation, "Import successful"
Worksheets("PSW").Activate
Unload Me

'error handler
On Error GoTo 0
Exit Sub
errHandler:
'clear memory
Set rs = Nothing
Set cnn = Nothing

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Import_Data"

End Sub

Sorry for the lengthy code.

Data for first sheet appears as follows:

PPAP Template test (Repaired).xlsm
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAYAZBABBBCBDBEBFBGBHBIBJBKBLBMBNBOBPBQBRBSBTBUBVBWBXBYBZCACBCCCDCECFCGCHCICJCKCLCMCNCOCPCQCRCSCTCUCVCWCXCYCZDADBDCDDDEDFDGDHDIDJDKDLDMDNDODPDQDRDSDTDUDVDWDXDYDZEAEBECEDEEEFEGEHEIEJEKELEMENEOEPEQERESETEUEVEWEXEYEZFAFBFCFDFEFFFGFHFIFJFKFLFMFNFOFPFQFRFSFTFUFVFWFXFYFZGAGBGCGDGEGFGGGHGIGJGKGLGMGNGOGPGQGR
1DatePart NumberSupplierRevRI CodeIPC ClassLaminatePrepragSolderMaskFinishShelf LifeDate CodeLayersUL FileRoHSFAIImpedance Control ReportCoCCA Prop 65IPC6012Electrical Test ReportCross SectionContamination ReportSample SizeReject QtyABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKSolder Mask Tape TestGold Finger Tape TestShipment DamageFire RatingMeets Cosmetic SpecUL StampElectrical Test StampSilkscreen LegiblePaint DotsLabels1234Overall Pass/FailUL File NumberCommentsVacuum SealedDesiccant PacksMoisture CardsDate Code PresentSolderability TestDrawing NumberDrawing REVUserNameColorLL AN AUL ALL BN BUL BLL CN CUL CLL DN DUL DLL EN EUL ELL FN FUL FLL GN GUL GLL HN HUL HLL IN IUL ILL JN JUL JLL KN KUL KLL LN LUL LLL MN MUL MLL NN NUL NLL ON OUL OLL PN PUL PLL QN QUL QLL RN RUL RLL SN SUL SLL TN TUL TLL UN UUL ULL VN VUL VLL WN WUL WLL XN XUL XLL YN YUL YLL ZN ZUL ZLL AAN AAUL AALL ABN ABUL ABLL ACN ACUL ACLL ADN ADUL ADLL AEN AEUL AELL AFN AFUL AFLL AGN AGUL AGLL AHN AHUL AHLL AIN AIUL AILL AJN AJUL AJLL AKN AKUL AKLot Size
25/3/2022C073061-D-FAugur Metal Products, Inc.DE06NoNoNoNo5-3-22NoNAReceivedNANANANANANANANA52320.54412.48126.534.1641.543.27.396.833.14208.822.19261256.2156.469.95127.6177.4236.5PassFailJ on RI form has wrong measurement compared to drawing. Drawing and my measurement came out correctNAC073061DJulieS319.6320.4321.2411.7412.5413.3126.2127127.834.134.935.741.541.541.84343.143.25.86.67.46.47.2833.84.66.37.17.921.722.523.3260.9261.7262.5256.1256.9257.7155.1155.9156.769.169.970.7126.6127.4128.2177.4178.2179234.9235.7236.500000000000000000000000000000000000000416
35/3/2022C073061-D-FAugur Metal Products, Inc.DE06NoNoNoNo5-3-22NoNAReceivedNANANANANANANANA52321.141312734.641.643.26.46.893.5120822.3261.1256.3156.369.1127.7178.24235.4PassPassPassPassFailJ on RI form has wrong measurement compared to drawing. Drawing and my measurement came out correctNAC073061DJulieS319.6320.4321.2411.7412.5413.3126.2127127.834.134.935.741.541.541.84343.143.25.86.67.46.47.2833.84.66.37.17.921.722.523.3260.9261.7262.5256.1256.9257.7155.1155.9156.769.169.970.7126.6127.4128.2177.4178.2179234.9235.7236.500000000000000000000000000000000000000416
45/3/2022C073061-D-FAugur Metal Products, Inc.DE06NoNoNoNo5-3-22NoNAReceivedNANANANANANANANA52320.5412.512734.541.5437.36.893.47208.822.26261256.2156.369.2127.8178.1235.7PassPassPassPassFailJ on RI form has wrong measurement compared to drawing. Drawing and my measurement came out correctNAC073061DJulieS319.6320.4321.2411.7412.5413.3126.2127127.834.134.935.741.541.541.84343.143.25.86.67.46.47.2833.84.66.37.17.921.722.523.3260.9261.7262.5256.1256.9257.7155.1155.9156.769.169.970.7126.6127.4128.2177.4178.2179234.9235.7236.500000000000000000000000000000000000000416
55/3/2022C073061-D-FAugur Metal Products, Inc.DE06NoNoNoNo5-3-22NoNAReceivedNANANANANANANANA52321.241312734.241.543.16.76.863.29207.9721.77261256.9156.769.1127.7177.9234.9PassPassPassPassFailJ on RI form has wrong measurement compared to drawing. Drawing and my measurement came out correctNAC073061DJulieS319.6320.4321.2411.7412.5413.3126.2127127.834.134.935.741.541.541.84343.143.25.86.67.46.47.2833.84.66.37.17.921.722.523.3260.9261.7262.5256.1256.9257.7155.1155.9156.769.169.970.7126.6127.4128.2177.4178.2179234.9235.7236.500000000000000000000000000000000000000416
65/3/2022C073061-D-FAugur Metal Products, Inc.DE06NoNoNoNo5-3-22NoNAReceivedNANANANANANANANA52321413127.834.241.5243.27.46.893.52208.922.2261256.7156.469.1128178.2235.8PassPassPassPassFailJ on RI form has wrong measurement compared to drawing. Drawing and my measurement came out correctNAC073061DJulieS319.6320.4321.2411.7412.5413.3126.2127127.834.134.935.741.541.541.84343.143.25.86.67.46.47.2833.84.66.37.17.921.722.523.3260.9261.7262.5256.1256.9257.7155.1155.9156.769.169.970.7126.6127.4128.2177.4178.2179234.9235.7236.500000000000000000000000000000000000000416
Data





Sheet below is the first pass of code prior to loop

PPAP Template test (Repaired).xlsm
ABCDEFGHIJKLMNOP
1
2TEST RESULTS
3SupplierProducts, Inc.Date Samples Made
4Part Number:C073061-D-FMaterial
5Part Name:
6Tool NumberColor
7Cavity NumberDesign Record Change Level:DUL or other Safety Regulation
8ItemNominalUpper Spec LimitLower Spec LimitMethodMeasurement Results (Data, 3 per non-critical, 10 per critical dimension per cavity)
9A320.400321.200319.600320.54321.1320.5321.2321
10B412.500413.300411.700412.48413412.5413413
11C127.000127.800126.20126.5127127127127.8
12D34.90035.70034.1034.1634.634.534.234.2
13E41.50041.80041.5041.541.641.541.541.52
14F43.10043.20043.0043.243.24343.143.2
15G6.6007.4005.807.396.47.36.77.4
16H7.2008.0006.406.836.896.896.866.89
17I3.8004.6003.003.143.513.473.293.52
18J7.1007.9006.30208.8208208.8207.97208.9
19K22.50023.30021.7022.1922.322.2621.7722.2
20L261.700262.500260.90261261.1261261261
21M256.90257.70256.10256.2256.3256.2256.9256.7
22N155.90156.70155.10156.4156.3156.3156.7156.4
23O69.9070.7069.1069.9569.169.269.169.1
24P127.40128.20126.60127.6127.7127.8127.7128
25Q178.20179.00177.40177.4178.24178.1177.9178.2
26R235.70236.50234.90236.5235.4235.7234.9235.8
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50Attach Drawing with Numbered Dimensions. Blanket statements of conformance are unacceptable for any test results.
51PPR SIGNATURETITLEDATE
526/10/2022
C073061-D-F - 1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C3:C7,J3:J6,K7,F52,J52,M52,P7Cellcontains a blank value textNO
F9:O49Cell Valuebetween $D9 and $C9textYES
F9:O49Cell Valuebetween 0.0001 and $D9textYES
F9:O49Cell Value>$C9textYES
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Sort by date Sort by votes
Data sheet for second pass appears as follows:

PPAP Template test (Repaired).xlsm
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAYAZBABBBCBDBEBFBGBHBIBJBKBLBMBNBOBPBQBRBSBTBUBVBWBXBYBZCACBCCCDCECFCGCHCICJCKCLCMCNCOCPCQCRCSCTCUCVCWCXCYCZDADBDCDDDEDFDGDHDIDJDKDLDMDNDODPDQDRDSDTDUDVDWDXDYDZEAEBECEDEEEFEGEHEIEJEKELEMENEOEPEQERESETEUEVEWEXEYEZFAFBFCFDFEFFFGFHFIFJFKFLFMFNFOFPFQFRFSFTFUFVFWFXFYFZGAGBGCGDGEGFGGGHGIGJGKGLGMGNGOGPGQGR
1DatePart NumberSupplierRevRI CodeIPC ClassLaminatePrepragSolderMaskFinishShelf LifeDate CodeLayersUL FileRoHSFAIImpedance Control ReportCoCCA Prop 65IPC6012Electrical Test ReportCross SectionContamination ReportSample SizeReject QtyABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKSolder Mask Tape TestGold Finger Tape TestShipment DamageFire RatingMeets Cosmetic SpecUL StampElectrical Test StampSilkscreen LegiblePaint DotsLabels1234Overall Pass/FailUL File NumberCommentsVacuum SealedDesiccant PacksMoisture CardsDate Code PresentSolderability TestDrawing NumberDrawing REVUserNameColorLL AN AUL ALL BN BUL BLL CN CUL CLL DN DUL DLL EN EUL ELL FN FUL FLL GN GUL GLL HN HUL HLL IN IUL ILL JN JUL JLL KN KUL KLL LN LUL LLL MN MUL MLL NN NUL NLL ON OUL OLL PN PUL PLL QN QUL QLL RN RUL RLL SN SUL SLL TN TUL TLL UN UUL ULL VN VUL VLL WN WUL WLL XN XUL XLL YN YUL YLL ZN ZUL ZLL AAN AAUL AALL ABN ABUL ABLL ACN ACUL ACLL ADN ADUL ADLL AEN AEUL AELL AFN AFUL AFLL AGN AGUL AGLL AHN AHUL AHLL AIN AIUL AILL AJN AJUL AJLL AKN AKUL AKLot Size
212/16/20211316025-H-FKOHLERHE06NANot RequiredReceievedNot RequiredNot RequiredNot RequiredNot RequiredNot RequiredNot RequiredNot RequiredNot Required512.9935.760.1870.125PassNot Required1316025HLisa Meier
312/16/20211316025-H-FKOHLERHE06NANot RequiredReceievedNot RequiredNot RequiredNot RequiredNot RequiredNot RequiredNot RequiredNot RequiredNot Required512.9935.760.1890.125PassNot Required1316025HLisa Meier
412/16/20211316025-H-FKOHLERHE06NANot RequiredReceievedNot RequiredNot RequiredNot RequiredNot RequiredNot RequiredNot RequiredNot RequiredNot Required5133.015.760.1880.125PassNot Required1316025HLisa Meier
512/16/20211316025-H-FKOHLERHE06NANot RequiredReceievedNot RequiredNot RequiredNot RequiredNot RequiredNot RequiredNot RequiredNot RequiredNot Required513.0135.760.1860.125PassNot Required1316025HLisa Meier
612/16/20211316025-H-FKOHLERHE06NANot RequiredReceievedNot RequiredNot RequiredNot RequiredNot RequiredNot RequiredNot RequiredNot RequiredNot Required513.0135.760.1870.125PassNot Required1316025HLisa Meier
Data



This sheet shows the second pass (notice the strange information posted into cells A9:D26

PPAP Template test (Repaired).xlsm
ABCDEFGHIJKLMNOP
1
2TEST RESULTS
3SupplierProducts, Inc.Date Samples Made
4Part Number:C073061-D-FMaterial
5Part Name:
6Tool NumberColor
7Cavity NumberDesign Record Change Level:DUL or other Safety Regulation
8ItemNominalUpper Spec LimitLower Spec LimitMethodMeasurement Results (Data, 3 per non-critical, 10 per critical dimension per cavity)
9A320.400321.200319.600320.54321.1320.5321.2321
10B412.500413.300411.700412.48413412.5413413
11C127.000127.800126.20126.5127127127127.8
12D34.90035.70034.1034.1634.634.534.234.2
13E41.50041.80041.5041.541.641.541.541.52
14F43.10043.20043.0043.243.24343.143.2
15G6.6007.4005.807.396.47.36.77.4
16H7.2008.0006.406.836.896.896.866.89
17I3.8004.6003.003.143.513.473.293.52
18J7.1007.9006.30208.8208208.8207.97208.9
19K22.50023.30021.7022.1922.322.2621.7722.2
20L261.700262.500260.90261261.1261261261
21M256.90257.70256.10256.2256.3256.2256.9256.7
22N155.90156.70155.10156.4156.3156.3156.7156.4
23O69.9070.7069.1069.9569.169.269.169.1
24P127.40128.20126.60127.6127.7127.8127.7128
25Q178.20179.00177.40177.4178.24178.1177.9178.2
26R235.70236.50234.90236.5235.4235.7234.9235.8
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50Attach Drawing with Numbered Dimensions. Blanket statements of conformance are unacceptable for any test results.
51PPR SIGNATURETITLEDATE
526/10/2022
C073061-D-F - 1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C3:C7,J3:J6,K7,F52,J52,M52,P7Cellcontains a blank value textNO
F9:O49Cell Valuebetween $D9 and $C9textYES
F9:O49Cell Valuebetween 0.0001 and $D9textYES
F9:O49Cell Value>$C9textYES
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question Question
Multiple filter criteria using VBA
Replies
2
Views
963
Thundercats
T
U
  • Solved
Rewrite Code as a Sub to work on Active Sheet
Replies
2
Views
53
Skyybot
Skyybot
R
  • Solved
Filter data set to #N/A, skip step entirely if none
Replies
3
Views
310
RoryA
RoryA
C
  • Question Question
Copy and paste data through multiple workbooks
Replies
2
Views
507
CDaviess
C
B
  • Question Question
VBA code to cut and paste multiple filtered rows based on multiple criteria
Replies
2
Views
1K
Alex Blakenburg
A

Forum statistics

Threads
1,224,820
Messages
6,181,161
Members
453,021
Latest member
Justyna P

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